LiveChat 
Support 
MySQL SHOW USERS: How to List All Users (There’s No SHOW USERS Command)
If you have ever typed `SHOW USERS;` into a MySQL or MariaDB console expecting a tidy list of accounts, you already know the punchline: the server throws a syntax error. Unlike `SHOW DATABASES` and `SHOW TABLES`, there is no `SHOW USERS` command in MySQL or MariaDB. Instead, user accounts live in a system table called `mysql.user`, and you list them with a plain `SELECT` query.
This guide explains why the command does not exist, gives you the exact queries to list every user, shows how to clean up the output, and covers how to do the same thing visually in phpMyAdmin or cPanel.
Key Takeaways
• There is no `SHOW USERS` command in MySQL or MariaDB, even though `SHOW DATABASES` and `SHOW TABLES` exist.
• List all accounts with `SELECT User, Host FROM mysql.user;`.
• Every account is a `User@Host` pair, so the same username can appear multiple times for different hosts.
• You need the `SELECT` privilege on the `mysql` system database (typically `root` or an admin user) to read `mysql.user`.
• In phpMyAdmin or cPanel, you can view database users through a graphical interface with no SQL required.
Why is there no SHOW USERS command in MySQL?
This is the part that trips up almost everyone. MySQL gives you convenient `SHOW` statements for many objects:
- `SHOW DATABASES;` lists databases.
- `SHOW TABLES;` lists tables in the current database.
- `SHOW GRANTS;` lists privileges for one account.
So it feels natural to assume `SHOW USERS;` exists too. It does not. User accounts are not treated as a “schema object” you browse with a `SHOW` command. Instead, they are stored as rows in an ordinary table inside the internal `mysql` system database. That table is `mysql.user`.
Here is the mental model that makes this click: in MySQL, a user account is data, not a structural object. Databases and tables describe the *shape* of your storage, so MySQL exposes them through dedicated `SHOW` metadata commands. Accounts, by contrast, are just records the access-control system reads at login time. Because they are data, you query them the same way you query any other table, with `SELECT`. Once you internalize that “users are rows,” the absence of `SHOW USERS` stops feeling like an omission and starts feeling consistent.
So whenever you reach for a non-existent `SHOW USERS`, mentally translate it to:
“`sql SELECT User, Host FROM mysql.user; “`
How do you list all users in MySQL or MariaDB?
The canonical way to list every account is to select the `User` and `Host` columns from the `mysql.user` table.
“`sql SELECT User, Host FROM mysql.user; “`
Sample output:
“` +——————+———–+
| User | Host | +——————+———–+
| app_user | % |
| backup_user | localhost |
| root | localhost |
| root | 127.0.0.1 |
| mysql.sys | localhost | +——————+———–+ “`
That is everything you need for a quick look. The `User` column is the account name, and the `Host` column is the host the account is allowed to connect from. Notice that `root` appears twice, once for `localhost` and once for `127.0.0.1`. That is expected, and it leads directly to the next point.
Why does the same username appear more than once?
In MySQL and MariaDB, an account is identified by a `User@Host` pair, not by the username alone. `’app_user’@’localhost’` and `’app_user’@’%’` are two separate accounts with potentially different passwords and privileges. This is a deliberate security feature: it lets you grant a user broad rights when connecting locally and restricted rights when connecting from a remote address.
The `Host` column values mean:
- `localhost` connections from the same machine over a socket.
- `%` a wildcard matching any host (use carefully).
- `127.0.0.1` local TCP connections.
- A specific IP or hostname connections only from that address.
Because of this pairing, “how many users do I have” is slightly ambiguous. If you want the list of distinct usernames regardless of host, use `DISTINCT`:
“`sql SELECT DISTINCT User FROM mysql.user ORDER BY User; “`
How do you make the output cleaner?
The raw table is functional but messy. A few small additions make the list far easier to scan, especially on servers with many accounts.
Sort the results so accounts group together predictably:
“`sql SELECT User, Host FROM mysql.user ORDER BY User, Host; “`
Filter out internal system accounts like `mysql.sys`, `mysql.session`, and `mysql.infoschema`, which MySQL creates for its own use:
“`sql SELECT User, Host FROM mysql.user WHERE User NOT LIKE ‘mysql.%’ ORDER BY User; “`
Find a specific user across all its host entries:
“`sql SELECT User, Host FROM mysql.user WHERE User = ‘app_user’; “`
If you frequently need to check connection details, you can also pull in authentication-related columns (column names vary slightly by version and may include `plugin` for the authentication method):
“`sql SELECT User, Host, plugin FROM mysql.user ORDER BY User; “`
Can you create your own “SHOW USERS” shortcut?
Yes, and it is a satisfying fix for the missing command. You can create a view that wraps the query, then simply select from it:
“`sql CREATE VIEW show_users AS SELECT User, Host FROM mysql.user ORDER BY User, Host;
— Now your “command” is: SELECT * FROM show_users; “`
Alternatively, many administrators define a shell alias so that typing a short command runs the query against the server. The view approach is portable because it lives inside the database itself, while the alias lives on your local machine. Either way, you have effectively built the `SHOW USERS` experience the server never shipped.
What about MariaDB specifically?
MariaDB began as a fork of MySQL, so the behavior is the same: there is no `SHOW USERS` command in MariaDB either, and you list accounts with `SELECT User, Host FROM mysql.user;`. The query works identically.
MariaDB does add one small convenience. The system table is also exposed as a view named `mysql.global_priv` in newer versions, but for simply listing users, `mysql.user` remains the standard and most compatible choice across both MySQL and MariaDB.
What privileges do you need to read mysql.user?
Reading `mysql.user` requires the `SELECT` privilege on the `mysql` system database. In practice this means you are connected as `root` or as an administrative account that has been granted access to the internal schema. A typical application user will *not* be able to read `mysql.user`, and that is by design, since the table contains password hashes and the full account roster.
If you run the query and get an “access denied” or “command denied” error, you are almost certainly connected as a limited user. Reconnect with an admin account:
“`bash mysql -u root -p “`
For a deeper look at auditing accounts, verifying their privileges, and spotting risky grants, see our companion guide on inspecting accounts and running a security review: .
Quick reference: queries to list MySQL users
| Goal | Query |
|---|---|
| List all accounts (user + host) | `SELECT User, Host FROM mysql.user;` |
| Clean, sorted list | `SELECT User, Host FROM mysql.user ORDER BY User, Host;` |
| Distinct usernames only | `SELECT DISTINCT User FROM mysql.user ORDER BY User;` |
| Exclude internal system accounts | `SELECT User, Host FROM mysql.user WHERE User NOT LIKE ‘mysql.%’;` |
| Find one specific user | `SELECT User, Host FROM mysql.user WHERE User = ‘app_user’;` |
| Count total accounts | `SELECT COUNT(*) FROM mysql.user;` |
| Show who you are connected as | `SELECT CURRENT_USER();` |
| Build a reusable “SHOW USERS” view | `CREATE VIEW show_users AS SELECT User, Host FROM mysql.user ORDER BY User;` |
How do you list users in phpMyAdmin or cPanel?
Not everyone wants to open a terminal, and you do not have to. If your hosting includes a graphical control panel, you can view database users with clicks instead of SQL.
In phpMyAdmin, log in as a privileged account and open the User accounts tab from the top navigation. This page renders the contents of `mysql.user` as a sortable table, showing each `User@Host` pair, its global privileges, and whether a password is set. You can also run any of the `SELECT` queries above directly from the SQL tab if you prefer.
In cPanel, the MySQL Databases section lists the database users you have created for your account, along with the databases each one is mapped to. This is account-scoped rather than server-wide, which is exactly what you want on shared hosting: you see your own users without needing server-level `root` access.
Manage MySQL users effortlessly with DarazHost
Listing and managing database users gets much easier when your hosting environment is built for it. DarazHost hosting plans include phpMyAdmin and cPanel, so you can view every database user through a clean graphical interface, no SQL or command line required. Prefer to work directly? Our VPS and dedicated plans give you full root access, letting you query `mysql.user`, create views, and administer accounts exactly the way this guide describes. Whatever your comfort level, our 24/7 expert support team is on hand to help with database access, user management, and privilege questions. .
Frequently asked questions
Is there a SHOW USERS command in MySQL? No. There is no `SHOW USERS` command in MySQL or MariaDB. To list users, query the system table instead with `SELECT User, Host FROM mysql.user;`. The `SHOW` family is reserved for objects like databases and tables, while accounts are stored as ordinary table rows.
How do I list all MySQL users from the command line? Connect as an administrative account (`mysql -u root -p`) and run `SELECT User, Host FROM mysql.user ORDER BY User;`. Add `WHERE User NOT LIKE ‘mysql.%’` to hide internal system accounts and keep the output focused on real users.
Why does the same username appear twice in the list? Because MySQL identifies accounts by a `User@Host` pair. `’app_user’@’localhost’` and `’app_user’@’%’` are distinct accounts that can have different passwords and privileges. Seeing a username repeated with different hosts is normal and expected.
What permission do I need to see mysql.user? You need the `SELECT` privilege on the internal `mysql` database, which usually means connecting as `root` or another admin account. Standard application users are intentionally blocked from reading this table because it holds password hashes.
Can I list users without SQL? Yes. In phpMyAdmin, open the User accounts tab to see all accounts in a table. In cPanel, open MySQL Databases to view the users tied to your hosting account, no queries needed.
