Managing users in MySQL is crucial for database security and organization. While MySQL doesn’t have a SHOW USERS command, you can use SQL queries to list and manage users effectively. This guide provides step-by-step instructions and essential commands for user management in MySQL.

Why Create Users in MySQL?

The default root user has full access to the MySQL server, posing significant security risks. Creating individual users with specific permissions minimizes vulnerabilities and ensures controlled access to databases.

How to List Users in MySQL

1. Log in as Root User

Access your MySQL server via SSH and log in as the root user: 

1
sudo mysql -u root -p

Enter the root password when prompted.

2. List All Users

Run the following query to list all users: 

1
SELECT user, host FROM mysql.user;

This displays usernames and their associated hosts.

3. View Unique Usernames

To avoid duplicates caused by multiple hosts, use: 

1
SELECT DISTINCT user FROM mysql.user;

4. Display Detailed User Information

For comprehensive user details, execute: 

1
SELECT * FROM mysql.user;

This shows all fields in the mysql.user table.

Advanced User Management

1. Check Current User

To view the currently logged-in user: 

1
SELECT current_user();

2. Identify Locked or Expired Accounts

To find users with locked accounts or expired passwords: 

1
SELECT user, host, account_locked, password_expired FROM mysql.user;
  • Y in account_locked indicates a locked account.
  • Y in password_expired means the password has expired.

3. Unlock Accounts or Reset Passwords

  • Unlock an Account: 
    1
    ALTER USER 'username'@'host' ACCOUNT UNLOCK;
  • Reset an Expired Password: 
    1
    ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

4. View User Privileges

To check a user’s permissions: 

1
SHOW GRANTS FOR 'username'@'host';

5. Grant or Revoke Privileges

  • Grant Privileges: 
    1
    GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';
  • Revoke Privileges: 
    1
    REVOKE INSERT ON database_name.* FROM 'username'@'host';

Using PHPMyAdmin to List Users

If you prefer a graphical interface:

  1. Log in to PHPMyAdmin.
  2. Navigate to the SQL tab.
  3. Run the query: 
    1
    SELECT user, host FROM mysql.user;

Conclusion

Managing users in MySQL is essential for maintaining database security and efficiency. By using the commands and techniques outlined above, you can list users, check privileges, and manage accounts effectively.

For more advanced MySQL tips, explore our MySQL Cheat Sheet.