How to Show All Users in MySQL

Database administrators may need to regularly keep track of all the users on their system, to see if someone has obtained unauthorized access to their databases. For this purpose, they might need to list all user accounts or get list of user accounts on their system. In this article, we will learn how to show all users in MySQL.

How to Show All Users in MySQL

Every MySQL database installation has a table called mysql which stores information about the different users who have access to the databases on it. Here is a simple command to view all users along with their hosts on your MySQL database system.

mysql> use mysql;
mysql> select user,host from mysql.users;

Here is a sample output

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| root             | demohost  |
| root             | 127.0.0.1 |
| debian-sys-maint | localhost |
|                  | %         |
+------------------+-----------+

As you can see, the above output may list the same username multiple times, in case they have access from multiple hosts. To view only the unique usernames who can access your system, run the following command.

mysql> use mysql;
mysql> select distinct user from mysql.users;

Here is the sample output.

+------------------+
| User             |
+------------------+
| root             |
| debian-sys-maint |
+------------------+

If you want to list user accounts from specific host you can use the WHERE clause to filter above results based on specific hosts. Here is an example to list all local users.

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

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| root             | 127.0.0.1 |
| debian-sys-maint | localhost |
+------------------+-----------+

Similarly, here is the command to list all remote users.

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

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | demohost  |
| root             | 54.43.32.1|
| debian-sys-maint | temphost  |
+------------------+-----------+

You can also run these queries in shell script or cronjob to automatically get list of users on a regular basis. Here is an example. Open crontab

$ crontab -e

Add the following line to run the first query above every day at 10.a.m. Replace <host>,<username> and <password> with your database server’s host, username and password respectively.

0 10 * * * mysql -h <host> -u <username> -p<password> -e "select user,host from mysql.users" >/dev/null 2>&1

Save and close the file.

In this article, we have learnt how to list all account users in MySQL. You can customize the above query results as per your requirement.

How to Show All Users in MySQL

Related posts:

Leave a Reply Cancel reply