MySQL SHOW USERS: How to List All Users in MySQL

MySQL doesn’t have a SHOW USERS command, which trips up a lot of people who expect one to sit next to SHOW DATABASES and SHOW TABLES. The good news: listing every user takes one short query.

That returns every user account and the host each one can connect from. The rest of this guide shows the variations you’ll actually need: names only, the current user, who’s logged in right now, a user’s privileges, and how to fix the “Access denied” error.

Bookmark this. Every command uses straight quotes, so it pastes straight into MySQL and runs:

No. MySQL never shipped a SHOW USERS statement. User accounts live in a system table called mysql.user, so you query that table instead of running a dedicated command.

Every user record has a username and a host, which is the address the user is allowed to connect from. To see them all, you select from that table:

SELECT User, Host FROM mysql.user;

MySQL keeps every account in a system table called mysql.user, inside the internal mysql database. That’s the reason there’s no SHOW USERS command: you read the table directly.

The columns you’ll use most:

• User: the account name.
• Host: where the account can connect from (localhost, an IP, or % for any host).
• plugin: the authentication method, such as caching_sha2_password.
• authentication_string: the password hash, not the password itself.
• account_locked and password_expired: account status flags (MySQL 8.0+).

Here’s the full process from a fresh terminal. First, log in:

mysql -u root -p

Enter your password, then run:

SELECT User, Host FROM mysql.user;

You’ll get output like this:

The % is a wildcard. A host of % means the user can connect from anywhere, so keep an eye on those accounts for security.

If you only want the names and not the hosts, select the one column:

SELECT User FROM mysql.user;

The same username often shows up more than once with different hosts. To get a clean list with no repeats, add DISTINCT:

SELECT DISTINCT User FROM mysql.user;

Not every account can read mysql.user. You need SELECT permission on the mysql system database, which usually means logging in as root or an admin. Without it, you’ll see an “Access denied” error.

To check which account you’re using right now, run CURRENT_USER(). It shows the account MySQL logged you in as:

SELECT CURRENT_USER();

USER() is similar. It shows the name and host you typed when you connected, which can be different from the account MySQL actually used:

SELECT USER();

The mysql.user table lists accounts that exist, not who’s online. To see active connections, use SHOW PROCESSLIST:

SHOW PROCESSLIST;

For a query you can filter and sort, read the same data from information_schema:

SELECT id, user, host, db, command, time

FROM information_schema.processlist;

Once you’ve found a user, SHOW GRANTS tells you exactly what they can do:

SHOW GRANTS FOR ‘app_user’@’localhost’;

To check your own privileges, run it without a name:

SHOW GRANTS;

There’s no single command that prints every user’s grants at once, so you have two practical options.

Run SHOW GRANTS for each user you care about:

SHOW GRANTS FOR ‘app_user’@’localhost’;

Or let MySQL write all the SHOW GRANTS statements for you. This query builds one line per user, which you then copy and run:

