The World of MySQL & MariaDB

MySQL User Management: List, Verify Permissions, Setup

Users and Permissions
目次

1. Importance and Purpose of MySQL User Management

In MySQL, “user management” is a critical process that greatly impacts database security and administrative efficiency. In environments where many users access the system, it is necessary to set permissions accurately and prevent unauthorized access. This article walks through how to retrieve a list of MySQL users, verify their permissions, and handle errors. In particular, it covers user management and access rights configuration, providing information useful for beginners through intermediate users.

2. How to View the User List in MySQL

In MySQL, user information is stored in the mysql.user table. By accessing this table, you can view all registered users.

2.1 Display Users and Hosts

First, use the following command to retrieve a basic list of usernames and hostnames.
SELECT User, Host FROM mysql.user;
This command lists each user and its originating host. For example, a user specified with localhost can only be accessed from the local machine. Controlling hostnames improves security, so you need to pay attention to the source host settings.

2.2 Retrieve Detailed User Information

If you need more comprehensive information, you can retrieve detailed user data with the following command.
SELECT * FROM mysql.user;
This command shows the username, hostname, password hash, and various settings. However, because it returns a large amount of data, it can be more efficient to limit the query to specific columns. As needed, you can filter columns such as User to narrow down the user information you want.

3. How to Check MySQL User Permissions

You can see what permissions a user has on the database using the SHOW GRANTS command. This step is important for managing permissions for each user in detail.

3.1 Steps to Verify Permissions

To check the permissions granted to a specific user, use the following command.
SHOW GRANTS FOR 'username'@'host';
For example, to view the permissions of the user main@localhost, run:
SHOW GRANTS FOR 'main'@'localhost';
The result shows which databases the specified user can access and whether they can read, write, or create tables. If you need to change access rights frequently, proper permission management helps ensure data security.

4. Verifying Password Hashes and Their Significance

In MySQL, user passwords are encrypted and stored in the authentication_string column. This hashing ensures that passwords are not directly visible, enhancing security. You can retrieve each user’s password hash with the following command.
SELECT User, Host, authentication_string FROM mysql.user;

4.1 Retrieving a Specific User’s Password Hash

If you want to check only a specific user’s hash, filter the user with a WHERE clause.
SELECT User, Host, authentication_string FROM mysql.user WHERE User='example_user';
Because the password is encrypted, you cannot retrieve the password itself, but you can verify whether a password is set. If a password reset is needed, it is common to set a new password and update the privileges.

5. How to Check Settings When Creating a User

In MySQL, you can fine‑tune settings when creating a user, but if you want to check the settings of an existing user, the SHOW CREATE USER command is handy.
SHOW CREATE USER 'username'@'host';
This command displays security‑related settings such as password expiration, account lock status, and host‑based access restrictions. In particular, password expiration and account lock status are important for security measures. Consider reviewing your account policies as needed.

6. How to Delete Users and Important Considerations

In MySQL, you can restrict database access by deleting unnecessary users. When deleting a user, be sure to fully consider the impact on the database and execute the DROP USER command.
DROP USER 'username'@'host';
After deleting a user, run the following command to refresh the privilege cache.
FLUSH PRIVILEGES;
After removing a user, it’s also important to review permissions and verify that no accidental deletions or insufficient access controls have occurred.

7. Common Errors and Their Solutions

When managing MySQL users, several errors can occur. Here we explain the typical errors and how to address them.

7.1 How to Handle Permission Errors

If a user attempts a specific operation and receives an “Access denied” error, it may be because the required permissions are missing. In that case, use the GRANT command as shown below to grant the necessary privileges.
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';
Afterward, be sure to run the FLUSH PRIVILEGES command to apply the changes.

7.2 Other Error Codes

Understanding common error codes and their causes helps you respond quickly. For example, the 1045 - Access denied error is a typical error caused by an incorrect username or password. In such cases, verify your authentication details and try again.