How to List All Users in a MySQL Database

Introduction

MySQL databases are multi-user environments. Knowing how to list all database users is essential for efficient database management and database security.

Various MySQL queries provide information about database users, and each command offers different insights.

This guide will show multiple ways to list all users in a MySQL database.

Prerequisites

• Access to the command line/terminal window.
• MySQL installed (we're using MySQL 8).
• Access to a user with sudo or root privileges.

Access MySQL Database

To access the MySQL server as a root user, enter the following in the terminal:

sudo mysql -u root -p

Enter the sudo and root user's password when prompted. The session switches to the MySQL shell.

MySQL Show Users Command

The following query lists usernames that have access to the database server:

SELECT user FROM mysql.user;

The system retrieves the information from the user column in the mysql.user database.

Add columns for a more detailed overview of MySQL users and their privileges.

How to List mysql.user Database Fields

Before expanding the search, list all available fields in the mysql.user database. Use the desc command to describe the table:

desc mysql.user;

The Field column contains data that can be queried in the mysql.user database. Combine several options in a single command to get detailed user information.

How to Show MySQL User Information

The following query provides a table with data specific to each user:

SELECT User, Host, authentication_string FROM mysql.user;

The query adds two more columns to the table. Host contains the host from which the user can connect and authentication_string is the user's password hash.

How to List Only Unique MySQL Users

Some queries duplicate the usernames in the User column. Remove duplicates with:

SELECT DISTINCT User FROM mysql.user;

The output removes duplicate rows and shows each username once.

Show Current MySQL User

Two MySQL functions help show the current user: current_user() and user(). Use the current_user() functions to get the details of the current MySQL user:

SELECT current_user();

The command shows the user account that's in use and authenticated. To show user information that was provided when establishing a connection, use the user() function instead:

SELECT user();

The output shows the logged-in MySQL user.

The two functions show the same account in most cases. The second may show a different output if there were authentication changes after establishing a connection.

Show Logged In MySQL Users

Knowing who is logged in when monitoring a MySQL server is crucial. Query the information_schema.processlist table to show all currently running processes and connected users:

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

The output shows the connected users, databases, and command type.

Conclusion

This guide showed various ways to query MySQL users. Numerous methods are available to list users, and each can provide specific search results.

Next, see how to delete a MySQL user or create a new user and grant privileges.