MySQL SHOW USERS: How to List All Users in a MySQL

This article explains different methods to populate the details of the Users in MySQL. We can view the list of users by using any of the following methods.

1. Show users by MySQL workbench
2. Show users by MySQL command-line tool

Environment Setup

For demonstration, I have installed MySQL Server 8.0 on Windows 10 and created a user named NisargU. Run the below query to create a user.

mysql> create user NisargU identified by 'abcd@1234';

Output

Execute the following query to grant privileges to the NisargU user.

mysql> Grant ALL on *.* to 'NisargU';

Output

Now, let us see how to show the list of the users using the MySQL command-line tools.

How to Show MySQL Users with Command-Line Tool

Unlike SHOW TABLE, SHOW DATABASES MySQL Server does not have the SHOW USERS command. We can populate the details of the user by running queries on system tables. Let us see how we can get the data from the system tables.

Example 1. Show All Users in MySQL

We can use mysql.User table to populate the list of the users created in the MySQL Server.

Query

mysql> select host as `Host Name` ,user as `User Name` from mysql.user;

Output

Note: To execute the above query on the table, the user must be granted a DBA role.

If you want to populate more information about the users, you can add a column from mysql.user tables. To view the structure of mysql.user table, run the following query.

Query

mysql>DESC mysql.user;

Output

Example 2. Show the Details of Specific MySQL Users

Suppose we want to populate all users’ hostname, username, password expiration status, and password change date. To do that, run the following query:

mysql> select Host, User, authentication_string ,password_expired, password_last_changed from mysql.user where user='NisargU';

Output

Example 3. Show Current User

Suppose you want to populate the user connected to the MySQL Server; you can use the user() or current_user() function. First, let us connect to the MySQL Server using NisargU user.

The query to connect to MySQL:

C:\Users\Nisarg>mysql -u NisargU -p

Output

The query to view the user with USER() function:

mysql> select user() as `Connected User`;

Output

The query to view the user with the current_user() function:

mysql> select current_user() as `Connected User`;

Output

Example 4. View the List of Database and Associate Users

For example, you want to populate the list of databases and associated users with it. You can populate the information by querying mysql.db table.

Query:

mysql> SELECT host,db, user FROM mysql.db;

Output:

If you want to populate the details of the privileges assigned to the NisargU user, run the following query:

mysql> SELECT * FROM mysql.db where user='NisargU' \G;

Output

Example 5. Show Users Connected to MySQL Server

If you want to populate the list of the users connected to MySQL Server, we can run the query on the information_schema.processlist table.

Query

mysql> select ID, User, Host, DB, command,time `Query execution time`, state `Query execution status` from information_schema.processlist;

Output

As you can see, there are four users connected to the MySQL Server.

Now, let us see how we can populate the list of the users using the MySQL workbench.

How to Show Grants for User

We can use SHOW GRANTS() functions to populate the list of the privileges granted to the specific users.

Note: The query output is lengthy, so I have used MySQL workbench to view the output.

Suppose we want to populate the privileges granted to the NisargU user. To do that, run the below query.

mysql> show grants for NisargU;

Output

Let us see how we can view the list of the users with their privileges by using MySQL workbench.

Show MySQL Users using MySQL Workbench

We can view the details of the users from the MySQL workbench. Open MySQL Workbench and connect to the server using the root login.

In Navigator Pan, click the Administration tab. In the Management section, click Users and Privileges.

In the right pane, you can see the list of the users created on the MySQL Server. To view their details, select any user from the list.

Now you can view the following details of the selected user:

Login details

In the Login details tab, you can view the user’s details, including login name, authentication type, and authentication string.

Account Limits

In the Account limits tab, you can set the following parameters:

1. Total queries the account can execute within an hour.
2. Total update statement the account can execute per hour.
3. A number of times the account can connect to the server per hour.
4. The number of simultaneous connections to the server.

In our case, we have used the default settings for the NisargU user.

Administrative roles

In the Administrative roles tab, you can view the global privileges and administrative roles assigned to the user. In our case, we have granted the DBA role to the NisargU user.

Schema Privileges

In the Schema Privileges tab, you can view the list of the privileges granted on the specific database to the user. In our demo, we have granted INSERT, UPDATE, DELETE, SELECT, EXECUTE and SHOW VIEW roles to the NisargU user on the Sakiladb database.

Summary

“How to retrieve the users in MySQL?” is a very common question asked by junior database administrators. In this article, we have explored different methods to view the users created in the MySQL Server. We’ve learned how we can retrieve the list using MySQL workbench and MySQL command-line tool.

Tags: command line, show users, workbech Last modified: June 27, 2023