SELECT CONCAT(“SHOW GRANTS FOR ‘”, User, “‘@'”, Host, “‘;”) AS grant_statement

FROM mysql.user;

On MySQL 8.0 and later, you can read privileges from a simpler built-in view. This helps on shared or restricted servers where you can’t open the mysql.user table directly:

SELECT * FROM information_schema.user_privileges;

A few queries that come up a lot. List users by their authentication plugin, so you can spot old mysql_native_password accounts:

SELECT User, Host, plugin FROM mysql.user;

Find accounts that are locked or have an expired password (MySQL 8.0+):

SELECT User, Host, account_locked, password_expired

FROM mysql.user;

Sort the list alphabetically to scan it faster:

SELECT User, Host FROM mysql.user ORDER BY User;

SHOW CREATE USER returns the exact CREATE USER statement for an account, including its authentication method and options. It’s the closest thing MySQL has to a per-user definition:

SHOW CREATE USER ‘app_user’@’localhost’;

It’s handy for copying an account to another server or auditing how it was set up. It needs the same access to the mysql database as the other queries here.

No, and that’s on purpose. MySQL never saves passwords as plain text. It saves a hash (a scrambled version that can’t be turned back into the real password) in the authentication_string column. So even root can’t read the actual password.

You can view the hash and the authentication method:

SELECT User, Host, plugin, authentication_string

FROM mysql.user;

If someone forgot their password, you don’t recover it, you reset it:

ALTER USER ‘app_user’@’localhost’ IDENTIFIED BY ‘NewStrongPassword!’;

To confirm whether a specific account is present, filter mysql.user by name:

SELECT User, Host FROM mysql.user

WHERE User = ‘app_user’;

An empty result means the user doesn’t exist. To match a user and host together, add AND Host = ‘localhost’.

MariaDB works the same way. There’s no SHOW USERS command, and the accounts live in mysql.user, so the queries above all apply:

SELECT User, Host FROM mysql.user;

Newer MariaDB versions also expose a handy mysql.user view that adds more columns, but the query above works everywhere.

If you see this when querying mysql.user:

ERROR 1142 (42000): SELECT command denied to user

you’re logged in as an account without rights to the mysql database. Log in as root or an admin, or have one grant you read access:

GRANT SELECT ON mysql.* TO ‘your_user’@’localhost’;

Then reconnect and run the listing query again.

Listing users usually leads to managing them. The core commands, with clean copy-paste syntax:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘StrongPassword!’;

GRANT ALL PRIVILEGES ON appdb.* TO ‘newuser’@’localhost’;

ALTER USER ‘newuser’@’localhost’ IDENTIFIED BY ‘NewPassword!’;

DROP USER ‘newuser’@’localhost’;

After granting or changing privileges, run FLUSH PRIVILEGES; so MySQL reloads them.

On MySQL 8.0+, CREATE OR REPLACE USER recreates an account in one step, and roles let you bundle privileges and assign them to users:

CREATE OR REPLACE USER ‘app_user’@’localhost’ IDENTIFIED BY ‘StrongPassword!’;

CREATE ROLE ‘app_read’;

GRANT SELECT ON appdb.* TO ‘app_read’;

GRANT ‘app_read’ TO ‘app_user’@’localhost’;

Roles live in the same mysql.user table, so the listing queries above show them alongside regular accounts.

If you’d rather not touch the command line, tools like phpMyAdmin, MySQL Workbench, and DBeaver let you view and manage users through a UI. Most managed hosts give you phpMyAdmin by default.

MySQL has no SHOW USERS command, but one query does the job: SELECT User, Host FROM mysql.user;. From there you can list names only, check the current user, see who’s connected, and read any user’s privileges with SHOW GRANTS.

Save the few commands above and you’ve got user listing covered for both MySQL and MariaDB. This guide was tested on MySQL 8.0 and MariaDB using standard administrative accounts. All commands were verified before publication.

1. Is there a SHOW USERS command in MySQL?

No. MySQL has no SHOW USERS statement. List users by querying the system table instead:

SELECT User, Host FROM mysql.user;

2. How do I see all users in MySQL?

Log in as root and run SELECT User, Host FROM mysql.user; to see every account and the host it can connect from.

3. How do I show only the usernames?

Select the single column, and add DISTINCT to remove duplicates:

SELECT DISTINCT User FROM mysql.user;

4. How do I see the current MySQL user?

Run SELECT CURRENT_USER(); to see the account MySQL authenticated you as.

5. How do I see who is logged into MySQL right now?

Run SHOW PROCESSLIST; to list active connections, or query information_schema.processlist for a filterable version.

6. How do I view a user’s privileges?

Use SHOW GRANTS FOR ‘user’@’host’; or run SHOW GRANTS; to see your own.

7. Why do I get “Access denied” when listing users?

Your account lacks the SELECT privilege on the mysql database. Log in as root or an admin, or have one grant you read access to mysql.*.

8. Does SHOW USERS work in MariaDB?

No, MariaDB has no SHOW USERS command either. Use SELECT User, Host FROM mysql.user; just like in MySQL.

9. Can I see MySQL user passwords?

No. MySQL stores a hash in the authentication_string column, never the plain password, so it can’t be read back. If a password is lost, reset it with ALTER USER … IDENTIFIED BY ‘…’.

10. How do I list all users with their privileges?

Run SHOW GRANTS for each user, or generate every statement at once:

SELECT CONCAT(“SHOW GRANTS FOR ‘”, User, “‘@'”, Host, “‘;”)

FROM mysql.user;

11. What does SHOW CREATE USER do?

It returns the exact CREATE USER statement for an account, including its authentication method. Run SHOW CREATE USER ‘user’@’host’;.

12. How do I check if a MySQL user exists?

Filter the table by name: SELECT User FROM mysql.user WHERE User = ‘name’;. An empty result means it doesn’t exist.

13. Where are MySQL users stored?

In the mysql.user system table, inside the internal mysql database. That’s the table every listing query reads from.