How to list all users in a MySQL or MariaDB database

Managing a MySQL server, MariaDB server or PersonaDB Server often requires generating a complete list of users created in the database. This can help manage permissions, identify potential security issues, or simply keep track of who has access. MySQL provides various ways to display user information using SQL queries and built-in commands. Here’s how to do it step by step.

Log in to Your MySQL Server

The first step to retrieving a list of MySQL users is to log in to your server. You can do this from the command line by running:

mysql -u root -p

After running the command, you’ll be prompted to enter the password for the root user (or any other administrative user you’re using). Enter the appropriate password to access the MySQL console.

List Users with the SHOW USERS Command

While MySQL doesn’t have a direct SHOW USERS command, you can query the mysql.user table, which stores all user-related data. To get a complete list of users, execute the following query:

SELECT User, Host FROM mysql.user;

This query will display a table showing the usernames (User) and the hosts (Host) they are allowed to connect from.

Display Unique Usernames Only

If you’re only interested in seeing unique usernames regardless of their host, you can refine the query as follows:

SELECT DISTINCT User FROM mysql.user;

The DISTINCT keyword ensures that duplicate usernames are removed, resulting in a cleaner, more concise list.

Additional Information About Users

The mysql.user table contains more than just usernames and host information. Some of the key fields include:

• plugin: Specifies the authentication method used by the user.
• authentication_string: Stores encrypted passwords.
• ssl_cipher: Indicates if the user is configured to use secure connections (SSL).
• max_questions, max_updates, max_connections: Limits that may be set for each user.

To see all the available fields in the mysql.user table, you can run:

DESCRIBE mysql.user;

This will provide a list of all the columns in the table.

Display Information About the Current User

To find out details about the user currently connected to the database, you can use the following command:

SELECT USER();

This command returns the username and the host the user is connected from, such as:

root@localhost

For more detailed information about the authenticated user, use:

SELECT CURRENT_USER();

This command shows the user as authenticated by the server.

Security Considerations

Accessing the mysql.user table requires administrative privileges, typically granted to the root user or others with appropriate management permissions. Additionally:

• Avoid displaying sensitive information, such as encrypted passwords, to unauthorized users.
• Regularly review and manage user accounts and their privileges to maintain a secure environment.
• Disable or delete obsolete accounts to minimize the risk of unauthorized access.

Conclusion

Listing all users in a MySQL server is a critical task for database administration, especially in environments with multiple users and custom permissions. By using queries such as SELECT User, Host FROM mysql.user, administrators can quickly obtain an overview of all configured accounts. Proactive user and privilege management is essential for ensuring the security and efficiency of your database system.