How to list MySQL/MariaDB users and their privileges (using RDBMS client and PHP)

This note explains which queries can be used to list MySQL and MariaDB users. The article shows how to run these commands in MySQL/MariaDB query string as well as with PHP.

How to display a list of users and their privileges using the DBMS client

Queries to list MySQL / MariaDB users

To list all users in MySQL / MariaDB, connect to the DBMS.

For example, connecting to MySQL/MariaDB as root user with password:

mysql -u root -p
mariadb -u root -p

Connecting to MySQL/MariaDB as root user without password:

mysql -u root
mariadb -u root

After that run the following query:

SELECT Host, User FROM mysql.user;

In the “User” column you will see the usernames. Some usernames are repeated because MySQL and MariaDB support assigning privileges to the HOST@USERNAME binding.

If you want to check if a user exists, you can use a query like:

SELECT Host, User FROM mysql.user WHERE User='USERNAME';

For example:

SELECT Host, User FROM mysql.user WHERE User='zara';
SELECT Host, User FROM mysql.user WHERE User='slava';

You can also use part of the username to search:

SELECT Host, User FROM mysql.user WHERE User LIKE 'za%';

MySQL / MariaDB queries to list user privileges

General information about user privileges

You can get generalized information about a user's privilege using a command like:

SHOW GRANTS FOR 'USERNAME'@'HOST';

For example:

SHOW GRANTS FOR 'zara'@'localhost';

See also:

• https://dev.mysql.com/doc/refman/8.1/en/grant-tables.html
• https://dev.mysql.com/doc/refman/8.1/en/request-access.html

Detailed information about user privilege

User privileges are stored in several tables of the mysql service database at once:

• user: User accounts, static global privileges, and other nonprivilege columns.
• global_grants: Dynamic global privileges.
• db: Database-level privileges.
• tables_priv: Table-level privileges.
• columns_priv: Column-level privileges.
• procs_priv: Stored procedure and function privileges.

Usually you don't need to read or change these values directly. Changes are made indirectly through the use of the CREATE USER, GRANT, and REVOKE statements. Nevertheless, you can get detailed information from these tables.

To list the privileges of a specific user, use a query like:

SELECT * FROM mysql.user WHERE User='USERNAME';

For example:

SELECT * FROM mysql.user WHERE User='root';

Be prepared for the fact that the table will contain a large number of fields and, as a result, will be very wide.

If you want to display only certain privileges, then list them. For example, to find out if the user “zara” has the Select_priv and Insert_priv privileges, the following query would be executed:

SELECT Select_priv, Insert_priv FROM mysql.user WHERE User='zara';

To display the value of the Select_priv and Insert_priv privileges for all users, send the following request:

SELECT User, Select_priv, Insert_priv FROM mysql.user;

Full list of privileges:

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv
• Show_db_priv
• Super_priv
• Create_tmp_table_priv
• Lock_tables_priv
• Execute_priv
• Repl_slave_priv
• Repl_client_priv
• Create_view_priv
• Show_view_priv
• Create_routine_priv
• Alter_routine_priv
• Create_user_priv
• Event_priv
• Trigger_priv
• Create_tablespace_priv
• Delete_history_priv

See also: https://dev.mysql.com/doc/refman/8.1/en/grant-tables.html

If you want to get the value of all the privileges of a particular user in human-readable form, then you can do the following:

1. Run the following query (replace “root” with your desired username):

mariadb -u root <<< 'SELECT * FROM mysql.user WHERE User="root";' > root.csv

As a result of executing the command, the “root.csv” file will be created, in which the output received from MySQL / MariaDB will be written.

2. Open the “root.csv” file with LibreOffice Calc:

The Tab character is used as field separators, but LibreOffice Calc is able to display such tables correctly.

You can use the slider at the bottom of the table to view all privileges and their current values for each user.

Listing DBMS users and their privileges using PHP

Listing MySQL / MariaDB users in PHP

The following PHP code will output a list of users in the format “HOST@USERNAME”.

<?php

$host = 'localhost';
$db_user = 'root';
$db_password = '';

$mysqli = new mysqli($host, $db_user, $db_password);
if ($mysqli->connect_errno) 
{
	printf("Connection error: %s\n", $mysqli->connect_error);
	exit();
}
else
{
	$query = "SELECT Host, User FROM mysql.user;";
	if ($result = $mysqli->query($query)) 
	{
		while ($row = $result->fetch_assoc()) 
		{
			echo $row["Host"] . '@' . $row["User"] . PHP_EOL;
		}
	}
	else 
	{
		echo 'We failed somehow' . PHP_EOL;
		printf("Error: %s\n", $mysqli->error);
	}
}

An example of the data obtained as a result of the script:

Use the queries shown above to get the desired data set.

Listing MySQL / MariaDB user privileges in PHP

The following PHP code will display the privileges for each user.

<?php

$host = 'localhost';
$db_user = 'root';
$db_password = '';

$mysqli = new mysqli($host, $db_user, $db_password);
if ($mysqli->connect_errno) 
{
	printf("Connection error: %s\n", $mysqli->connect_error);
	exit();
}
else
{
	$query = "SELECT * FROM mysql.user;";
	if ($result = $mysqli->query($query)) 
	{
		while ($row = $result->fetch_assoc()) 
		{
			print_r($row);
		}
	}
	else 
	{
		echo 'We failed somehow' . PHP_EOL;
		printf("Error: %s\n", $mysqli->error);
	}
}

An example of the data obtained as a result of the script:

You can also use the queries shown above to display the information you need about a particular user, as well as write your own handler for the output data (the data for each user is placed in a separate array).