MySQL – Show GRANTs for All Users

MySQLpermissions

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?

Best Answer

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';

    To install common_schema, follow the instructions here.

    Disclaimer: I am author of this tool.

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

    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.

Related Question