Written by 10:44 Database administation, Database Optimization & Structure

Different Ways to Populate the Users of MySQL

CodingSigh - Different Ways to Populate the Users of 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. MySQL workbench
  2. 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

Environment Setup

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

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

Output

grant privileges to the NisargU user

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.

Query

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

Output

View All Users in MySQL

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

view the structure of mysql.user table

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';

Output

View the Details of Specific Users

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

Output

View a Current User

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

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

Output

View the user with USER() function

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

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

Output

view the user with the current_user() function

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:

View the List of Database and Associate Users

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

populate the details of the privileges assigned to the NisargU user

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

Show Users Connected to MySQL Server

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;

Output

View the Privileges Granted to Users

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.

View Users with MySQL Workbench

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

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.

list of the users created on the MySQL Server

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.

Login details

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.

Account limits

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.

Administrative roles

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.

Schema Privileges

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.

(Visited 18 times, 2 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close