MySQL Show Users

Last updated: October 19, 2026Oct 19, 2026 3 min read

There is no SHOW USERS statement in MySQL. Accounts are stored in the system table mysql.user, and you list them with an ordinary SELECT. This tutorial shows how to list accounts, find out who you are logged in as, check what each account can do, and see who is connected.

1. There Is No SHOW USERS

SHOW USERS;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USERS' at line 1

2. Listing Accounts from mysql.user

SELECT user, host FROM mysql.user ORDER BY user, host;

Result: 8 rows

Hide system accounts and show useful details:

SELECT user, host, plugin, account_locked, password_expired, password_lifetime,
       DATE(password_last_changed) AS pwd_changed
FROM mysql.user
WHERE user NOT LIKE 'mysql.%'
ORDER BY user;

Result: 5 rows

viewer appears too: roles are stored as locked accounts.

3. Unique User Names

Because an account is user + host, a name can appear more than once. List each name once:

SELECT user, COUNT(*) AS host_entries, GROUP_CONCAT(host) AS hosts
FROM mysql.user WHERE user NOT LIKE 'mysql.%'
GROUP BY user;

Result: 5 rows

4. Who Am I?

USER() is the name and host you tried to log in as; CURRENT_USER() is the account MySQL actually matched, whose privileges apply:

SELECT USER() AS login_as, CURRENT_USER() AS authenticated_as;

Result: 1 row

They can differ, for example when you connect from 10.0.0.15 and match the 'etl'@'10.0.0.%' account.

5. What Can an Account Do?

SHOW GRANTS FOR 'etl'@'10.0.0.%';

Result: 2 rows

SHOW GRANTS FOR 'dash'@'localhost';

Result: 2 rows

Include the privileges that come from roles with USING:

SHOW GRANTS FOR 'dash'@'localhost' USING 'viewer';

Result: 3 rows

Privileges per database across all accounts:

SELECT grantee, table_schema, GROUP_CONCAT(privilege_type ORDER BY privilege_type) AS privileges
FROM information_schema.schema_privileges
GROUP BY grantee, table_schema;

Result: 4 rows

6. Finding Risky or Stale Accounts

SELECT user, host,
       CASE WHEN account_locked = 'Y' THEN 'locked'
            WHEN password_expired = 'Y' THEN 'password expired'
            WHEN host = '%' THEN 'any host!'
            ELSE 'ok' END AS status
FROM mysql.user
WHERE user NOT LIKE 'mysql.%' AND user <> 'viewer'
ORDER BY status, user;

Result: 4 rows

7. Who Is Connected Right Now?

Accounts are not the same as sessions. List live connections with the process list:

SELECT user, COUNT(*) AS sessions FROM information_schema.processlist GROUP BY user;

Result: 2 rows

SHOW PROCESSLIST;          -- one row per connection: id, user, host, db, command, time, state
SHOW FULL PROCESSLIST;     -- including the full SQL text

8. Summary

• There's no SHOW USERS; query SELECT user, host FROM mysql.user.
• Add columns like plugin, account_locked and password_expired for an audit view.
• CURRENT_USER() shows the account whose privileges you have.
• SHOW GRANTS FOR … [USING role] shows what an account can do.
• SHOW PROCESSLIST shows who is connected now.