How to Show Users in MySQL

Summary: in this tutorial, you will learn various techniques to show users in MySQL server.

Listing all users by querying the user table

First, open Command Prompt on Windows or Terminal on Unix-like systems and log in to the MySQL server:

mysql -u root -p

Second, change the current database to the mysql database:

use mysql;Code language: PHP (php)

Third, retrieve all users in the current database server by querying the user column of the user table:

select user from user;Code language: JavaScript (javascript)

It returns the user list like this:

+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

To get more information on the user table, you can preview its columns using the following command:

DESC user;Code language: SQL (Structured Query Language) (sql)

The following example lists all users and other information such as host, account locking, and password expiration status:

SELECT 
  user, 
  host, 
  account_locked, 
  password_expired 
FROM 
  user;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+-----------+----------------+------------------+
| user             | host      | account_locked | password_expired |
+------------------+-----------+----------------+------------------+
| mysql.infoschema | localhost | Y              | N                |
| mysql.session    | localhost | Y              | N                |
| mysql.sys        | localhost | Y              | N                |
| root             | localhost | N              | N                |
+------------------+-----------+----------------+------------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

Show the current user

To get the information on the  current user, you use the user() function as shown in the following statement:

SELECT user();

Output:

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Alternatively, you use the current_user() function:

SELECT current_user();

Output:

+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Show all currently logged-in users

To list all users that are currently logged in the MySQL database server, you execute the following statement:

SELECT 
  user, 
  host, 
  db, 
  command 
FROM 
  information_schema.processlist;Code language: CSS (css)

Output:

+-----------------+-----------------+-------+---------+
| user            | host            | db    | command |
+-----------------+-----------------+-------+---------+
| root            | localhost:62277 | mysql | Query   |
| event_scheduler | localhost       | NULL  | Daemon  |
+-----------------+-----------------+-------+---------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• List all users by querying from the user table of the mysql database.
• Use select current_user() to show the current user.