MySQL server is often the first choice for users who want to work with SQL (structured query language). It has multiple advanced features that make it more secure, reliable, and efficient. Most of the time, it is needed to manage a database on a MySQL server. In that case, check the user accounts list. More specifically, multiple commands and statements can be used for this purpose.

The outcomes of this post are:

• Method 1: How to List the Current Users in a MySQL Database?
• Method 2: How to List the Current Logged MySQL Database Users?
• Method 3: How to List the Users With Account Locked and Password Detail in a MySQL Database?

Method 1: How to List the Current Users in a MySQL Database?

To get the list of all current users in a MySQL database, provided steps should be followed.

Step 1: Launch “Command Prompt”

At first, open the “Command Prompt” terminal with the help of the Startup menu:

Step 2: Access MySQL Server

Now, access the MySQL server by utilizing the given command along with the username and user password:

Step 3: Display Databases

Next, use the “SHOW” command with the “DATABASES” option to view all existing databases:

From the below-given output, we have selected the “mysql” database:

Step 4: Change Database

Now, change the current database by running the “USE” command and specify the database name as follows:

Step 5: List Current Users

To get the information about all current users, the “user()” function can be invoked with the “SELECT” statement:

As you can see, currently, we have a “root@localhost” user:

We can also use the “current_user()” function for viewing the current username:

Method 2: How to List the Current Logged MySQL Database Users?

Sometimes, you need to view only the currently logged users in a MySQL database. For this purpose, utilize the “SELECT” command:

Here:

• “SELECT” command is utilized to select the variable’s value.
• “user” option gets all users’ names.
• “host” option denotes the hostname.
• “command” represents one user executing a query and another in the “Daemon” state.
• “FROM” clause is used for selecting some records from a table.
• “Information_schema.processlist” is the MySQL process list that represents what process is currently being performed by the set of information:

Method 3: How to List the Users With Account Locked and Password Detail in a MySQL Database?

To get the list of the users with their account and password details in a MySQL database, the following command can be used:

In the above-stated command:

• “account_locked” option is used to represent the users’ account state whether it’s locked or not.
• “password_expired” option denotes whether the user’s account password has expired or not.

As you can see in the below-provided output, all information has been displayed:

We have compiled several ways to get the list of users in a MySQL database.

Conclusion

To get the users in a MySQL database, there are multiple ways according to the users, such as if the user wants to just get the current users list, then the “SHOW user()” or “SHOW current_user()” statements can be used. On the other hand, if it is required to get the information along with the user, hostname, command, account, and password, the “SELECT” statement can be utilized. This post described multiple ways to get the list of users in a MySQL database.