Viewing a list of users allows you to see who has access to the MySQL server and what privileges they have. This can help you ensure that only authorized users have access and that they have the appropriate level of access for their role.
This will also make it easier to manage user accounts, such as adding, modifying, or removing unnecessary user accounts. This guide will teach our audience how to show/list users in MySQL and will cover the following.
Let’s explain the above in detail in the following sections.
Login as root
In a MySQL database, the root user has the ultimate power to do anything, and Listing Users is one of them. To log in as root in MySQL, the following command is used.
$ sudo mysql -u root -p
If you haven’t set any password for the root user, leave the password BLANK and press the enter key because it is set as default.
Showing All Users of MySQL
To view the users, there’s a query that you can use on the “mysql.user” table of MySQL database, which is auto-created; use this to do that.
> SELECT USER FROM mysql.user;
Using the above command or query, we’ve displayed all the users on the MySQL database, and there are 40 other columns you can use to get information; use this command to see what you want to view.
> desc mysql.user;
The above image shows the options that can be used to get all the required information, and here’s how you can use the options in the above image (we’d view User, Host, and Password expired).
> SELECT User, Host, authentication_string FROM mysql.user;
In the above command, we’ve used three options to be displayed; one is the user, the other is the host, and the third one is to check whether the password is expired or not.
Viewing Only the Unique Usernames
At some points, creating multiple accounts with the same name could be confusing, so here’s a command that you can use in MySQL Shell to display only the Unique UserNames.
> SELECT DISTINCT user FROM mysql.user;
As seen in the above image, there’s no name repeated, and only the first instance is displayed.
Viewing the Database Details with Respect to Their Associated Users
MySQL has a wide range of options that can be used in the queries. Viewing the database details along with the user and host name is also one of them; it is done using this command in MySQL Shell.
> SELECT DB, HOST, USER FROM mysql.db;
View Currently Logged-in User
While working with multiple user accounts on MySQL, you may need to view which account you are using and view the currently logged-in users; the following command is used in MySQL Shell.
> SELECT USER();
As expected, the root user is logged-in because all the management is done by a super-user account, which is, in our case, the root.
Conclusion
In MySQL, viewing a list of users allows you to manage and check the privileges of individuals who have access to the server. This can help you control who is authorized to use the server and what they can do within it. This article has provided a detailed guideline for viewing a list of users in MySQL.
