Adding users to a Mysql database

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s