How to list all users on MySQL / MariaDB?

Hi friends, in this post you will learn how to list all the users in MySQL / MariaDB. This post can be quite useful if you are starting to use these database managers or if you work with them.

A good practice when working with databases is to create specific users for each one of them. These users are given specific permissions to ensure that they can only do certain things. Of course, keep in mind that many of the database operations are performed by applications and you have to be even more careful.

Also, these users have certain information that should also be displayed in certain cases. Note that you are starting to work and you need to know which users are active and thus delete them or update them with specific permissions.

And this is where many users make mistakes because for database listing there is the SHOW DATABASES; command and for the tables SHOW TABLES; but there is no such thing as SHOW TABLES for users.

So, let us start.

List all users on MySQL / MariaDB

To display the users in MySQL / MariaDB the first thing we have to do is open the terminal or connect to the server.

Then, access the MySQL / MariaDB console:

mysql -u root -p

This command has to be executed as the root user or with sudo. Then you have to enter the password of the root user.

Once you are in the MySQL / MariaDB shell you can start.

The MySQL / MariaDB users are stored in a table called users. Inside it there is all the information about them.

So, to list the users, you can use this command:

SELECT User, Host, Password FROM mysql.user;

This command will generate a screen output similar to this one:

+------------------+--------------+--------------+
| user             | host         | password     |
+------------------+--------------+--------------+
| root             | localhost    | 14@sasA269JHh |
| debian-test-user | localhost    | VmtRe32@Z   |
| angelo           | localhost    |              |
| wordpress            | localhost    |              |
| root             | 22.33.44.14|              |
| guest            | %            |              |
| angelo2          | 11.111.12.122  | 8aFroasXZ2@  |
+------------------+--------------+--------------+
7 rows in set (0.01 sec)

Here we can see some of the information that users have. In this case, I have limited the search to three fields and of course, the password is encrypted. If you want more information, you can select all the fields in that table:

SELECT * FROM mysql.user;

This will provide as much information as possible but can be dangerous because the output per screen can be quite large.

Another alternative is to show only single users. It is possible to create the same user for several databases and on different hosts. Therefore, running the above command may bring duplicates.

So if for some reason you want to know only the list of users without duplicates, just run this command:

SELECT DISTINCT User FROM mysql.user;

An example of the screen output would be the following:

+------------------+
| user             | 
+------------------+
| root             | 
| debian-test-user | 
| angelo           | 
| angelo2          | 
| guest            | 
| wordpress          | 
+------------------+
6 rows in set (0.01 sec)

This is a cleaner and more readable way to know the users in MySQL / MariaDB

Finally, it is possible to obtain the current user’s information with the following command:

SELECT user();

So now you know how to list all MySQL / MariaDB users quickly from the terminal.

Conclusion

Working with MySQL / MariaDB is a pretty serious matter full of situations that require a lot of information. Listing the users is something quite simple but it can be very useful and necessary. Thanks to this post you know how to do it without problems.

So, share this post and join our Telegram Channel.