🔍 How to Display Users in MySQL and View User Information Using Linux

A MySQL database server is often the first choice for developers and anyone interested in experimenting with structured query languages. MySQL offers numerous features that make it reliable, secure, and efficient.

However, there are ways to further enhance the security of the MySQL server. One such way is by creating database users with restricted access.

This tutorial will explain why you should create separate user accounts in MySQL and how to use the MySQL SHOW USERS command on your Linux VPS.

🔐 Why Create Users on the MySQL Server?

When database users or administrators install MySQL, the first user created is the root user, which is the MySQL administrator. The root user has permissions to do everything in the MySQL database.

While having all permissions might seem advantageous, it comes with its own security vulnerabilities. Sharing the root user among multiple people is risky. Hackers often attempt to log in as the root user, steal hosted information, or even destroy the entire MySQL server along with its data.

Therefore, system administrators create users with specific permissions on certain databases. This way, if an account's credentials are compromised, the impact will be minimal and manageable.

🖥️ How to Display Users in MySQL Database on Linux

Unlike the SHOW DATABASES or SHOW TABLES commands that instantly display all databases or tables, the SHOW USERS command doesn’t exist in MySQL.

Even though such a command doesn't exist, users can use a MySQL query to get a complete list of users on a specific MySQL database server.

Follow the steps below to learn more.

Step 1: Log in as the MySQL Root User

Start by logging into your VPS via SSH as the root user. Once connected, enter the MySQL command line with the following:

"sudo mysql -u root -p"

Then, enter your MySQL root password.

Important! Keep in mind that the system's root password and MySQL root password are different and may vary.

Once you're in the MySQL console as the root user, you can execute queries to display other MySQL users.

Step 2: Use the MySQL SHOW USERS Query

Use the following query to display the MySQL users created on the database server:

"SELECT user FROM mysql.user;"

As a result, you will see a list of all the users that have been created in MySQL.

Common Issue: You might notice duplicate users. This occurs because MySQL filters access to a server based on the IP address it comes from.

To get even more information, you can add a host column using the following command:

"SELECT user, host FROM mysql.user;"

This will allow you to see MySQL users and which host or IP address they have access from. In most cases, all users will be from a local database.

Step 3: View Additional MySQL User Information (Optional)

If you need more information about MySQL users, the query can be expanded using MySQL queries.

For example, the following command will print all possible information from the user table:

"SELECT * FROM mysql.user;"

While this output might seem too cluttered to understand, it's helpful if you need detailed information about users.

Common Mistake: The output might be overwhelming, so it's recommended to narrow the search using more specific queries.

Additional Use Cases:

• Preview Table Columns: The following query will display a preview of the columns in the user table. It’s particularly useful if users want to verify information about a specific table.

"DESC mysql.user;"

• Show Only Unique Usernames: To skip repeated usernames, use the following query:

"SELECT DISTINCT user FROM mysql.user;"

• Password Expiration and Account Lock Status: To check the password expiration status and account lock status, use this query:

"SELECT user, account_locked, password_expired FROM mysql.user;"

• Show Current User and Connected Users: The current user can be displayed with the following query:

"SELECT current_user();"

For more detailed information, modify the query to show currently connected users along with their states. This command is beneficial for finding inactive users consuming too many resources:

"SELECT user, host, command FROM information_schema.processlist;"

Conclusion

Managing a database server can be a challenging task. Therefore, database administrators need to be careful when creating and managing user permissions.

The MySQL SHOW USERS command allows administrators to view MySQL users along with other important information.

To summarize, we've covered the basics of how to display MySQL users linked to a database and learned how to:

• List all users created in a given MySQL database.

• Preview columns of the MySQL database table.

• Show only unique usernames in a table.

• Verify password expiration and account lock status.

• Display current users and currently connected users in a MySQL database.

We hope you find this tutorial helpful. If you have any additional questions or comments, feel free to let us know in the comments section below.

Best Regards,

João @PixelHost.