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 working with Structured Query Language (SQL). MySQL includes numerous features that make it reliable, secure, and efficient.

However, there are ways to further enhance the security of your MySQL server — one of them 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 a MySQL Server

When users or database administrators install MySQL, the first user created is the root user — the MySQL administrator. The root user has full permissions to perform any action in the MySQL database.

While having all permissions might seem convenient, it poses security risks, especially if multiple people share the root user credentials. Hackers frequently target the root account to gain access to stored information or even destroy the entire MySQL server and its data.

Therefore, system administrators create users with specific permissions for certain databases. In other words, if one account is compromised, the overall impact is minimal and manageable.

How to Show Users in the MySQL Database on Linux

Unlike the SHOW DATABASES or SHOW TABLES commands that immediately display all databases or tables, there is no direct SHOW USERS command in MySQL.

Although such a command doesn’t exist, users can use a MySQL query to get a complete list of users on a given MySQL database server.

Follow the steps below to learn how.

1. Log In as the MySQL Root User

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

sudo mysql -u root -p

Then enter your MySQL root password.

Important: Note that your system root and MySQL root passwords are separate and may differ.

Once logged into the MySQL console as the root user, you can run queries to display other MySQL users.

2. Use the MySQL SHOW USERS Query

Run the following query to display all MySQL users created on the server:

SELECT user FROM mysql.user;

This will list all users that have been created in MySQL.

Be aware that duplicate usernames may appear, as MySQL filters access to a server based on the originating IP address.

You can add the host column to see additional information using:

SELECT user, host FROM mysql.user;

This will show MySQL users along with the host or IP address they’re allowed to connect from. In most cases, users belong to a local database.

3. View More MySQL User Information (Optional)

If you need more details about MySQL users, you can expand your query with additional MySQL statements.

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

SELECT * FROM mysql.user;

This output may look cluttered, so it’s better to narrow down your search with specific queries.

Here are a few useful examples:

Preview User Table Columns

DESC mysql.user;

Show Only Unique Usernames

SELECT DISTINCT user FROM mysql.user;

Check Password Expiration and Account Lock Status

SELECT user, account_locked, password_expired FROM mysql.user;

Show Current and Active Users

To display the current logged-in user:

SELECT current_user();

To view all currently connected users and their statuses:

SELECT user, host, command FROM information_schema.processlist;

This helps identify inactive users consuming too many resources.

Conclusion

We hope this guide has been helpful! 🚀 Remember, don’t hesitate to ask questions on the HolyHosting Discord or contact our support team.

Follow us on Twitter @HolyHosting to stay up to date.

Stuffy @ HolyHosting