There are numerous ways of adding a user to MySQL, one method is explained below.

Create a user called blah who can connect from any host with the password of f#4fsFF334@*.

mysql>; create user 'blah'@'%' identified by 'f#4fsFF334@*';
Query OK, 0 rows affected (0.02 sec)


Grant the user ability to run the select command on the test database. test.* indicates all tables that are part of test database.

mysql>; grant select on test.* to 'blah'@'%';
Query OK, 0 rows affected (0.00 sec)


Flush privileges is required if you want the user addition to be immediate.

mysql>; flush privileges;
Query OK, 0 rows affected (0.00 sec)


Verify that the user has permissions on the test database. The 2nd line which shows ‘GRANT SELECT’ confirms the privileges. The first line shows that the user has no permissions by default.

mysql>; show grants for 'blah'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for blah@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blah'@'%' IDENTIFIED BY PASSWORD '*7D3D76DCFC5842A5CDF9E2F01D18D3C4647A5400' |
| GRANT SELECT ON `test`.* TO 'blah'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


If you want to delete the user, you can simply run ‘drop’:

mysql>; drop user 'blah'@'%';
Query OK, 0 rows affected (0.00 sec)


How do you manage MySQL users? Share your comments in this blog.