Show Users - (MySQL User Management)

MySQL is a popular open-source relational database management system. One of its important features is user management. In this tutorial, we will discuss how to view the users in a MySQL database using various commands.

Syntax

There are different commands to show the users of a MySQL database:

• SELECT User FROM mysql.user;
• SELECT user,host FROM mysql.user;
• SELECT host,user,password FROM mysql.user;
• SELECT * FROM mysql.user;

Example

Suppose we have a MySQL database with the name "sampledb". Here's how we can view the users of this database using the above commands:

• Show only the usernames of all users:

SELECT User FROM mysql.user;

• Show the usernames and hostnames of all users:

SELECT user, host FROM mysql.user;

• Show the usernames, hostnames, and passwords of all users:

SELECT host,user,password FROM mysql.user;

• Show all the details of all users:

SELECT * FROM mysql.user;

Output

The above commands will return the following output respectively:

• Show only the usernames of all users:

+------------------+
| User             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
+------------------+

• Show the usernames and hostnames of all users:

+------------------+---------------------------+
| user             | host                      |
+------------------+---------------------------+
| root             | localhost                 |
| mysql.infoschema | localhost                 |
| mysql.session    | localhost                 |
| mysql.sys        | localhost                 |
| debian-sys-maint | localhost                 |
+------------------+---------------------------+

• Show the usernames, hostnames, and passwords of all users:

+-----------+---------------------------+-------------------------------------------+
| host      | user                      | password                                  |
+-----------+---------------------------+-------------------------------------------+
| localhost | root                      | *3636DABAF23F62D00B4BAE058EADF67F88B3839B |
| localhost | mysql.infoschema          | *D608311C79B58834CE56309EE9F5D069B9B831A6 |
| localhost | mysql.session             | *BC3DB831A0B7A19C775CC54A2896D25B53C22306 |
| localhost | mysql.sys                 | *0E3C50F65E10BDCE3BE3E36E80E342F4698B97AA |
| localhost | debian-sys-maint          | *589AF6BBD4B03F13                   |
+-----------+----------------------------+-------------------------------------------+

• Show all the details of all users:

+-----------+---------------------------+-------------------------------------------+--------+-------------+
| host      | user                      | password                                  | plugin | authentication_string |
+-----------+---------------------------+-------------------------------------------+--------+-------------+
| localhost | root                      | *3636DABAF23F62D00B4BAE058EADF67F88B3839B |        | NULL        |
| localhost | mysql.infoschema          | *D608311C79B58834CE56309EE9F5D069B9B831A6 |        | NULL        |
| localhost | mysql.session             | *BC3DB831A0B7A19C775CC54A2896D25B53C22306 |        | NULL        |
| localhost | mysql.sys                 | *0E3C50F65E10BDCE3BE3E36E80E342F4698B97AA |        | NULL        |
| localhost | debian-sys-maint          | *589AF6BBD4B03F13                   |        | NULL        |
+-----------+----------------------------+-------------------------------------------+--------+-------------+

Explanation

In the above example, we used various SELECT queries to show the users of a MySQL database. The first query, SELECT User FROM mysql.user;, returns only the usernames of all the users in the database. The second query, SELECT user,host FROM mysql.user;, returns both usernames and hostnames of all users. The third query, SELECT host,user,password FROM mysql.user;, returns the usernames, hostnames, and passwords of all users. The fourth query, SELECT * FROM mysql.user;, returns all the details of all users.

Use

Showing the users of a MySQL database is useful for understanding the security of your database and ensuring that the right users have appropriate permissions to execute different queries in the database.

Important Points

• To show the users of a MySQL database, you can use various SELECT commands.
• You can show only the usernames, usernames and hostnames, usernames, hostnames and passwords, or all details of all the users.
• Understanding the users in your MySQL database is important for ensuring the security of your database and the right permissions are granted to the right users.

Summary

In this tutorial, we discussed how to show the users of a MySQL database using various queries. As you can see, there are different queries that can be used to show different aspects of the users in the database. Knowing how to view your database users is important for maintaining database security and ensuring that the right users have appropriate permissions in the database.