In this tutorial, we will look at how to show the grants which were made to a MySQL user account. The grants we want to show are simply the privileges which were assigned to the user account using the MySQL grant privileges command.
Showing the grants of a user in MySQL is important in the event that we want to see what a user is allowed to do on our database. It is always important to ensure that users have the minimum permissions necessary to carry out their assigned task(s).
Showing grants in MySQL is done using the SHOW GRANTS statement.
The syntax for the MySQL SHOW GRANTS statement is as follows:
SHOW GRANTS FOR user;
Of course, user is specified just as how we specify it when we used the GRANT PRIVILEGES statement; that is 'username'@'hostname'. Note that the username and hostname must be identical to how they were specified when the grants were issued.
A user can only see grants for another user if they have SELECT permissions on the mysql database.
To show the grants (privileges) which are assigned to 'someone'@'%' for example, we would execute the following SQL:
SHOW GRANTS FOR 'someone'@'%';
The result may be something like:
+-----------------------------------------------------+ | Grants for someone@% | +-----------------------------------------------------+ | GRANT ALL PRIVILEGES ON `somedb`.* TO 'someone'@'%' | +-----------------------------------------------------+
As you can see, they are allowed all privileges on 'somedb';
Suppose we granted some permissions to a user like this:
GRANT SELECT, INSERT ON somedb.* TO 'someone';
If we did a SHOW GRANTS command after that using:
SHOW GRANTS FOR 'someone';
This would be the result:
+---------------------------------------------------------+ | Grants for someone@% | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO 'someone'@'%' | | GRANT SELECT, INSERT ON `somedb`.* TO 'someone'@'%' | +---------------------------------------------------------+
Believe it or not, that is all there is to using SHOW GRANTS in MySQL.
If we want to see the grants for the currently logged in user, all we have to execute is:
SHOW GRANTS;
And that is it. Remember to only issue as much permissions to a user as they need to carry out their required tasks.
We hope this MySQL tutorial on showing grants was useful.