How to SHOW USERS in MySQL and User Information Using Linux

How to SHOW USERS in MySQL and User Information Using Linux

A MySQL database server is often the go-to choice for developers and anyone willing to test a structured query language. MySQL has many features that make it reliable, secure, and efficient.

However, there are some ways to increase MySQL server security even more. One of them is to create users with limited permissions on the database.

In this tutorial, we’ll provide more context for why you should create separate MySQL user accounts and go over how to use the MySQL SHOW USERS command on your Linux server.

Download Complete Linux Commands Cheat Sheet

Why Create Users in MySQL Server?

Whenever users or database administrators install MySQL, the first user that will be created is the root user – the MySQL administrator. The root user will have permissions to do everything on the MySQL database.

While having all the permissions can seem beneficial, doing so comes with its own security flaws, and sharing the root user between several people is dangerous. Hackers often try to log in as the root user and steal the hosted information or even destroy the whole MySQL server alongside its data.

Thus, system administrators create users with specific permissions on some databases. In other words, if credentials for one account get compromised, the impact will be minimal and manageable.

How to SHOW USERS in MySQL Database on Linux

Unlike the SHOW DATABASES or SHOW TABLES commands that display all databases or tables right away, the SHOW USERS command does not exist in MySQL. Even though there is no such command, users can use a MySQL query and get a full list of users in a given MySQL database server.

Follow the steps below for more information.

1. Log in as the MySQL Root User

Start by logging in to the VPS via SSH as the root user. Once that’s done, enter the MySQL command line with this command:

sudo mysql -u root -p

Then, enter your MySQL root password.

Important! Keep in mind that system and MySQL root passwords are separate and can differ.

Once you are in the MySQL console as the root user, you will be able to run queries to display other MySQL users.

2. Use the MySQL SHOW USERS Query

Use the following query to show MySQL users created in the database server:

SELECT user FROM mysql.user;

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

MySQL query result displaying all the users created in a MySQL database.

Take note that there might be duplicate users. This is because MySQL filters access to a server according to the IP address it comes from.

You can also add a host column to see even more information using the following command:

SELECT user,host FROM mysql.user;

With this, you will be able to see the MySQL users and which host or IP address they have permission to access. In our case, all of the users are from a local database:

MySQL query showcasing all users along with hosts.

3. See More MySQL User Info (Optional)

If you need more information about MySQL users, the search command can be expanded with the help of MySQL queries.

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

SELECT * FROM mysql.user;
MySQL query showing all possible information from a MySQL database table

However, such output might look too messy to figure out. Thus, we recommend narrowing the search by using more specific queries.

Here are some of the more popular use-cases:

Preview Table Columns

The following query will display a preview of user table columns. It is especially helpful if users want to check information about a specific table.

DESK mysql.user
MySQL query to preview user table columns.

Display Only Unique Usernames

To skip repeating usernames, the following query can be used:

SELECT DISTINCT user FROM mysql.user;
MySQL query to display only unique usernames in a given MySQL database.

Password Expiration and Account Locking Status

In order to check the password expiration status and account locking state, use this query:

SELECT user, account_locked, password_expired FROM mysql.user;
MySQL query to show password expiration and account locking status.

Show Current and Current Logged Users

The current user can be displayed with the following query:

SELECT current_user();
MySQL query to display the current user.

If you need more information, you can modify the query to display currently logged-in users with their states. This command is beneficial for finding idle users that use up too many resources.

SELECT user,host, command FROM information_schema.processlist;
MySQL query showcasing currently logged-in users along with their states.

Conclusion

The administration of a database server can be challenging work. Thus, database administrators must be careful when creating and administering user permissions.

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

To recap, we’ve covered the basics of how to show MySQL users tied to a database and learned how to:

  • List all users created in a given MySQL database
  • Preview MySQL database table columns
  • Display only the unique usernames in a table
  • Check password expiration and account locking status
  • Show current and currently logged-in users in a MySQL database

We hope that you found this tutorial helpful. If you have any additional questions or insights, let us know in the comments section below.

Was this tutorial helpful?

Author
The author

Edward S.

Edward is a Content Editor with years of experience in IT as a writer, marketer, and Linux enthusiast. Edward's goal is to encourage readers to establish an impactful online presence. He also really loves dogs, guitars, and everything related to space.