How to Show/List MySQL Users?

How to Show/List MySQL Users?

by

MySQL is used and managed with users which have different privileges. One of the most popular questions about MySQL is how can I list all MySQL users. As a powerful database server, MySQL provides a lot of different ways to list all users and user-related information. In this tutorial, we will examine how to list MySQL users and related information.

Connect MySQL Shell

The MySQL database server users can be listed and displayed via the MySQL shell. This method can be also called as using command line interface in order to list MySQL users. The Select User ... SQL statement is used in order to show users which is stored in the special table named mysql.user . The mysql.user is a special table which stores the MySQL users and related information like user name, user password, user premissions etc.

First, we connect to the MySQL shell using a command-line interface with the mysql command by providing the user name to connect and password. In the following example, we use the user named root to connect the MySQL server. The -u option is used to specify the username and -p option is used to provide password login.

$ sudo mysql -u root -p
Connect MySQL Shell In Linux

If you are using the Windows operating system the mysql command is stored in the C:\Program Files\MySQL\MySQL Server 8.0\bin . So after opening the MS-DOS or PowerShell command line interface we navigate to this path with cd command like below.

> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Then we execute the mysql command the same as Linux part where the sudo is not used.

> mysql -u root -p
Connect MySQL Shell In Windows

Show/List Only Usernames of Users

The SELECT User FROM mysql.users SQL query is used to list all users from the mysql.users table by only selecting the user column which stores the username. Other related information list Host, Password,Select Privileges are not listed. 

SELECT user FROM mysql.user;

The output is very simple list of existing users listed line by line.

+---------+
 | user |
 +--------+
 | root |
 | ali     |
 | veli   |
 +--------+

Show/List All Information About Users

Alternatively all users can be liste with all user related information like host, password, select privileges, insert privileges, password_expired etc. 

SELECT * FROM mysql.user;
Show/List All Information About Users

Show/List Users with User, Host, Password and Password Expire Information

All users can be listed or displayed with their username, host, password, and password expiration information by using related columns in the specified SQL query. 

SELECT User, Host, Password, password_expired FROM mysql.user;

Show/List MySQL Users Related with a Specific Database

Another user listing method is listing users related with a specific database. Users do not have access or use privileges for all databases in MySQL. Every database may have different user access permissions. These users for a specific database can be listed like below. In the following example the users related with the database named wisetut is listed.

SELECT db, host, user FROM mysql.db WHERE db = 'wisetut';

Leave a Comment