MySQL Show Users/List All Users
Sometimes you want to manage a database in MySQL. In that case, we need to see the list of all user's accounts in a database. Most times, we assume that there is a SHOW USERS command similar to SHOW DATABASES, SHOW TABLES, etc. for displaying the list of all users available in the database server. Unfortunately, MySQL database does not have a SHOW USERS command to display the list of all users in the MySQL server. We can use the following query to see the list of all user in the database server:
After the successful execution of the above statement, we will get the user data from the user table of the MySQL database server.
Let us see how we can use this query. First, we have to open the MySQL server by using the mysql client tool and log in as an administrator into the server database. Execute the following query:
We will get the following output where we can see the five users in our local database:
If we want to see more information on the user table, execute the command below:
It will give the following output that lists all the available columns of the mysql.user database:
To get the selected information like as hostname, password expiration status, and account locking, execute the query as below:
After the successful execution, it will give the following output:
Show Current User
We can get information of the current user by using the user() or current_user() function, as shown below:
After executing the above command, we will get the following output:
Show Current Logged User
We can see the currently logged user in the database server by using the following query in the MySQL server:
The above command gives the output, as shown below:
In this output, we can see that there are currently four users logged in the database, where one is executing a Query, and others show in Sleep or Daemon status.