List all users in MySQL database server
This article describes how to list all users in a MySQL database server in MySQL.
It is a common operation for a database administrator or database operation and maintenance personnel to view all users in the current database server.
In the MySQL database server, the mysql database stores some basic information, including all users. You can query all users from the user table in the mysql database.
List All Users
To list all users of the MySQL server, you must be logged in to the MySQL database server as an administrator. In the mysql client, we can do these as follows:
mysql -u root -p
Enter the password for the root account and press Enter:
Enter password: ********
Use the following SELECT statement to query all users from the user table in the mysql database:
SELECT user, host FROM mysql.user;
Here’s the output from the MySQL server that I’m connecting to:
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| sqliz | % |
| test_role1 | % |
| test_role2 | % |
| test_user1_new | % |
| test_user2_new | % |
| test_user3_new | % |
| testuser | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+There are many columns in the mysql.user table, which store various information about the user, such as password, password expiration time, whether it is locked or not, and various privileges.
Here, we only output two columns: user and host, where the user column holds the username of the user account, and the host column holds the host (which is usually the hostname or IP address) that the user account is allowed to log in from.
To get more information about the user table, you can output all columns of the user tables:
DESC mysql.user;
Here is the complete output:
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+Show Current User
To get information about the current user’s credentials, you can use the current_user() function as follows:
SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@% |
+----------------+To get information about the current user, you can use the user() function as follows:
SELECT user();
+--------------------+
| user() |
+--------------------+
| [email protected] |
+--------------------+Show logged in users
You can get the currently logged in user using the following SHOW PROCESSLIST statement :
SHOW PROCESSLIST
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 4066439 | Waiting on empty queue | NULL |
| 24 | root | 192.168.0.204:54684 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 26 | sqliz | 192.168.0.204:54688 | sqlizdb | Sleep | 7192 | | NULL |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+Or, you can get all logged in users from the information_schema.processlist table, as following:
SELECT user, host, db, command
FROM information_schema.processlist;+-----------------+---------------------+---------+---------+
| user | host | db | command |
+-----------------+---------------------+---------+---------+
| root | 192.168.0.204:54684 | NULL | Query |
| sqliz | 192.168.0.204:54688 | sqlizdb | Sleep |
| event_scheduler | localhost | NULL | Daemon |
+-----------------+---------------------+---------+---------+Conclusion
In this article, you learned how to query information about all users, all logged in users, and the current user in MySQL.
Note that there is no SHOW USERS statement in MySQL, not like SHOW DATABASES, SHOW TABLES.