This article will show you, with examples, how to show what database privileges users have in MySQL and MariaDB.
List All Users
To show the privileges for a user, you need to be able to query the user’s name. Here’s how to generate a list of all users on a MySQL server:
SELECT user FROM mysql.user;
…and here’s how to list all users, with the host they are allowed to connect from:
SELECT user, host FROM mysql.user;
Both of the above queries pull information from the mysql.user table, which contains the user configuration for the MySQL server.
The MySQL SHOW GRANTS Statement
To query for users’ privileges, the SHOW GRANTS statement is used. The below examples will demonstrate its syntax and usage.
Show Privileges for Current User
To show the assigned privileges for the current user:
SHOW GRANTS FOR CURRENT_USER;
CURRENT_USER is a shortcut to the CURRENT_USER() function, which returns the username and hostname for the currently logged-in user.
Show Privileges for Any User
The syntax for SHOW GRANTS to show the privileges for any user is as follows:
SHOW GRANTS FOR 'username'@'hostname';
So, if there is a user called linuxscrew which is allowed to connect from any host:
SHOW GRANTS FOR 'username'@'%';
When referring to hosts in MySQL, % (percent symbol) represents that a user can connect from any host.
Users may have different privileges assigned based on which host they are connecting from. The host can be specified when querying privileges:
SHOW GRANTS FOR 'root'@'localhost';
The above example will show privileges for the root user when connecting from localhost.
SHOW GRANTS Output Format
SHOW GRANTS will output information in the following format:
+---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
The above output shows the permissions for the root user – you can see that root has ALL PRIVILEGES on *.* (meaning all tables in all databases).
Show Privileges for All Users
There’s no built-in way to list all permissions at once – you must specify the user you wish to view permissions for.
However, it is possible to use an external script to list all users, then query them separately, and output the results as a single report. Some MySQL libraries can do this too, but this single line Bash script is faster and doesn’t require installing extra stuff:
#!/bin/bash
mysql -uroot -p -sNe"`mysql -uroot -p -se"SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user;"`"
The above script will require the entry of the root MySQL user credentials and will then query the user table, and using the results of that query, run SHOW GRANTS on each user present.
To find out more about the SHOW GRANTS statement and other ways it can be used, check out the official MySQL documentation at:
