Show user privileges in MySQL

First off make sure you're logged in to MySQL. You can do this via a GUI, like MySQL Workbench, or via the MySQL shell:

$ mysql -u <user>

Now that you're logged in to MySQL you can simply show the grants of the current user by running one of the following query.

mysql> SHOW GRANTS;
+---------------------------------------------------+
| Grants for koen@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'koen'@'localhost' |
+---------------------------------------------------+
1 row in set (0,00 sec)

In order to find the grants given to another user you need to pass that user and the host to SHOW GRANTS.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0,00 sec)

By the way, if you need inspiration for querying users you can query the mysql.user table.

mysql> SELECT Host, User FROM mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| localhost | koen          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0,00 sec)

Unfortunately getting all grants for a specific user isn't as easy passing just the user and not the host :(.

mysql> SHOW GRANTS FOR 'root';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

The closest thing I came across to showing all the grants for a user is the query SELECT * FROM information_schema.user_privileges;. This does show the grants per privilege.

mysql> SELECT * FROM information_schema.user_privileges;
+-----------------------------+---------------+-------------------------+--------------+
| GRANTEE                     | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'          | def           | SELECT                  | YES          |
| 'root'@'localhost'          | def           | INSERT                  | YES          |
...
| 'koen'@'localhost'          | def           | CREATE TABLESPACE       | NO           |
+-----------------------------+---------------+-------------------------+--------------+
86 rows in set (0,00 sec)