How can I get a list of user accounts using the command line in MySQL?

Managing users and their privileges is a vital part of database administration. Sometimes you need a quick overview of who has access to your MySQL server. Below, we’ll explore the command-line approach to listing user accounts in MySQL and share best practices for user management.

1. Accessing the mysql Command Line

Before retrieving user accounts, ensure you’re able to log into MySQL from your terminal or command prompt:

mysql -u [username] -p

Replace [username] with a privileged MySQL user (often root), then enter the corresponding password when prompted.

2. Querying the mysql.user Table

MySQL stores user information in the mysql.user table. You can view user accounts by running:

SELECT User, Host 
FROM mysql.user;

• User: The username of the MySQL account.
• Host: The host or domain from which the user can connect (e.g., localhost or % for any host).

2.1 Adding More Columns

For a more detailed view (including authentication plugins or password expiration info):

SELECT User, Host, plugin, authentication_string 
FROM mysql.user;

Or view privileges if needed:

SHOW GRANTS FOR 'username'@'host';

3. Understanding the Output

• %: A wildcard allowing connections from any IP address or host.
• localhost: User can only connect from the local machine running MySQL.
• authentication_string: Stores the hashed password (not the plaintext).

4. Best Practices

1. Create and Grant Privileges Strategically: Use the GRANT statement or CREATE USER + GRANT for assigning privileges.
2. Limit Host Access: Restrict accounts to known IPs or the localhost to reduce the attack surface.
3. Use Strong Passwords: Regularly update authentication_string with secure, unique credentials.
4. Revoke Unused Accounts: Remove or lock old accounts to maintain a secure environment.

5. Further Learning: SQL Mastery

If you want to refine your SQL skills—especially around security, performance tuning, and advanced queries—check out these highly recommended courses from DesignGurus.io:

• Grokking SQL for Tech Interviews – Covers real-world scenarios and interview-focused SQL questions to build your confidence.
• Grokking Database Fundamentals for Tech Interviews – Delve into database design, indexing, and normalization best practices.

6. Preparing for Interviews? Try Mock Interviews

In addition to honing your SQL knowledge, practicing with real, timed scenarios can be invaluable. DesignGurus.io’s Mock Interviews let you get personalized feedback from ex-FAANG engineers, helping you refine your approach to database questions and beyond.

Conclusion

Listing user accounts in MySQL via the command line is straightforward once you know where MySQL stores user data. With a quick SELECT statement against the mysql.user table, you’ll gain insights into who has access and how. By following best practices around privilege assignment and security, you can keep your database environment both manageable and safe.

Explore DesignGurus.io for more courses, blogs, and resources to elevate your SQL and database administration skills!