How to Show/List Users in MySQL

Updated on

•

4 min read

MySQL Show Users

Have you ever needed to get a list of all users in your MySQL server? There are commands to show databases and tables, but there is no MySQL show users command.

This tutorial explains how to list all user accounts in a MySQL database server through the command line. We’ll also show you how the find out which users have access to a given database.

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 haven’t set a password for your MySQL root user, you can omit the -p option.

If you forgot your MySQL root password, you can reset it by following these instructions .

Show All MySQL Users

MySQL stores information about the users 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.users 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 command above shows only two columns from the mysql.user table (User and Host), This table contains more than 40 columns such as Password, Select_priv, Update_priv, etc.

A user account in MySQL consists of two parts: a user name and hostname.

Use the desc mysql.user; statement to display information about the table’s columns. Once you know the column name, you can run a query against a selected data.

For example, to get a list of all MySQL users accounts including information about the password and whether it is active or expired, you would use the following query:

SELECT User, Host, Password, password_expired FROM mysql.user;
+----------------+-----------+-------------------------------------------+------------------+
| User           | Host      | Password                                  | password_expired |
+----------------+-----------+-------------------------------------------+------------------+
| root           | localhost |                                           | N                |
| luke           | %         | *ADC3B5B27617732CD6320A2DA976258E149A7EC8 | N                |
| yoda           | %         | *9550E004046348198A143A115550E1262209FB6F | N                |
| jabba          | 10.10.0.6 | *F91C86B486B945C083B61A05FF6E197560D187EC | Y                |
| jabba          | 10.10.0.9 |                                           | Y                |
| chewbacca      | localhost | *17F2B1E48029294841AD66772BEBB7E6E6A005AF | N                |
| leia           | localhost | *74409C8DB55AC1A6829D801915981C46EDBFC64A | N                |
| han            | localhost | *7B3022FCAEC3534CE67C68322D8AF0C240D95745 | N                |
+----------------+-----------+-------------------------------------------+------------------+
8 rows in set (0.00 sec)

Show Users that Have Access to a Particular Database

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

You can query the table to find out which users have access to a given database and the level of the privileges.

For example, to get a list of all users that have some level access to the database named db_name you would use the following query:

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 information only about the user accounts that have access to a given database, without displaying the privileges use:

SELECT db, host, user FROM mysql.db WHERE db = 'db_name'
+---------+-----------+---------+
| db      | host      | user    |
+---------+-----------+---------+
| db_name | localhost | db_user |
+---------+-----------+---------+

The following query will show you information about all databases and associated users:

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       |
+------------------+-----------+-----------+

Conclusion

In this tutorial, we have shown how to get a list of all MySQL users and find out which users have access to a particular database.

Feel free to leave a comment if you have any questions.

mysql mariadb