How to Show All Users in MySQL
Grace Collins
Solutions Engineer · Leapcell

Key Takeaways
- You can list all MySQL users using a query on the
mysql.user
table. - Access to the
mysql.user
table requires administrative privileges. - Filtering by host or selecting distinct usernames can narrow down results.
When managing a MySQL database server, it's often necessary to view the list of all existing user accounts. This can help you audit security settings, manage permissions, and troubleshoot authentication issues. In this article, we’ll walk through how to list all MySQL users using SQL queries.
Prerequisites
Before you begin, ensure that:
- You have MySQL installed and running.
- You have access to a MySQL user account with sufficient privileges (e.g.,
root
or another administrative user).
You can log in to the MySQL shell using:
mysql -u root -p
You’ll be prompted to enter the root password.
Viewing All MySQL Users
MySQL stores user information in the mysql.user
system table. To see a list of all users, you can run the following SQL command:
SELECT User, Host FROM mysql.user;
This will return a list of users along with the host they are allowed to connect from. For example:
+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | localhost |
| app_user | % |
| reporting_user | 192.168.% |
+------------------+-----------+
User
is the username.Host
specifies where the user is allowed to connect from.%
means any host.localhost
means only local connections are allowed.
Optional: Display Unique Usernames
If you're only interested in the unique usernames regardless of host, you can modify the query:
SELECT DISTINCT User FROM mysql.user;
Filtering Users by Host
You can also filter the users by host, for example:
SELECT User FROM mysql.user WHERE Host = 'localhost';
Permissions Required
You must have the SELECT
privilege on the mysql.user
table to run these queries. Typically, administrative users like root
have this permission.
If you get a “permission denied” error, you may need to request access from your DBA or log in as a more privileged user.
Security Note
Avoid granting general access to the mysql.user
table to non-administrative users. It contains sensitive information about user authentication and permissions.
Conclusion
Listing all MySQL users is straightforward using a simple SQL query. Whether you're auditing accounts or managing access, understanding how to query the mysql.user
table is an essential skill for any database administrator.
FAQs
Use SELECT User, Host FROM mysql.user;
to list all users and their allowed hosts.
MySQL accounts are defined by both username and host, so one user may have multiple host entries.
Ensure you're logged in as a user with sufficient privileges, like root
.
We are Leapcell, your top choice for hosting backend projects.
Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:
Multi-Language Support
- Develop with Node.js, Python, Go, or Rust.
Deploy unlimited projects for free
- pay only for usage — no requests, no charges.
Unbeatable Cost Efficiency
- Pay-as-you-go with no idle charges.
- Example: $25 supports 6.94M requests at a 60ms average response time.
Streamlined Developer Experience
- Intuitive UI for effortless setup.
- Fully automated CI/CD pipelines and GitOps integration.
- Real-time metrics and logging for actionable insights.
Effortless Scalability and High Performance
- Auto-scaling to handle high concurrency with ease.
- Zero operational overhead — just focus on building.
Explore more in the Documentation!
Follow us on X: @LeapcellHQ