How to check what MySQL/MariaDB users exist and their characteristics

Contents

1. How to display the names of all MySQL/MariaDB users
2. How to display the names of all users and their hosts in MySQL/MariaDB
3. How to check which users are allowed to log in without a password in MySQL/MariaDB
4. How to check which users have expired passwords in MySQL/MariaDB. How to list users with expired passwords
5. How to check which plugin is used to identify a user in MySQL/MariaDB
6. How to check what privileges a user has
7. How to list all MySQL/MariaDB user settings

How to display the names of all MySQL/MariaDB users

If you need to get the names of all DBMS users, connect to the MySQL/MariaDB server

mysql -u root -p

Then run the following SQL query:

SELECT User FROM mysql.user;

How to display the names of all users and their hosts in MySQL/MariaDB

We may have noticed that for the previous query, the names of some users are repeated. The fact is that in MySQL and MariaDB, users are defined by two names: the username and the hostname – for these pairs, different privileges, different passwords, and even different authentication plugins can be configured.

To display the username together with the hostname, run the following SQL query:

SELECT User,Host FROM mysql.user;

How to check which users are allowed to log in without a password in MySQL/MariaDB

MySQL and MariaDB allow you to configure a user so that he can connect to databases and make SQL queries without entering a password. The following command will display all users who can connect to the MySQL/MariaDB server without a password.

SELECT User,Host FROM mysql.user WHERE Password = '' AND authentication_string = '';

This is a test environment, so the root user is allowed to connect and execute commands without a password – in a real production environment, this is, of course, unacceptable.

Please note that in different versions and variants of the DBMS (MySQL or MariaDB), the password hash can be stored in the Password or authentication_string field. In MariaDB, if both of these fields are empty, then the user is allowed to log in without a password; if both of these fields have values ​​(they store the password hash), then the user must enter a password to log in. In MySQL before version 5.7.26, the password hash was stored in the Password field, and starting with MySQL 5.7.26, the password hash is stored in authentication_string.

See also:

• https://mariadb.com/kb/en/mysql-user-table/
• https://github.com/sqlmapproject/sqlmap/issues/3759

How to check which users have expired passwords in MySQL/MariaDB. How to list users with expired passwords

To list all users with expired passwords, use the following query:

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

How to check which plugin is used to identify a user in MySQL/MariaDB

To check which user authentication plugin is used by each user, run the following command:

SELECT User,Host,plugin FROM mysql.user;

How to check what privileges a user has

The ‘user’ table in the ‘mysql’ database has quite a few columns that store various settings for each user. Among these settings are the user's grants (rights, privileges). Below is a list of all the columns of ‘mysql.user’. Columns with user privileges have the suffix ‘_priv’ in their names. The meaning of many columns can be guessed by their names. You can also find information about the meaning of the columns in ‘mysql.user’ in the documentation at the following link: https://mariadb.com/kb/en/mysql-user-table/

All columns of the table mysql.user:

• Host
• User
• Password
• 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
• ssl_type
• ssl_cipher
• x509_issuer
• x509_subject
• max_questions
• max_updates
• max_connections
• max_user_connections
• plugin
• authentication_string
• password_expired
• is_role
• default_role
• max_statement_time

How to list all MySQL/MariaDB user settings

To list the full list of users and all their settings, use the following SQL query. Note that this will output a very wide table.

SELECT * FROM mysql.user;

You can also get information about a specific user, for this use the following command:

SELECT * FROM mysql.user WHERE user = 'root' AND host = 'localhost';