Introduction

Listing MySQL user accounts from the command line is a standard administrative task for auditing access and troubleshooting authentication issues. The exact query depends on MySQL version and your privileges. A secure workflow should enumerate accounts, inspect grants, and avoid exposing sensitive metadata unnecessarily.

Connect with an Account That Can Inspect Users

To query user metadata, connect with an administrative account or one that has permission to read user-related system tables.

mysql -u root -p

Once connected, confirm server version first because privilege metadata layout differs between major versions.

SELECT VERSION();

Knowing the version helps you choose the safest and most portable query path.

List Accounts from the MySQL System Schema

For many versions, account identity is represented by user and host pair. This pair is the real principal key in MySQL authentication.

SELECT User, Host
FROM mysql.user
ORDER BY User, Host;

Typical output includes entries such as:

• root at localhost
• application users bound to specific subnets
• service accounts limited to loopback

Always interpret User with Host. The username alone is not unique in MySQL account management.

Use SHOW GRANTS for Effective Permissions

Enumerating accounts is only step one. You usually need effective privilege review.

SHOW GRANTS FOR 'app_user'@'10.0.0.%';

This command reveals role grants and privilege scopes that may not be obvious from account listing alone.

For broad audits, generate statements dynamically:

SELECT CONCAT("SHOW GRANTS FOR '", User, "'@'", Host, "';") AS grant_cmd
FROM mysql.user;

Run generated commands in a controlled environment and store results in audit records.

MySQL 8 and Privilege Metadata Notes

MySQL 8 introduced role features and additional metadata tables. While mysql.user remains common for basic listing, role-based access design means you should evaluate both direct grants and role assignments.

Useful role inspection pattern:

SELECT FROM_USER, FROM_HOST, TO_USER, TO_HOST
FROM mysql.role_edges
ORDER BY TO_USER, TO_HOST;

This helps map who inherits which role-based privileges.

Command-Line Friendly Reporting

If you need machine-readable output for scripts or audits, use batch mode options.

mysql -u root -p --batch --skip-column-names -e "SELECT User,Host FROM mysql.user ORDER BY User,Host;"

This avoids formatted table borders and simplifies integration with shell tooling.

For CSV-like output:

mysql -u root -p --batch --raw -e "SELECT User,Host FROM mysql.user" > mysql_users.tsv

Keep generated files protected because account names and host patterns are sensitive operational data.

Security and Operational Practices

Account enumeration is useful, but it should be handled with least-privilege and audit discipline.

Recommended practices:

• restrict who can read account metadata
• rotate credentials for script-based admin jobs
• review wildcard hosts such as % regularly
• track dormant accounts and remove unused principals
• include account review in change-management process

These steps reduce attack surface and prevent privilege drift over time.

Troubleshooting Access Errors

If SELECT from mysql.user fails with permission error:

1. verify current account identity
2. check SHOW GRANTS for current account
3. escalate with a dedicated audit role rather than full superuser when possible

Also confirm you are querying the intended server. In multi-environment setups, administrators often connect to the wrong host and misread account state.

Common Pitfalls

• Treating username alone as unique account identity and ignoring host component.
• Running audit queries from over-privileged accounts without access controls.
• Assuming account list equals effective permissions without checking grants and roles.
• Exporting account metadata to unsecured files or shared locations.
• Using wildcard host entries broadly and forgetting to tighten them later.

Summary

• List MySQL accounts with User and Host together for accurate identity context.
• Use SHOW GRANTS and role metadata to understand actual privileges.
• Adjust audit queries and expectations based on MySQL version.
• Prefer machine-readable command-line output for repeatable reviews.
• Combine enumeration with least-privilege and lifecycle governance for secure operations.