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.
- MySQL workbench
- MySQL command-line tool.
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';
Execute the following query to grant privileges to the NisargU user.
mysql> Grant ALL on *.* to 'NisargU';
Now, let us see how we can populate the list of the users using the MySQL command-line tools.
How to View Users with MySQL 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. View All Users in MySQL
We can use mysql.User table to populate the list of the users created in the MySQL Server.
mysql> select host as `Host Name` ,user as `User Name` from mysql.user;
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.
Example 2. View the Details of Specific 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';
Example 3. View a 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
The query to view the user with USER() function:
mysql> select user() as `Connected User`;
The query to view the user with the current_user() function:
mysql> select current_user() as `Connected User`;
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.
mysql> SELECT host,db, user FROM mysql.db;
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;
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.
mysql> select ID, User, Host, DB, command,time `Query execution time`, state `Query execution status` from information_schema.processlist;
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 View the Privileges Granted to Users
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;
Let us see how we can view the list of the users with their privileges by using MySQL workbench.
How to View Users with 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:
In the Login details tab, you can view the user’s details, including login name, authentication type, and authentication string.
In the Account limits tab, you can set the following parameters:
- Total queries the account can execute within an hour.
- Total update statement the account can execute per hour.
- A number of times the account can connect to the server per hour.
- The number of simultaneous connections to the server.
In our case, we have used the default settings for the NisargU user.
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.
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.
“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.