MySQL Show Users in Database

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:

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

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

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:

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:

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:

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.