How to Show Users in MySQL and View User Information Using Linux

A MySQL database server is often the first choice for developers and anyone interested in trying a structured query language. MySQL includes numerous features that make it reliable, secure and efficient.

However, there are ways to further improve the security of the MySQL server. One of them is 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

How to Show Users in the MySQL Database on Linux

1. Log In as MySQL Root User

2. Use the MySQL SHOW USERS Query

3. View More MySQL User Information (Optional)

FAQ About Showing Users in MySQL

How Can I See All MySQL Users and Passwords?

How Do I Show Users in MySQL MariaDB?

Where Are Users Stored in MySQL?

Why Create Users on the MySQL Server

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

Although having all permissions may seem beneficial, doing so has its own security vulnerabilities, and sharing the root user among several people is dangerous. Hackers often try to log in as the root user and steal the hosted information or even destroy the entire MySQL server along with its data.

Therefore, system administrators create users with specific permissions on some databases. In other words, if an account's credentials are compromised, the impact will be minimal and manageable.

How to Show Users in the MySQL Database on Linux

Unlike the SHOW DATABASES or SHOW TABLES commands that display all databases or tables immediately, the SHOW USERS command does not exist in MySQL.

Although there is no such command, users can use a MySQL query and get a complete list of users on a specific MySQL database server.

Follow the steps below to learn more.

1. Log In as MySQL Root User

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

```bash

sudo mysql -u root -p

```

Then, enter your MySQL root password.

Important! Note that the system root and MySQL root passwords are different and may not be the same.

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

2. Use the MySQL SHOW USERS Query

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

```sql

SELECT user FROM mysql.user;

```

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

Note that there may be duplicate users. This is because MySQL filters access to a server based on the IP address it comes from.

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

```sql

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 users are from a local database:

MySQL query showcasing all users along with hosts.

3. View More MySQL User Information (Optional)

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

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

```sql

SELECT * FROM mysql.user;

```

The MySQL query shows all possible information from a MySQL database table

However, that output may look too cluttered to understand. Therefore, we recommend narrowing the search using more specific queries.

Here are some of the more popular use cases:

Table Column Preview

The following query will show a preview of the user table columns. It is especially useful if users want to verify information about a specific table.

```sql

DESC mysql.user;

```

MySQL query to get a preview of the user table columns.

Show Only Unique Usernames

To omit repeated usernames, the following query can be used:

```sql

SELECT DISTINCT user FROM mysql.user;

```

MySQL query to show only unique usernames in a given MySQL database.

Password Expiration and Account Lock Status

To check the password expiration status and account lock status, use this query:

```sql

SELECT user, account_locked, password_expired FROM mysql.user;

```

MySQL query to show the password expiration status and account lock status.

Show Current Users and Currently Connected Users

The current user can be displayed with the following query:

```sql

SELECT current_user();

```

MySQL query to show the current user.

If you need more information, you can modify the query to show the currently connected users with their statuses. This command is beneficial for finding inactive users that consume too many resources.

```sql

SELECT user, host, command FROM information_schema.processlist;

```

MySQL query showing currently connected users along with their statuses.

Conclusion

Managing a database server can be a challenging job. Therefore, database administrators must be careful when creating and managing user permissions.

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

To summarize, we have covered the basics of how to show MySQL users linked to a database and learned how to:

- List all users created in a given MySQL database.

- Get a preview of MySQL database table columns.

- Show only unique usernames in a table.

- Check the password expiration status and account lock status.

- Show current users and currently connected users in a MySQL database.

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