How To View User Privileges? - MySQL FAQs - Managing User Accounts and Access Privileges

If a regular user wants to see his/her own granted privileges, he/she can use the "SHOW GRANTS" command. If the "root" user wants to see other user's granted privileges, he/she can use the "SHOW GRANTS FOR userName" command. The following tutorial exercise shows you how to view user's granted privileges:

>cd \mysql\bin
>mysql -u root -pretneciyf

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

mysql> SHOW GRANTS for dev;
+------------------------------------------------------
| Grants for dev@%                      |
+------------------------------------------------------
| GRANT CREATE ON *.* TO 'dev'@'%' ... 
+------------------------------------------------------
1 row in set (0.00 sec)

mysql> SHOW GRANTS for qa;
+------------------------------------------------------
| Grants for qa@%                    |
+------------------------------------------------------
| GRANT USAGE ON *.* TO 'qa'@'%' ...
| GRANT CREATE ON `faq`.* TO 'qa'@'%'                  
+------------------------------------------------------
2 rows in set (0.00 sec)

The output says:

• "root" has all privileges on all databases.
• "dev" has only "CREATE" privilege on all databases.
• "qa" has no privileges on any databases, except for "CREATE" privilege on "faq" database.