Skip to main content
Database Administration & Troubleshooting

MySQL Users CLI Reference: Syntax & Troubleshooting Guide

6 min read

check mysql users is the operation of querying the mysql.user table to list all database user accounts, their hosts, and authentication details using SQL SELECT statements via the mysql CLI client.

mysql -u root -p -e "SELECT User, Host, authentication_string FROM mysql.user;"

Syntax

# Login to MySQL server as root (password prompted)
mysql -u root -p

# Then execute one of the following queries inside the MySQL prompt:

# List all users with host and password hash
SELECT User, Host, Password FROM mysql.user;

# Show only unique usernames (each once)
SELECT DISTINCT User FROM mysql.user;

# Full user table (all columns)
SELECT * FROM mysql.user;

# Display grants for a specific user
SHOW GRANTS FOR 'username'@'host';

Cheat Sheet: MySQL User Listing Commands

Action CLI Command (inside mysql prompt) Key Flag / Clause Description
Login as root mysql -u root -p -u root -p Authenticate to MySQL server
List all users & hosts SELECT User, Host, Password FROM mysql.user; User, Host, Password Shows all user/host pairs and password hashes (MySQL ≤ 8.0; in 8.0+ use authentication_string)
Show unique usernames SELECT DISTINCT User FROM mysql.user; DISTINCT User Returns each username once, regardless of host
Full user details SELECT * FROM mysql.user; N/A All columns including privileges, account locking, password expiration
Show current user SELECT CURRENT_USER(); N/A Displays the user and host you are logged in as
Display grants for a user SHOW GRANTS FOR 'username'@'host'; FOR 'user'@'host' Lists all global, database, and table-level privileges
Identify password‑expired users SELECT User, Host, password_expired FROM mysql.user WHERE password_expired = 'Y'; password_expired = 'Y' MySQL 5.7+; shows accounts with expired passwords
See also  Concat In SQL: Syntax, Flags, Examples & Troubleshooting Guide

Options and Flags

Flag Type Default Description
-u string current OS user MySQL username to connect with (e.g., -u root)
-p N/A prompt Prompt for password; may also specify inline (e.g., -p'password' — insecure, use interactive)
-h string localhost MySQL server hostname or IP (e.g., -h 127.0.0.1)
-P integer 3306 TCP port number (e.g., -P 3307)
--protocol string TCP Connection protocol: TCP, SOCKET, PIPE, MEMORY
-e string N/A Execute single query and exit (non-interactive), e.g., mysql -u root -p -e "SELECT User FROM mysql.user;"

Tested on Ubuntu 22.04 with MySQL 8.0.32 (mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64).

Usage Examples

Example 1: List all user accounts with host and authentication

mysql -u root -p -e "SELECT User, Host, authentication_string FROM mysql.user WHERE User NOT IN ('mysql.session','mysql.sys','root') ORDER BY User;"

Context: Directly queries the mysql.user table using -e to avoid an interactive session. The WHERE clause filters out internal system accounts (mysql.session, mysql.sys) and the superuser root to focus on application users. Output shows the username, allowed host, and hashed password string. Note: in MySQL 8.0+, the column is authentication_string; in older versions it is Password.

Example 2: Check grants for a specific user

mysql -u root -p -e "SHOW GRANTS FOR 'app_user'@'192.168.1.%';"

Context: Quickly verify what privileges a particular user (e.g., app_user allowed from subnet 192.168.1.0/24) holds. The output lists each GRANT statement as executed. This is essential for security audits and troubleshooting permission failures.

Example 3: Identify users with expired passwords

mysql -u root -p -e "SELECT User, Host, password_expired FROM mysql.user WHERE password_expired = 'Y';"

Context: In MySQL 5.7+, the password_expired column indicates accounts whose password must be changed at next login. Running this regularly (via cron) helps flag accounts that might cause application connection failures.

See also  psql Date Difference: Syntax, Examples, and Best Practices

Troubleshooting & Common Errors

Error Message Root Cause Resolution Command
ERROR 1045 (28000): Access denied for user 'root'@'localhost' Incorrect root password or MySQL configured with auth_socket plugin (Ubuntu default) Use sudo mysql to login without password, then ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpass';
ERROR 1142 (42000): SELECT command denied to user 'app'@'%' for table 'user' User lacks SELECT privilege on the mysql database Grant SELECT ON mysql.* TO 'app'@'%'; (requires root or CREATE USER privilege)
Unknown column 'Password' in 'field list' MySQL 8.0 removed the Password column; use authentication_string Replace Password with authentication_string in the query
mysql: [Warning] Using a password on the command line interface can be insecure. Password passed as -p'password' in plaintext; visible in process list Remove the inline password; use -p alone and enter password at the prompt, or use ~/.my.cnf with restricted permissions

Closing Tip

For an auditable, non‑interactive user audit without exposing passwords in process lists, use mysql -u root -p --batch -e "SELECT User, Host FROM mysql.user WHERE User NOT IN ('mysql.session','mysql.sys');", redirect output to a secure file, and review host patterns for over‑permissive % wildcards.

Multi-Cloud Comparison

Feature MySQL (self‑managed) AWS RDS MySQL Google Cloud SQL MySQL Azure Database for MySQL
List all users SELECT User, Host FROM mysql.user; Same SQL (user table is accessible) Same SQL Same SQL
Show grants SHOW GRANTS FOR 'u'@'h'; Same command Same command Same command
Check locked users SELECT User, account_locked FROM mysql.user; Same column (MySQL 8.0+) Same column Same column
Authentication method authentication_string in mysql.user Same (AWS does not modify the table) Same Same

All managed MySQL services expose the mysql.user table for reading; write access (e.g., INSERT/UPDATE/DELETE) is restricted to users with CREATE USER or SUPER privileges but is possible through standard SQL.

See also  ALTER TABLE ADD Multiple Columns: Syntax & Troubleshooting

Frequently Asked Questions

What is the difference between querying the `mysql.user` table and using `SHOW GRANTS` to check MySQL users?

Answer: `mysql.

The `mysql.user` table (in the `mysql` system schema) provides a raw view of all user accounts, including host, authentication string, and password expiration. Use 

mysql -e "SELECT user, host, account_locked FROM mysql.user"

to see all accounts. For granular privilege review, execute 

mysql -e "SHOW GRANTS FOR 'devops'@'192.168.%';"

iteratively per user, or combine with a script.

When should I use the `–execute` flag instead of interactive mode to check MySQL users?

Answer: Use `–execute` (or `-e`) in automation scripts or CI/CD pipelines when you need non-interactive, non-TTY output; interactive mode is for….

The `-e` flag executes a single SQL statement and exits. Ideal for cron jobs or remote command execution via SSH. Example: 

mysql -u root -p -e "SELECT user, host, max_user_connections FROM mysql.user;"

For interactive analysis (e.g., multi-line queries, paging), launch the mysql client with no statement.

How do I fix error “ERROR 1449 (HY000): The user specified as a definer (‘root’@’%’) does not exist” when querying MySQL users?

Answer: Recreate the missing definer user with `CREATE USER ‘root’@’%’ IDENTIFIED BY ‘password’;` or change the affected views/triggers to an exi….

This error occurs when a stored program references a non‑existent account. To resolve without recreating, identify the defective object: 

mysql -e "SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DEFINER FROM information_schema.ROUTINES WHERE DEFINER = 'root@%';"

Then use `ALTER DEFINER = ‘root’@’localhost’ SQL SECURITY INVOKER` to reassign.

Does `SELECT user, host FROM mysql.user` work identically on AWS RDS, Google Cloud SQL, and Azure Database for MySQL?

Answer: Yes, the `mysql.

In RDS, `SELECT user, host FROM mysql.user` works, but `password` or `authentication_string` will show ``. On Azure, the table includes platform accounts (`azure_superuser`). Use 

mysql -h  -u admin -p -e "SELECT user, host, account_locked FROM mysql.user;"

What is the fastest way to check all MySQL users and their current privileges in one command?

Answer: Use `mysql -e “SELECT user, host, GROUP_CONCAT(DISTINCT PRIVILEGE_TYPE) as privileges FROM information_schema.

This single query aggregates all global privileges per user/host pair, avoiding per‑user `SHOW GRANTS` calls. For a detailed breakdown including database‑level grants, extend with: 

mysql -e "SELECT Grantee, Table_schema, GROUP_CONCAT(DISTINCT privilege_type) FROM information_schema.SCHEMA_PRIVILEGES GROUP BY Grantee, Table_schema;"