MySQL MariaDB

MySQL Show Users in Database

If you have ever tried to get a list of users available in your MySQL server, you will notice there is no native command to do this. For example, to get a list of databases, you can simply query SHOW DATABASES. However, this is not the case for users.

This guide will show you how you can view the users available in your MySQL using simple and quick commands.

MySQL Dump All Users

To get the information about the users in the server, we query the users’ table available in the MYSQL database for specific information.

Ensure that the server is up and running. Next, log in to the server using the root user as shown in the command:

mysql -u root -p

For example, retrieve the User and Host rows in the MySQL.users table using the SELECT statement as shown below:

SELECT User, Host FROM mysql.user;

The above query will display the username and the host of all the users on the server. Here is an example output:

mysql> SELECT User, Host FROM mysql.user;
+------------------+--------------+
| User             | Host         |
+------------------+--------------+
| LT.Spock         | 10.10.0.14   |
| Uhura            | 172.23.58.23 |
| captain.kirk     | localhost    |
| mysql.infoschema | localhost    |
| mysql.session    | localhost    |
| mysql.sys        | localhost    |
| root             | localhost    |
+------------------+--------------+
7 rows in set (0.00 sec)

NOTE: In the above query, we limit the rows to User and Host but the table contains more rows such as account_locked, User_attributes, Delete_Priv, etc.

To view all the rows about the table, use the DESC query as shown below:

DESC mysql.user;

The output is as shown below:

Show Current User

To view the current user logged in, you can use the current_user() function as shown below:

An example output is below:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

Show Logged in Users

Using the information_schema database and the process list table, we can get information about the logged-in users. The process_list table contains the list of operations currently being executed by various threads in the server.

Source: https://dev.mysql.com/doc/refman/8.0/en/information-schema-processlist-table.html

To view the logged-in users, use the Query as shown below:

SELECT id, user, host, db, command, state FROM information_schema.processlist;

This will display information such as the id, user, etc., of the logged-in users and the currently running command.

Here is an example output:

Conclusion

In this tutorial, we discussed how to view users and their corresponding information by querying the MySQL.users table.

Thank you for reading.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list