MySQL: User Management

User Management in MySQL is one of the fundamental admin task and is quite easy, as well.

So, here are the basic commands regarding this:


** To list all the MySQL users: Users will be stored in the system table mysql.user. So, you need query this to fetch the users:


mysql> select user,host,password from mysql.user;

Output:

+-----------------+--------------+-------------------------------------------+
| user            | host         | password                                  |
+-----------------+--------------+-------------------------------------------+
| root            | localhost    | *0EF40B7834E1725C32C7EC640FC474ED72254B69 |
| dump            | 127.0.0.1    | *E1A36C05EDBEB86BF19531672613ECFC88F3DB2F |
| root            | 127.0.0.1    | *0EF40B7834E1725C32C7EC640FC474ED72254B69 |

| readonly        | %            | *9E1258A6F6806487E2BFA236472D4D28236F6215 |
+-----------------+--------------+-------------------------------------------+

4 rows in set (0.009 sec)

Note that, Password is encrypted here. This you've to take care while setting up password for any user by using PASSWORD(), a system function.


** To create new user and grant privilege: There are 3 ways to create an user:

1. Using CREATE command:

CREATE USER username@'host' IDENTIFIED BY 'password';
GRANT SELECT ON db.table TO 'username'@'host';
FLUSH PRIVILEGES;


2. Using GRANT command:

GRANT SELECT ON db.table TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

The above command will create the user, if it is not present.


3. Inserting into mysql.user table:

INSERT INTO mysql.user(user,host,password) values ('usrename','host','encrypted_password');

GRANT SELECT ON db.table TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

However, this is not a RECOMMENDED way. Make sure, this password is encrypted using below query:

SELECT PASSWORD('password');



**Removing User/Access:


To Revoke access:

REVOKE SELECT ON db.table FROM 'username'@'host';
FLUSH PRIVILEGES;


To Remove user:

DROP USER 'username'@'host';
FLUSH PRIVILEGES;

or

DELETE FROM mysql.user WHERE USER='username' and host='host';
FLUSH PRIVILEGES;


FLUSH PRIVILEGES -- This command is to Reload the privileges of all the user(reloads from mysql.user table)