What to do if mysql show grants for all users?

263    Asked by Aashishchaursiya in SQL Server , Asked on Sep 30, 2022

 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?

Answered by Alan Taylor

If mysql show grants for all users - 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 the 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.



Your Answer

Interviews

Parent Categories