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:

snippet

mysql> Select user from mysql.user;

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:

snippet

> mysql -u root -p
Enter password: *********
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;

We will get the following output where we can see the five users in our local database:

MySQL Show Users

If we want to see more information on the user table, execute the command below:

snippet

mysql> DESC user;

It will give the following output that lists all the available columns of the mysql.user database:

MySQL Show Users

To get the selected information like as hostname, password expiration status, and account locking, execute the query as below:

snippet

mysql> SELECT user, host, account_locked, password_expired FROM user;

After the successful execution, it will give the following output:

MySQL Show Users

Show Current User

We can get information of the current user by using the user() or current_user() function, as shown below:

snippet

mysql> Select user();
         or,
mysql> Select current_user();

After executing the above command, we will get the following output:

MySQL Show Users

Show Current Logged User

We can see the currently logged user in the database server by using the following query in the MySQL server:

snippet

mysql> SELECT user, host, db, command FROM information_schema.processlist;

The above command gives the output, as shown below:

MySQL Show Users

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.