By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. The Importance of MySQL User Management

1.1 What Is MySQL User Management?

MySQL user management is essential for both the security and operation of your database. Since databases often contain sensitive information, proper user control is critical to prevent unauthorized access. By assigning specific privileges to each user and allowing only necessary actions, you can ensure data protection and maintain system stability.

1.2 When You Need to Check the User List

Reviewing the user list is particularly important during security audits and user management tasks. For example, when adding a new user or modifying permissions for existing users, checking the current user list helps ensure there are no unnecessary accounts or permission issues. It’s also helpful for troubleshooting, as identifying who is connecting from which host can assist in pinpointing the source of a problem.

2. Where MySQL Stores User Information

2.1 Overview of the mysql.user Table

In MySQL, user information is stored in the user table within the mysql database. This table contains a variety of user-related details, including usernames, hostnames, authentication plugins, and password expiration settings. By referencing this table, you can access detailed information about each user.

2.2 Permissions Required to Access the mysql.user Table

Accessing the mysql.user table requires special privileges. Typically, only the root user or users with SELECT permission on this table can access it. This restriction prevents regular users from viewing others’ information, helping to maintain the security of the database.

3. How to Retrieve a List of MySQL Users

3.1 Basic Query to Retrieve User List

To get a list of MySQL users, you can run a query that selects the Host and User columns from the mysql.user table. Here’s the command:

SELECT Host, User FROM mysql.user;

This query will display all users currently registered in the database and indicate which hosts they are allowed to connect from.

3.2 Understanding the Query Results

The results will show combinations of users and their allowed hosts. The Host column indicates the host(s) from which the user can access the database, while the User column shows the usernames. For example, if you see localhost, that means the user is only allowed to connect from the local server.

4. Checking Detailed User Information

4.1 Retrieving Password Information

To view detailed information about users, including their passwords, run a query that includes the Password column from the mysql.user table:

SELECT Host, User, Password FROM mysql.user;

This query retrieves information including the users’ encrypted passwords.

4.2 Why Encrypted Passwords Matter

The passwords retrieved from this table are encrypted, which is crucial for maintaining security. If passwords were stored in plain text, there would be a much higher risk of unauthorized access. Encrypting passwords is a fundamental practice for securing your database environment.

5. Verifying User Authentication Details

5.1 Checking the Currently Connected User

To check which user is currently connected to MySQL, you can use the user() and current_user() functions. These functions return the username used for the connection and the actual authenticated user, respectively.

SELECT user(), current_user();

In most cases, both functions return the same value. However, they may differ if the user is anonymous or due to specific authentication settings.

5.2 Importance of Authenticated Username and Hostname

From a security perspective, knowing the authenticated username and hostname is critical. This allows you to clearly see which user is accessing the database and from where. It’s especially useful during security audits or when investigating unauthorized access.

6. Reviewing Settings When Creating Users

6.1 Using the SHOW CREATE USER Command

To see how a specific user was created, use the SHOW CREATE USER command. This will display the SQL statement used to create that user:

SHOW CREATE USER 'username'@'hostname';

This command shows detailed settings such as authentication methods and password policies used during user creation.

6.2 Example: Viewing a User’s Creation Settings

For example, to view how the user tanaka@localhost was created, run the following command:

SHOW CREATE USER 'tanaka'@'localhost';

This will show what authentication plugin is used, the password expiration policy, and any other security settings that were applied when the user was created.

7. Checking User Privileges

7.1 Using the SHOW GRANTS Command to Check Privileges

To check the privileges assigned to a specific user, use the SHOW GRANTS command. This command displays all the permissions that have been granted to the user:

SHOW GRANTS FOR 'username'@'hostname';

Running this command will list all the privileges associated with that user.

7.2 Importance of Privilege Management and Best Practices

Proper privilege management is a key aspect of database security. By assigning only the necessary privileges, you can prevent unauthorized actions and protect the integrity and confidentiality of your data. Special care should be taken with the root user since it has full administrative access. For regular users, follow the principle of least privilege—grant only what’s needed and review permissions periodically as part of your security best practices.

8. Summary

8.1 Key Points in MySQL User Management

Reviewing and properly managing MySQL users is fundamental to database security. By retrieving user information from the mysql.user table and checking their authentication details and privileges, you can strengthen the overall security of your system.

8.2 Next Steps in User Management

After reviewing the user list, consider taking the next steps such as auditing user privileges and removing any unnecessary accounts. You can also improve security by enforcing strong password policies and conducting regular security audits.

9. References and Resources

• Official MySQL Documentation: MySQL 8.0 Reference Manual
• Recommended Security Book: Security Practices for Building Secure Web Applications by Hiroshi Tokumaru (Japanese)