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 learn about mysql show users (mysql list users).
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.
📚 Looking to enhance your SQL skills? Check out our comprehensive MySQL Cheat Sheet for quick tips and tricks!
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;
View only unique user names in MySQL
Sometimes, especially when a user is assigned to multiple hosts, you may see duplicate usernames while working with the `mysql.user` table. For example, a single user can be connected using various IP addresses or domains while maintaining the same username when you run MySQL show users command. As a result, when you when you’re viewing the list of users, you may see many unnecessary clutters. To solve this problem, you can use the `SELECT DISTINCT` command and view only unique usernames without any repetition. Here is how to do this:
SELECT DISTINCT User FROM mysql.user;
Using this command, all the usernames associated with different hosts will be eliminated and you can see only unique usernames from the `mysql.user` table. In this way, you’ll have a clearer view of the users list on your server.
The result will be a list where each username appears just once, providing a cleaner view of the MySQL list users on your database server. showing unique users is especially useful when you want to see the user accounts on the server quickly and you won’t be distracted by many rows for the username.
Details of the MySQL user table fields
Do you want to know more details of your users? so, you should know that show users MySQL can provide many fields containing detailed information. All you need to do is runnig the following command and then you’ll access all the possible details about user properties on your MySQL server:
mysql> desc mysql.user;
after running this command, the output will include different options that describe users. Something like this:
Now, for more specific details, you can combine this command with other MySQL show users commands. For instance, if you want to see a list of users along with their update privileges, you can run this:
SELECT User, Update_priv FROM mysql.user;
Remember to replace Update_priv with any other fields that you need to see.
Display current user login details
MySQL includes a built-in function that returns the username and host used to access the MySQL command line. This function, called user(), and you can use it easily with this command:
SELECT user();
And the output will be like:
Do you need more detailed information? For example, do you want to see the status of currently logged-in users? if yes, you can change this query.
If you need more detailed information, such as the status of currently logged-in users, you can modify the query. This is especially important for spotting idle users who may be utilizing excessive resources. Run the following command:
SELECT user, host, command FROM information_schema.processlist;
How to check which users have locked accounts or expired passwords?
The MySQL list users table can give you important information about users. For example, is the account locked, or whether the password has expired? If you want to check which users have locked accounts or expired passwords, use the following command:
SELECT
User,
Host,
Account_locked,
password_expired
FROM
mysql.user;
Using this command, the following information will be displayed:
- User: The username of the account.
- Host: The host associated with the user account.
- Account_locked: This field shows whether the account is locked or not. The value of `Y` means the account is locked, and `N` means it is unlocked.
- password_expired: This field shows whether the user’s password has expired or not. A `Y` value means the password is expired, and `N` means it is still valid.
Using this command, you can recognize which users need to update their passwords and which ones are locked out of the system.
Some notes to consider:
- Locked Accounts: Locked accounts prevent unauthorized access by temporarily preventing user logins. This is commonly employed as a security measure.
- Expired Passwords: Users must update their passwords to continue access.
- User Management: Use ALTER USER to unlock users or reset expired passwords. MySQL offers commands like 'ALTER USER' to help manage user statuses. To unlock accounts or reset expired passwords you can use the following commands:
ALTER USER 'username'@'host' ACCOUNT UNLOCK;
Reset an expired password:
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
Checking a user's privileges in MySQL/MariaDB
Not only you can create user in MySQl, but also you can control them. To control what actions each user can do by database administrators, you can use user privileges in MySQL. Privileges manage the ability of a user to perform tasks in a database, such as accessing data, updating records, managing tables, and controlling access for other users. With efficient privilege management, you can guarantee the security of the database and offer users suitable access levels. After learning MySQL show all users commands, now it’s turn to learn how to display the privileges assigned to a specific user:
SHOW GRANTS FOR 'user_account';
For example, to see the privileges of the user `maria`, the command would be:
SHOW GRANTS FOR 'maria';
This command displays the specific permissions granted to the maria account. These rights specify the actions that the user can execute, such as executing queries, changing tables, or controlling other database functions.
Changing User Privileges
As a database administrator, you can change the privileges as you need. This includes:
Granting privileges: Using this command, you can give any user access to specific specific databases, tables, or commands. For example, you can use this command:
GRANT SELECT, INSERT ON database_name.* TO 'maria';
Running this command, the user named ‘maria’ is allowed to select and insert data into all tables within `database_name`.
Revoking privileges: If you need to remove specific access rights from a user, use this command:
REVOKE INSERT ON database_name.* FROM 'maria';
Using this command, the `INSERT` privilege from the user `maria’ will be removed.
Global, database, or table-level privileges: Privileges can be provided or removed at multiple stages, based on what you need. Global privileges apply to the entire database server, whereas database- or table-level privileges influence only certain areas of the database.
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.
I'm fascinated by the IT world and how the 1's and 0's work. While I venture into the world of Technology, I try to share what I know in the simplest way with you. Not a fan of coffee, a travel addict, and a self-accredited 'master chef'.