Read in 3 minutes

last updated 

How to Show MySQL Users

Have you ever wanted 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.

Before you begin

Before you start with this tutorial, we are assuming that you already have MySQL or MariaDB server installed on your system. If not, you can easily install it by following one of the tutorials below:

All commands will be 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 switch.

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

Show 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 we can use the SELECT statement and 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.

Advertisement

A user account in MySQL consists of user name and host name parts.

You can use the desc mysql.user; statement to display information about each of a table’s columns. Once you know the column name 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 can 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)

Conclusion

In this tutorial, we have shown you how to get a list of all MySQL users accounts by retrieving data from the user table in the mysql database.

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