Javatpoint Logo
Javatpoint Logo

MySQL Show Users/List All Users

Sometimes you want to manage a database in MySQL. In that case, we need to see the list of all user's accounts in a database. Most times, we assume that there is a SHOW USERS command similar to SHOW DATABASES, SHOW TABLES, etc. for displaying the list of all users available in the database server. Unfortunately, MySQL database does not have a SHOW USERS command to display the list of all users in the MySQL server. We can use the following query to see the list of all user in the database server:

After the successful execution of the above statement, we will get the user data from the user table of the MySQL database server.

Let us see how we can use this query. First, we have to open the MySQL server by using the mysql client tool and log in as an administrator into the server database. Execute the following query:

We will get the following output where we can see the five users in our local database:

MySQL Show Users

If we want to see more information on the user table, execute the command below:

It will give the following output that lists all the available columns of the mysql.user database:

MySQL Show Users

To get the selected information like as hostname, password expiration status, and account locking, execute the query as below:

After the successful execution, it will give the following output:

MySQL Show Users

Show Current User

We can get information of the current user by using the user() or current_user() function, as shown below:

After executing the above command, we will get the following output:

MySQL Show Users

Show Current Logged User

We can see the currently logged user in the database server by using the following query in the MySQL server:

The above command gives the output, as shown below:

MySQL Show Users

In this output, we can see that there are currently four users logged in the database, where one is executing a Query, and others show in Sleep or Daemon status.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA