MySQL Show Users Command – Quick Guide & Pro Tips

MySQL Show Users: Managing users in MySQL is a critical part of administering databases securely and efficiently. While MySQL doesn’t have a direct SHOW USERS command, there are reliable methods to list all users and gather information about their privileges and authentication methods.

This article explores everything you need to know about MySQL user management: how to list users, best practices, common commands, security tips, and advanced queries. If you’re a database administrator or developer looking to master MySQL user management, this guide is for you.

MySQL is one of the most popular relational database management systems in the world. Managing users is central to database security and functionality. Each MySQL user is associated with a username and a host from which the user can connect. These users are stored in the mysql.user system table.

Key Concepts:

• Username: Identifier for the user
• Host: The IP address or hostname from which the user can connect
• Privileges: Defines what operations the user can perform

Authentication plugin: Method used to validate the user’s password

There is no built-in SHOW USERS command in MySQL. Unlike commands like SHOW DATABASES or SHOW TABLES, MySQL doesn’t provide a shortcut to list users.

Alternative Method

Instead, you can run the following SQL query:

SELECT user, host FROM mysql.user;

This will give you a list of all users in the MySQL database and the hosts they are associated with.

Prerequisites

To execute queries on the mysql.user table, you must have SELECT privilege on the mysql database. This usually requires root or administrative access.

List All Users

SELECT user, host FROM mysql.user;

Output Example:

This output tells you that users exist with specific host permissions. % represents any host.

To create a new user:

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘strongpassword’;

Always use strong, randomly generated passwords.

You can then assign privileges:

GRANT ALL PRIVILEGES ON dbname.* TO ‘newuser’@’localhost’;

Change Password

ALTER USER ‘user’@’host’ IDENTIFIED BY ‘newpassword’;

Delete User

DROP USER ‘user’@’host’;

Rename User

RENAME USER ‘olduser’@’host’ TO ‘newuser’@’host’;

Use caution with these commands to avoid removing access for critical services.

List Users with Password Expiry Enabled

SELECT user, host, password_expired FROM mysql.user WHERE password_expired = ‘Y’;

List Users by Authentication Plugin

SELECT user, host, plugin FROM mysql.user GROUP BY plugin;

Useful for understanding how users are being authenticated (e.g., caching_sha2_password, mysql_native_password).

While mysql.user is most common, in MySQL 8.0+, you can use:

SELECT * FROM information_schema.user_privileges;

This provides a more secure and standardized view, especially in restricted environments.

1. Avoid using the root account for application-level tasks.
2. Use % wildcard sparingly to prevent access from any host.
3. Enable password expiration for users with elevated access.
4. Use strong passwords and update them regularly.
5. Limit privileges to only what’s necessary (principle of least privilege).
6. Enable and enforce SSL for secure connections.
7. Log and audit user activities.
8. Monitor user accounts regularly for suspicious access or unused accounts.

You can export the list of users to a file using command-line:

mysql -u root -p -e “SELECT user, host FROM mysql.user;” > user_list.txt

This can be useful for backup or audit purposes.

If you prefer a visual interface, here are some tools:

• phpMyAdmin: Web-based interface for managing MySQL databases
• MySQL Workbench: Official tool from Oracle with advanced user admin features
• DBeaver: Cross-platform tool that supports MySQL and many other databases
• HeidiSQL: Lightweight Windows-based tool for MySQL admin

These tools allow you to view users, create new ones, and assign privileges via UI.

Error: Access Denied for User

ERROR 1045 (28000): Access denied for user ‘user’@’host’

Fix: Ensure you’re using the correct username, host, and password. Also check the user@host combination exists.

Error: User Already Exists

ERROR 1396 (HY000): Operation CREATE USER failed for ‘user’@’host’

Fix: Either drop the existing user or use CREATE OR REPLACE USER instead.

Error: No Privileges on mysql.user

ERROR 1142 (42000): SELECT command denied to user

Fix: Run the query as a user with SELECT privilege on the mysql database.

MySQL 8.0 introduced several enhancements:

• Roles: Create roles and assign them to users
• Password expiration policies
• Account locking to prevent brute-force attacks
• JSON-based audit logs

Example: Creating and Assigning Roles

CREATE ROLE ‘developer’;

GRANT SELECT, INSERT ON dbname.* TO ‘developer’;

GRANT ‘developer’ TO ‘user1’@’localhost’;

You can use cron jobs or scheduled tasks to monitor user logins, privileges, and expired passwords by querying the mysql.user or information_schema tables regularly and comparing changes over time.

You can write SQL scripts to create, update, or drop multiple users automatically. These scripts can be version-controlled and used during deployments.

Use mysqldump to back up user privileges:

mysqldump -u root -p mysql user > mysql_user_backup.sql

This ensures you can restore your user data in case of a disaster.

In businesses, MySQL may work with LDAP to authenticate users from a single place. This feature is supported by plugins like auth_ldap_simple.

To locate users that haven’t signed in for a long time, check the MySQL audit logs or the login history (if you have it). Then, think about disabling them.

Use SQL views or scheduled reports to make custom reports that show how user activity, password changes, and permission changes vary over time.

MySQL doesn’t have a direct SHOW USERS command, but it does have sophisticated capabilities for listing, managing, and securing user accounts. To keep a database safe, you need to be good at managing users. A database administrator needs to know how to get user data and understand privileges.

You can keep a strong user management system by querying the mysql.user table, utilizing the SHOW GRANTS command, and taking advantage of GUI tools and automation. These methods, along with strong security procedures and role-based access control, make sure that your MySQL system is both safe and fast.