I wrote this post to memo the basic MySQL commands to show, grant, or revoke user privileges.

Firstly, let’s show all users and hosts of my current MySQL instance by the following command:

mysql> SELECT user,host FROM mysql.user;

+---------------+-----------+
| user          | host      |
+---------------+-----------+
| dbuser1       | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+

1, Show Privileges for a user in MySQL

SHOW GRANTS FOR <username>@<host>;

Also, we can check the permissions for the currently logged in user with:

mysql> SHOW GRANTS;

+----------------+
| Grants for dbuser1@% |
+----------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'dbuser1'@'%' WITH GRANT OPTION |
+----------------+

3, Revoke privileges for a user in MySQL. Here we take “REPLICATION SLAVE” as an example.

REVOKE REPLICATION SLAVE ON *.* FROM dbuser1;

4, Grant privileges for a user in MySQL

GRANT REPLICATION SLAVE ON *.* TO dbuser1;

End;