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.

2 Answers 2

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? Nov 20, 2015 at 3:33
  • Check out my query using the mysql schema.
    – Vérace
    Nov 20, 2015 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 and acknowledge you have read our privacy policy.

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