How to show users in MySQL?

List of content you will read in this article:

The main purpose of a database is to store data in an organized manner for convenient retrieval. At times, you may need to handle a MySQL database. 

In such a scenario, accessing the inventory of all user accounts within the database becomes necessary. Often, we presume that a command named SHOW USERS, akin to SHOW DATABASES, SHOW TABLES, etc., can be employed to list users in the MySQL database server. 

However, regrettably, the MySQL database does not possess any SHOW USERS command that can be utilized to show the users in a MySQL database.

Follow the steps in this guide to understand how to display a complete list of all user profiles on a MySQL server.

What is the purpose of creating users in MySQL Server? 

Upon installing MySQL, the initial user to be established is the root user, who serves as the administrator. The root user is granted complete access to the MySQL database. 

Despite the advantages of having complete access, this approach exposes security vulnerabilities, and sharing the root user among multiple users is hazardous. 

Hackers frequently attempt to access the system as the root user to swipe or obliterate the entire MySQL server and its data.

Hence, administrators establish users with particular authorizations on certain databases. In simpler terms, if an account's login information is breached, the consequences will be insignificant and controllable.

How to show the users in a MySQL database?

While the SHOW DATABASES or SHOW TABLES commands immediately exhibit all databases or tables, the SHOW USERS command is unavailable in MySQL. 

Despite the absence of this command, users can execute a MySQL query and retrieve a complete catalogue of users in the specified MySQL database server.

• First, log in as the Root User.

Begin by accessing the VPS through SSH as the root user. After successfully logging in, execute the following command to enter the MySQL command line: 

sudo mysql -u root -p

Next, provide the MySQL root password. 

Upon acquiring access to the MySQL console as the root user, you can execute queries to showcase additional MySQL users.

• Use MySQL SHOW USERS Query

Execute the below command to show the users in a MySQL database:

SELECT user FROM mysql.user;

Upon execution, a complete record of every user created in MySQL will be shown. Please be aware that there could be replicated users. This is because MySQL restricts entry to a server based on its source IP address. 

Moreover, you can incorporate a host column to retrieve additional details by executing the subsequent command: 

SELECT user,host FROM mysql.user;

You can observe the MySQL users and their authorized host or IP address through this. 

• View additional MySQL user details (optional) 

If you require additional details regarding MySQL users, the search function can be enhanced by utilizing MySQL queries. To illustrate, executing the subsequent command will display all feasible data from the user table:

SELECT * FROM mysql.user;

However, the output you get might look too complicated to understand. Therefore, we recommend narrowing down the search via more specific queries.

🚀🚀Experience unparalleled performance, flexibility, and control with our high-powered VPS hosting, empowering your online presence like never before.🚀🚀

List users in MySQL via PHPMyAdmin

To make a list of MySQL users via PHPMyadmin, you must deploy CyberPanel. CyberPanel will install PHPMyAdmin automatically, a graphical user interface for administering MySQL. 

After installing CyberPanel, choose PHPMyAdmin from the left-side menu, which will automatically log you in. Choose SQL from the top menu, bringing up the query box. Then, paste in the query and click Go:

desc mysql.user;

You can also execute a query to show the users in a MySQL database in the same way:

select user,host from mysql.user;

In addition, in case you want to see the current user, you can use the following query:

SELECT current_user();

You can modify the query to display currently logged-in users plus their states for more information. This command is used for finding idle users that waste useful resources.

SELECT user,host, command FROM information_schema.processlist;

Conclusion

MySQL database server is frequently preferred for developers and individuals who want to evaluate a structured query language. MySQL has numerous attributes that ensure its dependability, safety, and effectiveness. 

Using the above commands, you can effortlessly organize user details from the mysql.user repository. Rather than list users in MySQL, you can alter search results and uncover user-centric specifics. 

An elaborate and adaptable summary empowers you to make an informed determination concerning safeguarding your MySQL server.

People also read: 

• MySQL Cheat Sheet
• What Is Default MySQL Port Number?
• SQL vs MySQL
• How to Reset MySQL Root Password