Scripting & Programming

MySQL: Check user privileges on a database

I had to check the user privileges on a database. What I found in google is only how to check the grants for a single user but I couldn’t find how to check the privileges of all users to a single database so I decided to write this article.

The grants are usually kept in the information_schema database.

Firstly you need to login to your MySQL as root using the following command:

user@TheBox ~ # mysql -uroot -p

Once you have logged in you need to use the information_schema database:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

now we have to get the needed privileges info on a database. Let’s say the database is named example.

We have to execute the following:

mysql> select * from SCHEMA_PRIVILEGES where TABLE_SCHEMA=’example’;

Note: You have to replace example with the name of your database.

This command will print a list of all the users that have privileges on the database and what are they able to do.

Leave a Reply