LiveChat 
Support 
How to Check MySQL/MariaDB Users: List, Inspect, and Audit Database Accounts
Knowing exactly which accounts can connect to your database is one of the most fundamental skills in database administration. Whether you are onboarding a new application, troubleshooting a failed login, or running a security audit, you need a reliable way to check MySQL users and understand what each one can do.
This guide walks through the canonical queries for listing database accounts in both MySQL and MariaDB, how to find out who you are currently logged in as, how to inspect the privileges attached to any account, and how to use this information to spot security weaknesses such as anonymous logins and over-privileged users.
Key Takeaways
• Use `SELECT User, Host FROM mysql.user;` to list every account on the server.
• `SELECT current_user();` and `SELECT user();` tell you who you are connected as (they can differ).
• `SHOW GRANTS FOR ‘user’@’host’;` reveals exactly what an account is permitted to do.
• In MySQL/MariaDB an account is the username + host pair, so `’bob’@’localhost’` and `’bob’@’%’` are two distinct accounts.
• Querying `mysql.user` requires elevated privileges (typically `SELECT` on the `mysql` system database).
How do you list all users in MySQL or MariaDB?
The authoritative source of user accounts is the `user` table inside the `mysql` system database. Every account the server recognizes has a row there. The canonical query is short and works identically on MySQL and MariaDB:
“`sql SELECT User, Host FROM mysql.user; “`
This returns two columns: the username and the host from which that username is allowed to connect. A typical result looks like this:
“`sql +——————+———–+
| User | Host | +——————+———–+
| app_user | % |
| app_user | localhost |
| root | localhost |
| mysql.sys | localhost |
| backup_agent | 10.0.0.5 | +——————+———–+ “`
If you want a cleaner, sorted view, you can order the results:
“`sql SELECT User, Host FROM mysql.user ORDER BY User, Host; “`
You do not need to memorize every column in `mysql.user`. For a quick audit, `User` and `Host` are enough to see the shape of who can connect. To explore the full structure of the table on your specific version, run `DESCRIBE mysql.user;`.
Why does the same username appear more than once?
This is the single most important concept for understanding MySQL and MariaDB accounts, and it trips up newcomers constantly.
In MySQL and MariaDB, the account is not just the username — it is the username plus the host. `’bob’@’localhost’` and `’bob’@’%’` are two completely separate accounts. They can have different passwords, different privileges, and even different authentication plugins. When you see “bob” listed twice in `mysql.user`, you are not looking at a duplicate; you are looking at two distinct security principals that merely happen to share a login name. Granting a privilege to one does nothing for the other.
The host part controls where a connection is allowed to originate:
- `localhost` — connections made from the same machine, typically over a Unix socket.
- `%` — a wildcard meaning any host (use with caution).
- `10.0.0.5` — a specific IP address.
- `10.0.0.%` — any host in that subnet.
This is why `’root’@’localhost’` and `’root’@’%’` are treated so differently. The first lets root connect only from the database server itself, which is the safe default. The second would let root connect from anywhere on the network, which is a serious risk and something a security audit should flag immediately.
How do you check which user you are currently logged in as?
When a query fails with a permissions error, the first question is always: *who am I connected as right now?* Two functions answer this, and the difference between them is subtle but useful.
“`sql SELECT current_user(); SELECT user(); “`
- `user()` returns the username and host you supplied when connecting — the account you *requested*.
- `current_user()` returns the account the server actually authenticated you as and is using to evaluate your privileges.
Most of the time these match. They diverge when, for example, you connect as a user that does not have an exact account match and the server falls back to an anonymous account, or when a `DEFINER`-context stored routine changes the effective account. If a privilege check is behaving unexpectedly, comparing these two values is often the fastest diagnostic.
You can also see your current database and version at the same time for a complete picture of your session:
“`sql SELECT current_user(), user(), database(), version(); “`
How do you see what a user is allowed to do?
Listing accounts tells you *who* exists. To find out *what* each account can do, use `SHOW GRANTS`. This displays the exact privilege statements assigned to a given account, formatted as the `GRANT` commands that would recreate them.
“`sql SHOW GRANTS FOR ‘app_user’@’%’; “`
A result might look like this:
“`sql +——————————————————————+
| Grants for app_user@% | +——————————————————————+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `app_user`@`%` | +——————————————————————+ “`
Read these from the most general to the most specific. `GRANT USAGE ON *.*` means “this account may connect but has no global privileges” — it is effectively a no-op placeholder. The second line is where the real permissions live: read/write access scoped to the `shop` database only.
To inspect the grants for the account you are currently using, you can reference `current_user()` directly:
“`sql SHOW GRANTS FOR current_user(); “`
Remember the username+host rule here too: `SHOW GRANTS FOR ‘app_user’@’%’;` and `SHOW GRANTS FOR ‘app_user’@’localhost’;` may return entirely different results because they describe different accounts.
Which queries should you keep handy?
The following table collects the everyday commands for checking and inspecting database accounts. These work on both MySQL and modern MariaDB releases.
| Goal | Query |
|---|---|
| List all accounts | `SELECT User, Host FROM mysql.user;` |
| List accounts, sorted | `SELECT User, Host FROM mysql.user ORDER BY User, Host;` |
| Who am I (requested) | `SELECT user();` |
| Who am I (authenticated) | `SELECT current_user();` |
| Show a user’s privileges | `SHOW GRANTS FOR ‘user’@’host’;` |
| Show my own privileges | `SHOW GRANTS FOR current_user();` |
| Find anonymous accounts | `SELECT User, Host FROM mysql.user WHERE User = ”;` |
| Find accounts allowed from anywhere | `SELECT User, Host FROM mysql.user WHERE Host = ‘%’;` |
| Count total accounts | `SELECT COUNT(*) FROM mysql.user;` |
| Inspect table structure | `DESCRIBE mysql.user;` |
How do you audit users for security problems?
Once you can list accounts and read their grants, you have everything you need for a basic security audit. Three categories of weakness are worth checking on every server.
Find anonymous users
An anonymous account has an empty username (`”`). Historically these were created by default installers and allowed unauthenticated local connections. They are a classic security gap and should almost always be removed.
“`sql SELECT User, Host FROM mysql.user WHERE User = ”; “`
If this returns any rows, investigate and remove them once you confirm nothing depends on them.
Find accounts that can connect from anywhere
Accounts with a host of `%` accept connections from any network location. Some are legitimate (a web app connecting from a fleet of servers), but each one widens your attack surface and deserves justification.
“`sql SELECT User, Host FROM mysql.user WHERE Host = ‘%’; “`
Pay special attention to administrative usernames like `root`, `admin`, or `mysql` appearing with a `%` host — that combination is rarely intentional and frequently dangerous.
Find overly-privileged accounts
The principle of least privilege says an application account should hold only the rights it actually needs. To audit this, run `SHOW GRANTS` for each non-system account and look for global privileges (`ON *.*`) where a database-scoped grant (`ON dbname.*`) would suffice. Privileges such as `SUPER`, `ALL PRIVILEGES`, `GRANT OPTION`, `FILE`, and `SHUTDOWN` on an ordinary application account are red flags worth questioning.
A note on passwords: in modern MySQL and MariaDB, credential details live in columns such as `authentication_string` and `plugin` rather than a simple `Password` column. The exact column names vary by version, so confirm with `DESCRIBE mysql.user;` before scripting any password-related checks. The most reliable signal that an account can authenticate without a real secret is an empty credential combined with a plugin that performs no verification — review these manually.
What privileges do you need to check users?
Reading `mysql.user` is itself a privileged operation. To run `SELECT User, Host FROM mysql.user;`, your account needs `SELECT` permission on the `mysql` system database (administrative accounts such as `root` have this by default). Without it, the query returns an “access denied” error.
`SHOW GRANTS FOR current_user();` and `SHOW GRANTS` for your own account generally work without special privileges, because every account is allowed to inspect its own permissions. Viewing the grants of *other* accounts, however, requires the same level of access to the `mysql` database. If you administer a database on shared hosting where you do not have full server-level access, your control panel’s database tools will usually expose user and privilege management through a friendlier interface instead.
Managing database users with DarazHost
Running these audits is far easier when your hosting environment gives you the right level of access. ** include cPanel with the MySQL Databases tool and phpMyAdmin**, so you can create database users, assign privileges, and review who has access through a clear visual interface — no need to memorize `mysql.user` column names for routine tasks.
For teams that need to run full `mysql.user` audits, configure authentication plugins, or lock down `%`-host accounts at the server level, ** provides root access to the complete MySQL/MariaDB instance. That means unrestricted querying of the system tables, custom security policies, and the freedom to harden your database exactly how your compliance requirements demand. Every plan is backed by 24/7 expert support**, so if a permissions issue blocks a deployment at 3 a.m., someone is there to help.
Frequently asked questions
Does `SELECT User, Host FROM mysql.user;` work the same in MariaDB? Yes. MariaDB inherited the `mysql.user` table from MySQL, so the canonical listing query is identical. Some internal columns differ between the two systems and across versions, but the `User` and `Host` columns — the ones you need for listing accounts — are consistent.
Why does `SHOW GRANTS` say a user has no privileges when they clearly can log in? You are almost certainly checking a different account than the one in use. `SHOW GRANTS FOR ‘bob’@’localhost’` and `SHOW GRANTS FOR ‘bob’@’%’` describe separate accounts. Confirm the exact host with `SELECT User, Host FROM mysql.user WHERE User = ‘bob’;`, then run `SHOW GRANTS` against the precise username+host pair.
What is the difference between `user()` and `current_user()`? `user()` shows the credentials you supplied when connecting, while `current_user()` shows the account the server actually authenticated and is using for privilege checks. They usually match but can differ when the server maps your connection to a fallback or anonymous account.
How do I find users who can connect from any host? Run `SELECT User, Host FROM mysql.user WHERE Host = ‘%’;`. Each result is an account that accepts connections from any network location. Review them carefully, especially any administrative usernames, and tighten the host where a wildcard is not strictly required.
I get “access denied” when I query `mysql.user`. Why? That query requires `SELECT` privilege on the `mysql` system database, which ordinary accounts do not have. Connect with an administrative account (such as `root`), or on shared hosting use your control panel’s database tools to view users instead.
