0

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.

1

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.

2
  • is there a way up to the table and column level? – Darwin Cabiling Nov 20 '15 at 3:33
  • Check out my query using the mysql schema. – Vérace Nov 20 '15 at 3:34
1

If you have Percona Toolkit installed, you can use the pt-show-grants to print all MySQL grants.

pt-show-grants [OPTIONS] [DSN]

pt-show-grants shows grants (user privileges) from a MySQL server.

Refer to the Documentation for available options.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.