How to List Users in MySQL

MySQL is a popular database that allows multiple users to easily build data-driven applications and perform data analysis. The number of users accessing a MySQL server keeps growing over time and it is important to keep an eye on their activities. So database administrators need to determine all the users that are able to access MySQL databases to prevent unauthorized actions. There are numerous ways to get the list of users in MySQL. In this article, we will learn how to do this.

How to List Users in MySQL

Here are the different ways to list MySQL users. You will need to have admin privileges to be able to run most or all of these queries. Regular users will not be able to run them.

1. List All User Accounts

Every MySQL server creates a bunch of system databases & tables at the time of installation. They are updated as the data and users on your server evolve over time. All MySQL user accounts on your server are present in one such table mysql.users table. It contains information about all MySQL users, their IP address, encrypted password, etc. You can check its columns by logging into MySQL and using the following query.

mysql> desc mysql.user

You get a list of all MySQL usernames using the following query.

mysql> select distinct user from mysql.users

You will see an output like the following.

+-------+
| User  |
+-------+
| root  |
+-------+
| user1 |
+-------+

Please note, in MySQL.users table, there are multiple rows of information for each user. Therefore, we need to use distinct keyword while selecting usernames.

2. List Users & Hosts

Sometimes, you may have multiple users with the same username but different host name or IP address. In this case, you can also select host column along with username, in the above query.

mysql> select user, host from mysql.user;

You will see the following output which displays usernames and hosts.

+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | localhost   |
| user1     | localhost   |
| user1     | 54.43.32.21 |
+-----------+-------------+

You will see that user1 is listed twice – with host as localhost and as 54.43.32.21. This means that user1 can access your database from localhost or from IP 54.43.32.21 and not from anywhere else.

This is a good way to determine all remote users as well as local users. Here is the query to get a list of all local users.

mysql> select user, host from mysql.user 
       where host='localhost' or host='127.0.0.1';

You will see the following output listing all local users.

+-----------+-------------+
| user      | host        |
+-----------+-------------+
| root      | localhost   |
| user1     | localhost   |
+-----------+-------------+

Here is the query to get all remote users.

mysql> select user, host from mysql.user 
       where host<>'localhost' and host<>'127.0.0.1';

Here is the output containing all remote users.

+-----------+-------------+
| user      | host        |
+-----------+-------------+
| user1     | 54.43.32.21 |
+-----------+-------------+

3. List User, Host & Password

You can also view a list of users along with their encrypted passwords.

mysql> select user, host, password from mysql.user

You will see the following kind of output. We have hidden password characters using ‘…’ but it will be an alphanumeric string.

+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *4ACFE...029441                           |
| user1     | localhost | *EECD8...2D9AAA                           |
+-----------+-----------+-------------------------------------------+

4. Users with Locked Accounts

If you have locked some MySQL users then you may want to check up on them from time to time, to ensure they remain so. Here is the query to check if an account is locked or not. Starting MySQL 5.7.6, mysql.user table contains a column account_locked. It contains Y for locked accounts and N for accounts that are not locked.

mysql> select user, account_locked from mysql.user;

You will see the following output.

+-----------+-----------------+
| user      | account_locked  |
+-----------+-----------------+
| root      | N               |
| user1     | Y               |
+-----------+-----------------+

Please note, this feature is available only on MySQL >=5.7.6. Older systems will need to upgrade to this version for this feature.

5. List Accounts with Expired Password

When a user’s password has expired, they are unable to log into their MySQL account. So database administrators will need to check if that is the case, before taking any action. You can do this using the following query.

mysql> select user, password_expired from mysql.user;

You will see the following output which contains the value of password_expired column for each username. It is Y if password expired and N if it is not expired.

+-----------+-------------------+
| user      | password_expired  |
+-----------+-------------------+
| root      | N                 |
| user1     | Y                 |
+-----------+-------------------+

If you want to check whether a specific account’s password is expired or not, then you can use a WHERE clause for this purpose.

mysql> select user, password_expired from mysql.user
       where user=<username>;

Please note, this is not available in older MySQL versions.

6. List Logged in Users

So far all the above queries are about the user accounts that are present on your MySQL server, whether they are logged in or not, whether they are locked or not.

What if you want to get a list of users that are presently logged into your server? For this purpose, you can use the following SQL query.

mysql> select user();

You will see the following list of logged in users, with their usernames and host.

+-----------------+
| user()          |
+-----------------+
| user1@localhost |
+-----------------+

7. Logged In Users with Commands

If you also want to view the different commands executed by users, you can get this information from information_schema.processlist table.

mysql> select user, host, db, command from information_schema.processlist;

You will see an output as the following.

+------+-----------------+------+---------+
| user | host            | db   | command |
+------+-----------------+------+---------+
| root | localhost:49790 | NULL | Query   |
+------+-----------------+------+---------+

Conclusion

In this article, we have learnt several easy ways to quickly get a list of users in MySQL. Database administrators can use them to understand who all are presently accessing their database server. Each of these commands works for a different use case. Depending on your requirement, you can use any of them. You can run these queries from MySQL command line client or GUI tools such as Workbench and HeidiSQL. Just make sure that the admin user credentials you use, actually has the permission to run these queries.

Also read:

How to Get Character Set of MySQL Database
How to Remove Item from JavaScript Array
How to Check if File Exists in Python