How to list users in MySQL or MariaDB

Listing database accounts provides a quick audit of who can authenticate, where connections are allowed from, and which entries might be obsolete or risky. Keeping an accurate account inventory also speeds up troubleshooting when logins fail or permissions drift.

MySQL and MariaDB store account definitions in the system schema (the mysql database), where each account is identified by a User name combined with a Host pattern. During authentication, the server matches the incoming connection to the best-fitting User@Host entry, so the same username can exist multiple times with different host rules.

Reading mysql.user usually requires an administrative account (for example root) or equivalent privileges, and selecting sensitive columns can expose credential hashes. Use queries that return only User and Host for routine auditing, and remember that localhost and remote client hosts are distinct account scopes.

1. Open an interactive mysql client session with an account that can read mysql.user.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   mysql>

   Add --host and --port when connecting to a remote server.

2. Show the authenticated account used for privilege checks.

   mysql> SELECT USER() AS client_user, CURRENT_USER() AS authenticated_user;
   +----------------+-------------------+
   | client_user    | authenticated_user|
   +----------------+-------------------+
   | root@localhost | root@localhost    |
   +----------------+-------------------+
   1 row in set (0.00 sec)

   CURRENT_USER() reflects the matched User@Host entry used for privilege evaluation.

3. List all accounts as User and Host pairs.

   mysql> SELECT User, Host FROM mysql.user ORDER BY User, Host;
   +------------------+-----------+
   | User             | Host      |
   +------------------+-----------+
   | app_user         | 10.0.0.%  |
   | app_user         | localhost |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+
   6 rows in set (0.00 sec)

   

   An account is the combination of User and Host, so repeated usernames represent different accounts.

4. Generate copy-ready 'user'@'host' identifiers for use with SHOW GRANTS.

   mysql> SELECT CONCAT(QUOTE(User), '@', QUOTE(Host)) AS account
       -> FROM mysql.user
       -> ORDER BY User, Host;
   +----------------------------------+
   | account                          |
   +----------------------------------+
   | 'app_user'@'10.0.0.%'            |
   | 'app_user'@'localhost'           |
   | 'mysql.infoschema'@'localhost'   |
   ##### snipped #####
   +----------------------------------+
   6 rows in set (0.00 sec)

   Avoid selecting authentication_string, password, or similar credential fields on shared terminals or in shared logs.

5. Display grants for a single account using the exact 'user'@'host' value from the list.

   mysql> SHOW GRANTS FOR 'app_user'@'10.0.0.%';
   +---------------------------------------------------------------------+
   | Grants for app_user@10.0.0.%                                        |
   +---------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO `app_user`@`10.0.0.%`                          |
   | GRANT SELECT, INSERT, UPDATE ON `appdb`.* TO `app_user`@`10.0.0.%`   |
   +---------------------------------------------------------------------+
   2 rows in set (0.00 sec)

   

   A mismatched Host value returns an error even when the username exists.

6. Exit the mysql client session.

   mysql> exit
   Bye