List all users in MySQL database server

MySQL accounts are identified by both a user name and a host. To list configured accounts, query the mysql.user system table with an account authorized to read it. This list is different from the sessions that happen to be connected now.

List All Users

To list accounts, use a MySQL account with permission to read mysql.user. An administrative account commonly has this access. Replace admin_user below with the authorized account name, then enter its password when prompted:

mysql --user=admin_user --password

Enter the password for admin_user 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;

For example, a result might contain rows like these. The names and hosts are illustrative:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| app_reader       | localhost |
| app_writer       | 192.0.2.20 |
| db_admin         | localhost |
+------------------+-----------+

The mysql.user system table contains authentication and privilege metadata. Avoid selecting or sharing more columns than you need; the query above returns only account names and allowed hosts. See the MySQL grant-table reference for the system table’s role.

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.

Inspect the system-table columns

To see the columns available on your MySQL version, run:

DESC mysql.user;

The system-table schema can change between MySQL releases. Use the output from your own server when you need schema details; the account listing above only needs user and host.

Check the account identities for your connection

CURRENT_USER() returns the MySQL account the server uses for privilege checks. USER() returns the user name and host supplied by the client. The values can differ; see the MySQL information-functions reference.

For example, if the client connects as app_user from 192.0.2.25 and MySQL matches the 'app_user'@'%' account, the functions can return:

Use CURRENT_USER() to check the account used for access checks:

SELECT current_user();

+----------------+
| current_user() |
+----------------+
| app_user@%     |
+----------------+

Use USER() to see the client user name and host:

SELECT user();

+--------------------+
| user()             |
+--------------------+
| [email protected] |
+--------------------+

Inspect active sessions

To see current server threads, run SHOW PROCESSLIST:

SHOW PROCESSLIST;

The process list shows session user and host information, subject to the PROCESS privilege. For a queryable Performance Schema alternative and details about the output columns, see the MySQL SHOW PROCESSLIST tutorial. The deprecated INFORMATION_SCHEMA.PROCESSLIST table is not recommended for new monitoring queries.

Conclusion

This article showed how to list configured accounts, check the identity used by a connection, and inspect active sessions.

MySQL has no SHOW USERS statement. Query mysql.user for accounts; use SHOW DATABASES and SHOW TABLES for database and table names.