MySQL show users

Quick answer

MySQL has no SHOW USERS command. Query the mysql.user table instead:

SELECT user, host FROM mysql.user;

List all users

SELECT user, host FROM mysql.user;

Output:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| app_user         | localhost |
| backup_user      | %         |
| readonly_user    | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

Every MySQL account is a user@host pair. root@localhost and root@% are two separate accounts with independent passwords and privileges. The host column controls which machines a user can connect from.

List users with password status

Include the plugin and authentication_string columns to see what authentication method each account uses and whether a password is set:

SELECT
  user,
  host,
  plugin,
  authentication_string IS NOT NULL AS has_password
FROM mysql.user;

Output:

+------------------+-----------+-----------------------+--------------+
| user             | host      | plugin                | has_password |
+------------------+-----------+-----------------------+--------------+
| app_user         | localhost | caching_sha2_password |            1 |
| backup_user      | %         | caching_sha2_password |            1 |
| readonly_user    | localhost | mysql_native_password |            1 |
| root             | localhost | auth_socket           |            0 |
+------------------+-----------+-----------------------+--------------+

The auth_socket plugin authenticates by matching the OS user, so it does not store a password hash. That is normal for root accounts on Debian and Ubuntu systems.

Filtering by host

Show only accounts that can connect from any host:

SELECT user, host FROM mysql.user WHERE host = '%';

Show only local accounts:

SELECT user, host FROM mysql.user WHERE host = 'localhost';

Filter by a subnet or IP pattern:

SELECT user, host FROM mysql.user WHERE host LIKE '10.0.%';

Checking if a specific user exists

SELECT user, host
FROM mysql.user
WHERE user = 'app_user';

This returns all accounts with that username across different hosts. To check for a specific user@host pair:

SELECT user, host
FROM mysql.user
WHERE user = 'app_user' AND host = 'localhost';

An empty result means that exact account does not exist.

Viewing a user's privileges

SHOW GRANTS returns the privilege statements for a specific account:

SHOW GRANTS FOR 'app_user'@'localhost';

Output:

+---------------------------------------------------------------+
| Grants for app_user@localhost                                 |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                  |
| GRANT SELECT, INSERT, UPDATE ON `my_app`.* TO `app_user`@`localhost` |
+---------------------------------------------------------------+

To check the current session's grants:

SHOW GRANTS;

Or explicitly:

SHOW GRANTS FOR CURRENT_USER();

Viewing all users' grants at once

There is no single query that returns all users' grants in a clean format. You would need to loop through mysql.user and call SHOW GRANTS for each account.

In the MySQL shell, you can generate the statements with:

SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';')
FROM mysql.user;

Then run each generated statement separately. For an automated audit, the pt-show-grants tool from Percona Toolkit handles this and formats the output cleanly.

The current user

Two functions return information about the current session:

SELECT CURRENT_USER();
-- Returns: app_user@localhost

SELECT USER();
-- Returns: app_user@192.168.1.10

CURRENT_USER() returns the account MySQL matched in the grant tables. USER() returns the username and host that were supplied at login. These differ when a wildcard host like % matched the connection. CURRENT_USER() is what matters for privilege checks.

Required privileges

To query mysql.user, your account needs the SELECT privilege on the mysql database:

GRANT SELECT ON mysql.* TO 'admin_user'@'localhost';

Without it, you get an access denied error. Root and accounts with global SELECT can always query mysql.user. For SHOW GRANTS FOR another user, you need the SELECT privilege on mysql.user or the global PROCESS privilege.

Quick reference

| Task | Query | |------|-------| | List all users | SELECT user, host FROM mysql.user; | | List users with auth info | SELECT user, host, plugin FROM mysql.user; | | Users connecting from anywhere | WHERE host = '%' | | Local users only | WHERE host = 'localhost' | | Check if a user exists | WHERE user = 'name' AND host = 'host' | | View a user's privileges | SHOW GRANTS FOR 'user'@'host'; | | View current user's privileges | SHOW GRANTS; | | Current matched account | SELECT CURRENT_USER(); | | Account supplied at login | SELECT USER(); |