Friday, April 18, 2008

Create User Account Example for MySQL

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

2 comments:

Anonymous said...

I use a variation of method 1. When you use the GRANT statement, it will automatically create a user if the user specified does not exist. This means you can do the following:

grant INSERT,DELETE,UPDATE,SELECT on mydb.* to esoft@localhost;

set password for esoft = password('mypasswd');

Anonymous said...

thx for the post!

I had to surf for a while before I found your tutorial that correctly includes the 'flush privs' command. I couldn't quite remember the syntax, but I knew there was some way to clear the command buffer.