Take the 2-minute tour ×
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 10 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 information_schema.user_privileges;
share|improve this answer

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/$/;/'
done

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
3  
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 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
1  
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

Your Answer

 
discard

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.