The World of MySQL & MariaDB

MySQL User List & Permission Management – Complete Guide

Users and Permissions
目次

1. Introduction

MySQL user management is an essential task for database security and efficient operation. In particular, understanding which users can access the database and what permissions they have enables access restrictions and security enhancements. In this article, we cover a wide range from how to display the MySQL user list, retrieve detailed information, create and manage users, to troubleshooting common errors. This will help you understand the fundamentals of MySQL user management and practical procedures.

2. How to Retrieve the User List

To retrieve a list of users in MySQL, use the `mysql.user` table. This table stores information for all users registered in MySQL, and you can display the needed information with simple commands.

Basic User List Display

First, log in to MySQL and run the following command.
SELECT Host, User FROM mysql.user;
This command displays each user’s “host name” and “user name” in a list. For example, host names such as “localhost” or “127.0.0.1” may appear, which refer to local IPv4 and IPv6 connections respectively. This is a common setting for user management in local environments.

Retrieving All User Information

Additionally, to view more detailed information, you can display all columns of the `mysql.user` table with the following command.
SELECT * FROM mysql.user;
This command shows detailed information for each user (e.g., authentication data and privilege settings), but typically it’s easier to read if you specify only the columns you need. While using the above can provide broader information, selecting specific columns is also useful for a visually organized output.

3. Retrieve Detailed User Information

In MySQL, you can also retrieve user password hashes and privilege information. This makes user management even more efficient.

How to Display Password Hashes

In MySQL, passwords are encrypted and stored as hashes in the “authentication_string” column. You can use the following command to view the host name, user name, and password hash for each user.
SELECT Host, User, authentication_string FROM mysql.user;
The result lets you verify whether each user has a password set. The password hash values are displayed, but they cannot be decrypted, preserving security. This is a very handy way to check new users and privilege grant status.

Checking a Specific User’s Privileges

To find out what privileges a user has on databases or tables, use the “SHOW GRANTS” command.
SHOW GRANTS FOR 'username'@'hostname';
This displays a list of all privileges granted to the specified user. It is a useful command for database administrators when deciding which privileges to grant to a particular user.

4. User Creation and Management

This introduces how to create a new user in MySQL and set or modify permissions.

Creating a New User

To create a new user, use the CREATE USER statement. Below is an example that creates the user “example_user” and sets the password to “password123”.
CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'password123';
After creating the user, you need to run FLUSH PRIVILEGES to apply the changes in MySQL.
FLUSH PRIVILEGES;

Cautions When Deleting Users

To remove an unnecessary user, use the DROP USER command. However, before deleting a user, it’s important to verify the impact on related privileges and data. For example, if the user being removed is integrated with other systems or services, deletion could cause loss of access, so caution is required.

5. Common Errors and Their Solutions

MySQL user management can sometimes produce errors. Here we explain common errors and how to resolve them.

Permission Error “Access denied”

The “Access denied” error occurs when a user lacks the proper privileges for a database or table. In this case, an administrator can resolve it by granting the necessary privileges to the user. You can grant privileges to a specific database with the following command.
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';
FLUSH PRIVILEGES;

User Authentication Error “Unknown user”

The “Unknown user” error occurs when the specified user does not exist. If you see this error, verify that the username and hostname are correctly specified, or check that the user exists in the “mysql.user” table.

Password-Related Errors

If you encounter password mismatches or authentication errors, you can resolve them by resetting the password. Use the following command to reset the password.
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

6. Summary

In this article, we explained how to retrieve a list of MySQL users, view detailed information, create and manage users, and also covered common errors and their solutions. User management is directly tied to strengthening database security, so it is important to regularly review user information and privileges to reduce the risk of unauthorized access. By applying these methods, you can operate MySQL safely and efficiently.