How to Show/List Users in MySQL

MySQL has commands to show databases and tables, but there is no built-in SHOW USERS command. Instead, you query the mysql.user table directly.

This guide explains how to list all user accounts in a MySQL server, check their privileges, and see who is currently connected.

Before You Begin #

We are assuming that you already have MySQL or MariaDB server installed on your system.

All commands are executed inside the MySQL shell as a root user. To access the MySQL shell, type the following command and enter your MySQL root user password when prompted:

mysql -u root -p

If you have not set a password for your MySQL root user, you can omit the -p option.

Show All MySQL Users #

MySQL stores information about user accounts in a table named user in the mysql database.

To get a list of all MySQL user accounts, use the SELECT statement to retrieve all rows from the mysql.user table:

SELECT User, Host FROM mysql.user;

The output should look similar to below:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| luke             | %         |
| yoda             | %         |
| jabba            | 10.10.0.6 |
| jabba            | 10.10.0.9 |
| chewbacca        | localhost |
| leia             | localhost |
| han              | localhost |
+------------------+-----------+
8 rows in set (0.00 sec)

The query above shows only two columns (User and Host). The mysql.user table contains many more columns, including authentication_string, account_locked, and various privilege columns such as Select_priv and Update_priv.

Use DESC mysql.user; to display all available columns. Once you know the column names, you can run a query against the specific data you need.

For example, to list all user accounts with their authentication plugin and account status, run:

SELECT User, Host, plugin, account_locked, password_expired FROM mysql.user;

+------------------+-----------+-----------------------+----------------+------------------+
| User             | Host      | plugin                | account_locked | password_expired |
+------------------+-----------+-----------------------+----------------+------------------+
| root             | localhost | caching_sha2_password | N              | N                |
| luke             | %         | caching_sha2_password | N              | N                |
| yoda             | %         | caching_sha2_password | N              | N                |
| jabba            | 10.10.0.6 | caching_sha2_password | N              | Y                |
| jabba            | 10.10.0.9 | caching_sha2_password | Y              | Y                |
| chewbacca        | localhost | caching_sha2_password | N              | N                |
| leia             | localhost | caching_sha2_password | N              | N                |
| han              | localhost | mysql_native_password | N              | N                |
+------------------+-----------+-----------------------+----------------+------------------+
8 rows in set (0.00 sec)

Notice that most accounts use caching_sha2_password, which is the default authentication plugin since MySQL 8.0. The han account still uses the older mysql_native_password plugin.

Check Privileges for a Specific User #

After listing your users, you will often want to see what a particular account is allowed to do. The SHOW GRANTS statement displays the privileges assigned to a user:

SHOW GRANTS FOR 'luke'@'%';

+-------------------------------------------+
| Grants for luke@%                         |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `luke`@`%`          |
| GRANT ALL PRIVILEGES ON `blog`.* TO `luke`@`%` |
+-------------------------------------------+
2 rows in set (0.00 sec)

The output shows that luke has full access to the blog database and no global privileges beyond the basic USAGE grant (which means “no privileges”). You must specify both the username and the host part; otherwise MySQL defaults to 'user'@'%'.

To check the grants for the account you are currently logged in as, run SHOW GRANTS; without the FOR clause.

Show Users that Have Access to a Particular Database #

The information about database-level privileges is stored in the mysql.db table.

You can query this table to find out which users have access to a given database and what level of privileges they hold.

For example, to get a detailed view of all privileges for the database named db_name, use the \G modifier to display the results vertically:

SELECT * FROM mysql.db WHERE Db = 'db_name'\G;

*************************** 1. row ***************************
                 Host: localhost
                   Db: db_name
                 User: db_user
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)

To fetch only the user accounts that have access to a given database, without displaying all the privilege columns:

SELECT db, host, user FROM mysql.db WHERE db = 'db_name';

+---------+-----------+---------+
| db      | host      | user    |
+---------+-----------+---------+
| db_name | localhost | db_user |
+---------+-----------+---------+

The following query shows all databases and the users associated with each one:

SELECT db, host, user FROM mysql.db;

+------------------+-----------+-----------+
| db               | host      | user      |
+------------------+-----------+-----------+
| db_name          | localhost | db_user   |
| ghost_production | localhost | chewbacca |
| blog_db          | localhost | leia      |
| linuxize         | localhost | han       |
+------------------+-----------+-----------+

Show Currently Connected Users #

To see which users are connected to the server right now, use SHOW PROCESSLIST:

SHOW PROCESSLIST;

+----+-----------+-----------+--------+---------+------+----------+------------------+
| Id | User      | Host      | db     | Command | Time | State    | Info             |
+----+-----------+-----------+--------+---------+------+----------+------------------+
|  5 | root      | localhost | mysql  | Query   |    0 | starting | SHOW PROCESSLIST |
| 12 | luke      | 10.0.0.3  | blog   | Sleep   |  340 |          | NULL             |
+----+-----------+-----------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

Each row represents an active connection. The Command column tells you what the connection is doing: Query means it is running a statement, and Sleep means the connection is idle.

Quick Reference #

For a printable quick reference, see the MySQL/MariaDB cheatsheet .

FAQ #

Does MySQL have a SHOW USERS command?
No. Unlike SHOW DATABASES or SHOW TABLES, there is no built-in SHOW USERS statement. You need to query the mysql.user table with a SELECT statement instead.

Do these commands work with MariaDB?
Most of them do, but MariaDB account metadata differs from MySQL in newer releases. If you are working on MariaDB, verify the available columns in mysql.user with DESC mysql.user; before using MySQL-specific examples such as account_locked.

Conclusion #

You can list MySQL users by querying mysql.user, check what they can do with SHOW GRANTS, and see active connections with SHOW PROCESSLIST. For more on managing users and databases or changing a user password , see the linked guides.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page