A couple of days ago I was adding entries to my ban related tables via phpbb's administration tools. After awhile, I quickly tired of serially adding an entry and then submitting it. I decided to dump the three tables in question and then modify them by hand. Plus that, I wanted to review the MySQL syntax.

Here are a series of create and insert statements for MySQL. I modified some of the entry values with "x", "!", "?" as to not offend some readers.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `phpbb_banlist`
--

CREATE TABLE `phpbb_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) NOT NULL default '',
`ban_email` varchar(255) default NULL,
PRIMARY KEY (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=97 ;

--
-- Dumping data for table `phpbb_banlist`
--

INSERT INTO `phpbb_banlist` VALUES(84, 0, '', '*@fromru.com');
INSERT INTO `phpbb_banlist` VALUES(5, 0, '', '*@mail.ru');
INSERT INTO `phpbb_banlist` VALUES(6, 0, '', '*@*.ru');
INSERT INTO `phpbb_banlist` VALUES(7, 0, '', '*@go2.pl');
...

CREATE TABLE `phpbb_disallow` (
`disallow_id` mediumint(8) unsigned NOT NULL auto_increment,
`disallow_username` varchar(25) NOT NULL default '',
PRIMARY KEY (`disallow_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

INSERT INTO `phpbb_disallow` VALUES(1, '*porn*');
INSERT INTO `phpbb_disallow` VALUES(2, '*sex*');
INSERT INTO `phpbb_disallow` VALUES(3, '*xxxxx*');
INSERT INTO `phpbb_disallow` VALUES(4, '*co!!*');
INSERT INTO `phpbb_disallow` VALUES(5, '*axx*');
INSERT INTO `phpbb_disallow` VALUES(6, '*adult*');
INSERT INTO `phpbb_disallow` VALUES(7, '*viagra*');
INSERT INTO `phpbb_disallow` VALUES(8, '*pill*');
INSERT INTO `phpbb_disallow` VALUES(9, '*drug*');
INSERT INTO `phpbb_disallow` VALUES(10, '*pharm*');
...

-- Table structure for table `phpbb_words`
--

CREATE TABLE `phpbb_words` (
`word_id` mediumint(8) unsigned NOT NULL auto_increment,
`word` char(100) NOT NULL default '',
`replacement` char(100) NOT NULL default '',
PRIMARY KEY (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

--
-- Dumping data for table `phpbb_words`
--

INSERT INTO `phpbb_words` VALUES(1, 'xxxx', '***');
INSERT INTO `phpbb_words` VALUES(2, 'sh??', '****');
INSERT INTO `phpbb_words` VALUES(3, 'xxxch', '*****');
INSERT INTO `phpbb_words` VALUES(4, 'fxxke?', '******');
INSERT INTO `phpbb_words` VALUES(5, 'axx', '***');
INSERT INTO `phpbb_words` VALUES(6, '*turd*', '****');
...

Unix: My SysAd Blog

System Database SQL Unix MySQL Administration insert

Create User Account Example for MySQL

Thursday, May 08, 2008

Lately, I have been adding several user accounts to the mysql database. Since I have seen several queries via my metadata provider to create user accounts in MySQL, I have decided to add a post about it. There are three different methods that can be used to create user accounts and they are fairly straightforward.

mysql> use mysql;
Database changed

Method 1: (create user, password - no privileges)
mysql> CREATE USER 'esoft'@'localhost' IDENTIFIED BY '12wer56hi';
Query OK, 0 rows affected (0.16 sec)

--grant certain privileges
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'esoft'@'localhost';
Query OK, 0 rows affected (0.00 sec)

--grant all privileges
mysql> GRANT ALL ON *.* TO 'esoft'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Method 2: (create user/password and grant all privileges at one fell swoop)
mysql> GRANT ALL ON *.* TO 'topblog'@'localhost' IDENTIFIED BY 'topblog123';
Query OK, 0 rows affected (0.00 sec)

Method 3: (Insert user, password and privileges in table)
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','freeads',PASSWORD('adlists123'));
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)
-> VALUES('localhost','softhub',PASSWORD('softhub126'),'Y','Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
-> Create_priv,Drop_priv)
-> VALUES('localhost','freebies',PASSWORD('afreeb456'), 'Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Note additional *_priv:
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
Show_db_priv
Super_priv
Create_tmp_table_priv
Lock_tables_priv
Execute_priv
Repl_slave_priv
Repl_client_priv
Create_view_priv
Show_view_priv
Create_routine_priv
Alter_routine_priv
Create_user_priv
Event_priv
Trigger_priv

Unix: My SysAd Blog

Database Unix MySQL user create privileges insert