mysql: Show GRANTs for all users

Question

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?

Answer 1

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.

Answer 2

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 :-)