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 themysql
database. - Use
select current_user()
to show the current user.