Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MySQL's SHOW GRANTS shows the permissions of the current user.

Is there a way to log in as root and show the permissions of all users?

share|improve this question

4 Answers 4

up vote 14 down vote accepted

Nothing built-in. You have two options though:

  • Use common_schema's sql_show_grants view. For example, you can query:

    SELECT sql_grants FROM common_schema.sql_show_grants;

    Or you can query for particular users, for example:

    SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app';

    Disclaimer: I am author of this tool.

  • Use Percona Toolkit's pt-show-grants, for example:

    bash$ pt-show-grants --host localhost --user root --ask-pass

In both cases you can ask for the GRANT command or the REVOKE (opposite) command.

The first case requires that you install a schema, the latter requires that you install PERL scripts + dependencies.

share|improve this answer
+1 for developing this tool! Any chance that MySQL will adopt your code? – Adam Matan Aug 28 '12 at 11:35
Many thanks! I'm pushing this project at this time (new release scheduled in a week or so), in an attempt to make it popular. I do not know if any will adopt it. – Shlomi Noach Aug 28 '12 at 12:41
I am going to stop working on my answer. It looks like you are light-years ahead on this one. +1. I hope it gains more traction in the MySQL Universe. – RolandoMySQLDBA Aug 29 '12 at 14:51
Could you please describe in more detail how to use common_schema's sql_show_grants view? I get an error ERROR 1146 (42S02): Table 'common_schema.sql_show_grants' doesn't exist – Martin Vegter Oct 24 '13 at 19:52
@MartinVegter , have you installed common_schema? Download here and install following these instructions. – Shlomi Noach Oct 25 '13 at 5:04

select * from mysql.user;

Can give you User list and Privileges assigned to each of them, requires access to mysql.user table though and root user has it :-)

share|improve this answer
This only gives you the "top level" (server level) privileges. Privileges set on specific schemas are in mysql.db. Privileges on specific tables are in mysql.tables_priv, and so on. So it's not so simple. – Shlomi Noach Aug 28 '12 at 12:40
For rainbow table shenanigans, throw your password hashes from select * from mysql.user into and see the unhashed output. – Pacerier Jul 7 at 8:30

This shell fragment loops over all MySQL users and does a SHOW GRANTS for each:

mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user" | sort | \
while read u
 do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'

Works best if you can connect to MySQL without a password.

Output is formatted so it can be run in a MySQL shell. Caution: Output also contains the MySQL root user permissions and password! Remove those lines if you don't want the MySQL root user changed.

share|improve this answer
You may want to add some details about what this does or how it answers the question. Just displaying a bunch of code doesn't help anyone understand why your solution works. – Max Vernon Sep 9 '13 at 23:13
@Max Vernon: Thanks, explanation added. – mleu Nov 7 '13 at 14:16
select * from information_schema.user_privileges;
share|improve this answer
This should be the accepted answer. – Pacerier Jul 7 at 8:14
Sorry, it should not be the accepted answer. information_schema.user_privileges only lists user-level privileges, such as SUPER, RELOAD etc. It also lists all-round DML grants like SELECT. It does not list database-specific, table-sepcific, column-specific, routine-specific privileges. There fore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show on information_schema.user_privileges. The common_schema solution presented above aggregates data from user_privileges and other tables to give you the full picture. – Shlomi Noach Jul 9 at 11:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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