Complete Guide to Retrieving User Account Lists in MySQL Command Line

Fundamentals of MySQL User Account Queries

In MySQL database management, retrieving user account lists is a fundamental yet crucial task. Contrary to common misconceptions, MySQL does not provide a direct SHOW USERS command to display user lists. Instead, it requires querying system tables to obtain relevant information.

Basic User Query Methods

The most straightforward approach is to query the mysql.user table, which stores all user account information for the MySQL server. The basic query statement is as follows:

SELECT User FROM mysql.user;

This query returns a list of all usernames, typically formatted as:

+-------+
| User  |
+-------+
| root  |
| user2 |
| user3 |
+-------+

Extended Query Fields

To obtain more comprehensive user information, the Host field can be added to the query:

SELECT User, Host FROM mysql.user;

This query method is particularly useful because in MySQL, user accounts are defined by both username and hostname. The same username may correspond to different hosts with different permission settings.

User Deduplication Processing

When users have the same username on different hosts, query results may contain duplicates. To obtain a unique username list, the DISTINCT keyword can be used:

SELECT DISTINCT User FROM mysql.user;

This method eliminates duplicate usernames, providing a clear and concise view of the user list.

mysql.user Table Structure Analysis

The mysql.user table contains rich user information fields, and the complete table structure can be viewed using the DESC command:

DESC mysql.user;

Main field types in this table include:

• Basic user information: User, Host, Password
• Privilege fields: Select_priv, Insert_priv, Update_priv, etc.
• Resource limitations: max_connections, max_questions, etc.
• Account status: Account_locked, password_expired, etc.

Specific Information Queries

Based on specific requirements, particular fields can be selected for querying. For example, to view user update privileges:

SELECT User, Update_priv FROM mysql.user;

Or to check account lock status and password expiration:

SELECT User, Host, Account_locked, password_expired FROM mysql.user;

Current User Information

To obtain information about the currently logged-in user, the built-in function can be used:

SELECT user();

This function returns the username and host information of the user executing the query.

User Privilege Management

Beyond viewing user lists, user privileges can also be viewed and managed:

SHOW GRANTS FOR 'username'@'host';

Privilege management includes granting and revoking privileges:

GRANT SELECT, INSERT ON database.* TO 'user'@'host';
REVOKE INSERT ON database.* FROM 'user'@'host';

Practical Recommendations

In practical applications, it is recommended to select appropriate query fields based on specific needs and avoid using SELECT * queries, as the mysql.user table contains numerous fields that may result in difficult-to-read output. Additionally, regularly reviewing user lists and permission settings is an important measure for maintaining database security.