MySQL SHOW USERS: How to List All Users in a Database

Listing MySQL users might seem like a routine task, but it’s one with real security stakes. Whether you're auditing access, checking privileges, or onboarding new users, even a small misstep can expose your database to risk.

This guide walks you through how to list MySQL users using SQL queries and GUI tools like Workbench and phpMyAdmin. You'll also learn how to check user privileges, manage accounts, and avoid common security pitfalls, like using wildcard hosts or root for applications.

And because user access is a critical part of your security posture, we show you how to simplify MySQL access management with centralized controls, session logging, and just-in-time access, so you always know who’s connecting, what they can do, and when their access ends.

How MySQL Stores User Information

User information for MySQL databases usually exists in the mysql.user table. This information includes the username, host, authentication string (password), user’s privileges, and authentication plugins.

You can inspect the mysql.user table to see this data using the command below:

DESC mysql.user;

It’s short for the DESCRIBE mysql.user; command. The output for this command will usually include columns such as field, type, null, key, default, and extra. 

Note: The DESC mysql.user; command is also useful for understanding the schema of the mysql.user table before querying more fields.

How to List All Users in MySQL

Before we look at the various methods, it’s worth noting that there are several prerequisites to list users in a MySQL database. For one, you must have access to a terminal window or the command line. Secondly, your user account should have root or sudo privileges.

Using SQL Query

This is the most commonly used method because it directly queries the mysql.user table. You can use this basic syntax:

SELECT user FROM mysql.user;

You should see a list similar to the one below:

+------------------+    
 | user  |               
 +------------------+    
 | debian-test-user |    
 | claire  |             
 | sean  |               
 | root  |               
 | joe  |                
 | ray  |                
 +--------------------+ 

6 rows in set (0.03 sec)

Detailed User List 

If you want a more detailed view of the users, such as their hosts and passwords, you can edit the basic syntax:

SELECT User, Host, authentication_string FROM mysql.user;

Note: In older versions than MySQL 8.0+, the authentication_string column in the command was noted as Password.

The above syntax would display a list of users similar to this:

+-----------+------------+--------------------------+ 
 | User  | Host  | Password  | 
 +-----------+------------+--------------------------+ 
 | debian-test-user | localhost |  *QGRO60573BH343U9OJOFNQUUUIBFBQEY13009G | 
 | claire  | %  | *PUHUD JGCYXG6T7AW3EE;KPMMAWRE9J8W4YUNVG | 
 | sean  | 127.0.0.1 | *2BkdnVZESR8ITGQE AFDMIOJRJY08WUTSHNMSFEN | 
 | root  | localhost | *MXCOVJPZJPSJINSRHX78HSRNISFHYRW38743UIHA | 
 | joe  | %  | *9CKNAGZBJBZJWTE754437UGJADMBBSGEFKGKYBE | 
 | ray  | localhost | *SJLHKA3HI8Y845YHQYRBNMXFK09ET65EKIYT774 | 
 +-----------+-----------+---------------------------+

6 rows in set (0.20 sec)

Filtering Users 

If you want to return specific results with the SELECT command, you can use the WHERE option. 

Take, for instance, this command:

SELECT User FROM mysql.user WHERE Host = 'localhost';

This tells the system to list MySQL users where the host column equals 'localhost'

So, from the table above, the list would only contain debian-test-user, root, and ray, as shown below:

+-------------------+-----------+ 
 | User  | Host  | 
 +-------------------+-----------+ 
 | debian-test-user | localhost |  
 | root  | localhost | 
 | ray  | localhost |  
 +------------------+-----------+

3 rows in set (0.01 sec)

You can also filter results using pattern matching with the LIKE option. This tells the system to display users with a specific pattern. The LIKE option uses wildcard characters such as:

• %: This stands for zero or more characters 
• _: This stands for just one character 

For instance, the command SELECT User FROM mysql.user WHERE User LIKE 'c%'; will list all the users whose names start with the letter c. 

Another example:

SELECT User FROM mysql.user WHERE User LIKE '____'; will list users whose names have only four dd. 

GUI Alternatives: Listing Users via Tools

Dealing with manual queries can be slow and time-consuming, especially if you just started working with MySQL. If that’s the case, you can use Graphical User Interfaces (GUI) MySQL SHOW USERS alternatives. GUIs provide an easy way to list users by letting you perform actions like point, click, drop, and select within the database.

MySQL Workbench

1. If you have the MySQL Workbench application downloaded and installed on your machine, launch it. 
2. Connect to the MySQL server you want to view users on.
3. Choose the Users and Privileges option, and a new tab will list all users and their hosts. 
4. Clicking on any user will present a more detailed view, such as their account limits, administrative roles, and schema privileges. 

phpMyAdmin

