Managing MYSQL users

These are a few commands I use in mysql to manage users and grants. I do this infrequently so I put them here to save having to google them when I need them.

Grant a user 'dev' all privileges on a database called "test";

mysql> GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost' IDENTIFIED BY 'devtest';
Query OK, 0 rows affected (0.02 sec)

See full syntax for GRANT command

To see what privileges a user has been granted;

mysql> SHOW GRANTS FOR 'dev'@'localhost';
+-----------------------------------------------------------------------------------------+
| Grants for dev@localhost                                                                |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'localhost' IDENTIFIED BY PASSWORD '*D98YCCE724CCT7BFA48E1' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'dev'@'localhost'                                   |
+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Sometimes I need to list all the users that have had permissions granted to them;

mysql> SELECT CONCAT('SHOW GRANTS FOR \'', user,'\'@\'', host, '\';') AS mygrants FROM mysql.user ORDER BY mygrants;


+-------------------------------------------------+
| mygrants                                        |
+-------------------------------------------------+
| SHOW GRANTS FOR ''@'localhost';                 |
| SHOW GRANTS FOR 'debian-sys-maint'@'localhost'; |
| SHOW GRANTS FOR 'dev'@'192.168.4.2';            |
| SHOW GRANTS FOR 'dev'@'localhost';              |
| SHOW GRANTS FOR 'root'@'127.0.0.1';             |
| SHOW GRANTS FOR 'root'@'::1';                   |
| SHOW GRANTS FOR 'root'@'localhost';             |
+-------------------------------------------------+

From that table you can copy-paste the relevant line to see the grants for a particular user.


Revoke a grant

mysql> REVOKE ALL PRIVILEGES ON `test`.* FROM 'dev'@'localhost';
Query OK, 0 rows affected (0.02 sec)

After revoking a users privileges, you will notice that the user still shows up with USAGE rights. To make a user go away completely you need to "drop" them;

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