Introduction
While working with databases, sometimes it becomes necessary to show the list of users to manage the Database well. So how will you do that on the MySQL server? You might be wondering if a command similar to SHOW DATABASES, SHOW TABLES that show us all databases on MySQL server and tables in the selected database. Unfortunately, with MySQL, the case is a bit different. There is no ‘SHOW USERS’ command in MySQL.
Source: tenor.com
Therefore to list all users, we have to use queries.
Now let's start with the implementation:
Show Users
To list all users in a MySQL database server, we can use the following query:
SELECT user FROM mysql.user;
In the above statement, we query the user data from the user table of the MySQL database.
After successfully executing the above statement, we will get the user data from the user table.
Now let’s see how we will use this query:
To use the above query, one 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;
We will get the following output to see the list of users.
+-----------+ | user | +-----------+ | mysql.sys | | mysqlxsys | | root | +-----------+ 3 rows in set (0.00 sec) |
To get more information about the user table, we can preview its columns. To this, use the following command.
DESC user;
The above command will give the lists of all the available columns of the mysql.user database as an output.
For information like hostname, password expiration status, and account locking, execute the following query:
mysql> SELECT user, host, account_locked, password_expired FROM user;
The output of the following query will be similar to the output given below.