Practical MySQL & MariaDB

How to List MySQL Users (Host & Username) + Check Privileges and Authentication

Users and Permissions
目次

1. The Importance of MySQL User Management

1.1 What Is MySQL User Management?

MySQL user management is a core part of database security and operations. Because databases often contain sensitive information, proper user management is essential to prevent unauthorized access. By granting each user specific privileges and allowing only the necessary operations, you can protect data and maintain system stability.

1.2 Situations Where You Need to Check the User List

Checking the user list is especially important during security audits and user administration. For example, when adding a new user or changing privileges for an existing user, reviewing the current user list helps you verify whether unnecessary users exist or whether there are any privilege issues. It is also useful for troubleshooting, because confirming which user is connecting from which host makes it easier to identify the cause of a problem.

2. Where MySQL User Information Is Stored

2.1 Overview of the mysql.user Table

In MySQL, user information is stored in the user table inside the mysql database. This table contains a wide range of user-related details, such as the username, host, authentication plugin, and password expiration settings. By referencing this table, you can retrieve detailed information about MySQL users.

2.2 Privileges Required to Access the mysql.user Table

Access to the mysql.user table requires special privileges. Typically, only the root user or users with SELECT privileges on this table can access it. This restriction prevents regular users from viewing other users’ information and helps maintain database security.

3. How to Retrieve a User List

3.1 Basic Query to List Users

To retrieve a list of users, you can run a query that selects the Host and User columns from the mysql.user table. Run the command as follows:

SELECT Host, User FROM mysql.user;

This query allows you to check all users that exist in the database and which hosts they are allowed to connect from.

3.2 Explanation of the Output

As a result of the query, a list of user and host combinations will be displayed. The Host column shows which host the user can access MySQL from, and the User column shows the username. For example, if it is shown as localhost, that user can only connect from the local machine.

4. Checking User Details

4.1 Retrieving Password Information

To check detailed user information, run a query that includes the Password column from the mysql.user table:

SELECT Host, User, Password FROM mysql.user;

This query retrieves information that includes the user’s encrypted password.

4.2 Why Encrypted Passwords Matter

The passwords you retrieve are encrypted. This is essential for maintaining security. If passwords were stored without encryption, the risk of unauthorized access by malicious users would increase significantly. For this reason, password encryption is a fundamental part of database security.

5. Checking User Authentication Information

5.1 Check the Currently Connected User

To check the user currently connected in MySQL, use the user() function and the current_user() function. These functions return the username used at connection time and the actually authenticated username, respectively.

SELECT user(), current_user();

In most cases, both functions return the same value, but they may differ depending on anonymous users or specific authentication settings.

5.2 Why the Authenticated Username and Host Matter

From a security perspective, what matters most is the actually authenticated username and host. By accurately understanding this information, you can clearly confirm which user is accessing the database from which host. This is useful for security audits and detecting unauthorized access.

6. Checking User Creation Settings

6.1 How to Use the SHOW CREATE USER Command

To check how a specific user was created, use the SHOW CREATE USER command. This command displays the SQL statement that was used to create the specified user.

SHOW CREATE USER 'username'@'hostname';

With this command, you can review detailed settings at creation time, such as the authentication method and password configuration.

6.2 Example: Checking User Creation Settings

For example, if you want to check the creation settings for a user named tanaka@localhost, run the following command:

SHOW CREATE USER 'tanaka'@'localhost';

This will display details such as which authentication plugin the user is using, how password expiration is configured, and what security requirements are applied.

7. Checking User Privileges

7.1 Check User Privileges with the SHOW GRANTS Command

To check which privileges are assigned to a user you created, use the SHOW GRANTS command. This command displays all privileges granted to the specified user.

SHOW GRANTS FOR 'username'@'hostname';

When you run this command, a list of privileges granted to the user will be displayed.

7.2 Why Privilege Management Matters (Best Practices)

Managing user privileges is an essential part of maintaining database security. Proper privilege settings help prevent unnecessary operations and protect data integrity and confidentiality. In particular, the root user has powerful privileges, so it must be used with extreme caution. For regular users, grant only the minimum required privileges and review them regularly as a security best practice.

8. Summary

8.1 Key Points of MySQL User Management

Checking the MySQL user list and managing it properly is a fundamental part of database security. By retrieving user information from the mysql.user table and verifying user authentication details and privileges, you can strengthen system security.

8.2 Next Steps for User Management

After confirming the user list, consider reviewing privileges and removing unnecessary users as the next step. You can further improve database security by strengthening password policies and performing regular audits.

9. References and Resources