MySQL Permissions – How to Show All Grants in MySQL

MySQLmysql-5.6permissions

I have over 100 system and admin users in my db server. Does anyone know how to show their grants by running a single query? Thank you very much.

Best Answer

No need to run a query - just input 

SHOW GRANTS;

If you have sufficient privileges, you should get all their privileges.

A bit more fine-grained is the following query

mysql> SELECT grantee, table_catalog, table_schema, privilege_type, is_grantable
 FROM schema_privileges;

This way, you can do it by user, &c. BTW, you have to run this from the information_schema. This information

This page should also be of use.

Using the mysql schema, you can issue the query

mysql> SELECT Host, Db, User, Table_name, Column_name, Column_priv FROM columns_
priv;

From this page.

Related Question