How to Show All Users in MySQL

Key Takeaways

1. You can list all MySQL users using a query on the mysql.user table.
2. Access to the mysql.user table requires administrative privileges.
3. 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

What SQL command shows all MySQL users?

Use SELECT User, Host FROM mysql.user; to list all users and their allowed hosts.

Why do I see multiple entries for the same user?

MySQL accounts are defined by both username and host, so one user may have multiple host entries.

What should I do if I get a permission error running the query?

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