This method requires that you install phpMyAdmin. [A1] Once installed, 

1. From the left-side menu of your cPanel, select PHPMyAdmin, and you’ll be automatically logged in.
2. Select SQL from the dashboard’s top menu.
3. A box for you to type in your query will open. 
4. Type desc mysql.user; and click Go. 
5. You’ll see a list of all the users displayed with their usernames, host, global privileges, and SSL-related settings. 

dbForge Studio for MySQL

This is another visual interface you can leverage to manage users and view permissions. It’s useful for teams and enterprise environments. 

1. Launch the dbForge Studio for MySQL application.
2. If this is your first time using dbForge Studio for MySQL, you must create a server connection. Here’s how to do it.
3. After establishing a connection, click on the Database option on the top bar menu, then Security Manager from the drop-down menu that appears.
4. A new tab will open, and on the left pane, select Users to list all user accounts. 

How to Check MySQL User Privileges

After listing users on your MySQL database, you may also need to check the privileges and permissions they possess for security purposes. 

To see the user privileges for just one user, type this:

SHOW GRANTS FOR 'username'@'host';

You can use this command for auditing purposes or to replicate access levels.

To see the privileges for all users on the database, type this:

SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user;

NOTE: This will generate a batch of grant statements for each user on your database. You can then run each of the SHOW GRANTS statements to view the privileges each user has. 

Managing MySQL Users

There are also several important actions you should understand alongside listing MySQL users. 

For instance, if you want to create or add a new user in your database, you can use this command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

You can also remove a user easily:

DROP USER 'username'@'host';

If you need to change the password and host of a user as part of routine security management, you can run this command:

ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';

For security purposes, you may need to lock a user account. To do so, use the following command:

ALTER USER 'username'@'host' ACCOUNT LOCK;

Should you need to reactivate it, the following command will do so:

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

Security Best Practices for MySQL User Management

When it comes to database user management, security should be your number one priority. It’s vital to adopt measures that block malicious actors and reduce vulnerabilities:

• Use specific hosts: Using the wildcard as a host allows users to connect from any client, which is a security risk. It’s best to specify the exact host the user should connect from to reduce the attack surface.
•  Avoid root for applications: The root user is a prime target for hackers because it can be used to take over the database. It’s best to avoid using this user and instead assign users the least privileges required to perform their roles. 
• Regularly audit users: You should occasionally check the actions that user accounts perform and the privileges assigned to them. This is vital to ensure that only authorized users can perform certain actions. Additionally, you should check for stale accounts and deactivate them, as threat actors can use them to take over your database.  
• Enable activity logging: One of the best ways you can utilize logging is for failed logins. Multiple failed logins can help detect and thwart potential brute force attempts before they become successful.
• Observe password security: The passwords for users in your MySQL database must be strong and unique so hackers can’t easily guess them. To do this, you can use plugin-based authentication like caching_sha2_password to strengthen security. Luckily, caching_sha2_password is the default and recommended plugin starting with MySQL 8.0.4. Additionally, regularly rotating passwords and monitoring for expired passwords can help. For instance, you can force users to change their expired passwords by running: ALTER USER 'username'@'host' PASSWORD EXPIRE.

StrongDM’s Approach to MySQL User Management and Security

Listing MySQL users isn’t just an admin task—it’s a security checkpoint. Every user, host, and privilege entry represents a potential access point. Missteps like wildcard hosts, overprivileged accounts, or stale users can expose your database to serious risk.

StrongDM removes that risk by giving you centralized visibility, tight access control, and full audit trails across every database connection—no manual queries or guesswork needed.

Here’s how StrongDM simplifies MySQL user security

Centralized Access, Not Local MySQL Users

• Eliminate scattered local MySQL users across environments.
• StrongDM brokers access through your SSO or IAM, tying every session to an identity you trust.
• No more wildcard hosts, shared passwords, or unmanaged accounts.

Just-in-Time Access & Least Privilege

• Grant temporary access to MySQL based on role, request, or workflow triggers.
• Enforce least privilege automatically, down to specific tables or commands.
• Revoke access instantly when a role changes or a user offboards.

Full Visibility & Session Logs

• See who connected to your MySQL database, when, from where, and what queries they ran.
• Replay sessions for audits and investigations—no digging through log files.
• Pipe data to your SIEM for alerting and compliance reporting.

Automate Account Lifecycle Management

• Offboard users automatically—no need to manually drop accounts from mysql.user.
• Rotate credentials without interrupting workflows.
• Reduce attack surface by enforcing short-lived, ephemeral access instead of long-lived accounts.

Skip the CLI scripts, GUIs, and user audits. StrongDM gives you real-time, secure control over MySQL access—without the operational overhead.

Book a demo to see how StrongDM transforms database access into a secure, manageable, and auditable process.