MySQL SHOW USERS: List All Users in a MySQL Database Server
Summary: this tutorial shows you how to list users in a MySQL database.
MySQL show users: list all users
Are you looking for the MySQL SHOW USERS command? Unfortunately, MySQL does not have the SHOW USERS command like SHOW DATABASES, SHOW TABLES, etc., therefore to list all users in a MySQL database server, you use the following query:
SELECT
user
FROM
mysql.user;
Code language: SQL (Structured Query Language) (sql)
In this statement, we queried user data from the user table of the mysql database.
To execute this query, you must log in to the MySQL database server as an administrator.
>mysql -u root -p
Enter password: ***********
mysql> use mysql;
Database changed
mysql> SELECT user FROM user;
Code language: SQL (Structured Query Language) (sql)
The following shows the output of the query above:
+-----------+
| user |
+-----------+
| mysql.sys |
| mysqlxsys |
| root |
+-----------+
3 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
As you can see, we have three users in our local database.
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)
For example, to show users and other information such as host, account locking, and password expiration status, you use the following query:
SELECT
user,
host,
account_locked,
password_expired
FROM
user;
Code language: SQL (Structured Query Language) (sql)
Here is the output of the query.
Show current user
To get the information on the current user, you use the user() function as shown in the following statement:
mysql> SELECT user();
+-----------------+
| user() |
+-----------------+
| local@localhost |
+-----------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Or you use the current_user() function:
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| local@localhost |
+----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
In this case, the current user is local@localhost.
Show current logged 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;| user | host | db | command |
+——-+—————–+—————+———+
| local | localhost:50591 | classicmodels | Sleep |
| root | localhost:50557 | NULL | Query |
+——-+—————–+—————+———+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
As use can see, there are two users that are currently logged in the MySQL database, one is executing a query while the other is “sleep”.
In this tutorial, you have learned how to list all users in a MySQL database server by querying data from the user table in the mysql database.