User management in MySQL

Welcome back!

Here is something that anybody should remember.

Adding, setting privileges and removing users in MySQL db using command line.

I know this is trivial and that you can easily google/yahoo/bing this, but still....

This blog is supposed to be a complete admin's diary so -  it has to be here.

Anyway:

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Create a user that can access the MySQL from any host.
If you want to restrict the access to certain machine - replace '%' (MySQL wildcard) with IP address or hostname.
If you want to create a user without any password - do not input the IDENTIFIED BY part.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password'
-> WITH GRANT OPTION;

Give this user full administrative access to all databases with the option to control other users' privileges.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
 -> ON database1.*
 -> TO 'username'@'%';
 

Give this user superuser access (read and modify tables and data) to all tables in database1.

mysql> REVOKE INSERT,UPDATE,DELETE,CREATE,DROP
-> ON database1.*
-> TO 'username'@'%'; 
 

Remove from this user superuser access (only SELECT command is available now) to all tables in database1.

mysql> FLUSH PRIVILEGES; 

This tells MySQL to reload the grant tables. Not always necessary.

Other method is to inject the user data into the 'user' table on 'mysql' database (default db existing in every MySQL).

mysql> INSERT INTO user(Host,User,Password)
-> VALUES('localhost', 'username', PASSWORD('password'));
mysql> FLUSH PRIVILEGES;

Last line is required when using this method. 

To remove a user you only need one command (since MySQL version 5.0.2)

mysql> DROP USER user@host;