The World of MySQL & MariaDB

How to Manage MySQL Users: User List, Permissions, and Best Practices

Users and Permissions
目次

1. Overview of MySQL Users

User management in MySQL is a crucial aspect of database security and operations. Proper user management helps prevent unauthorized access to the database and improves operational efficiency by granting the appropriate permissions. In this article, we’ll explain in detail how MySQL user information is stored and how you can display a list of users.

1.1 Importance of MySQL User Management

Since databases often store highly confidential information, proper user management is essential. By setting detailed access rights for each user, you can strengthen security and maintain data integrity.

2. Where MySQL User Information is Stored

MySQL user information is stored in the user table within the mysql database. This table contains various details related to each user, such as the username, host name, (encrypted) password, authentication plugin, and more.

2.1 Structure of the mysql.user Table

The mysql.user table has many columns, each storing information related to individual users. Typical columns include:

  • Host: Hostname from which the user connects
  • User: Username
  • Password: Encrypted password
  • plugin: Type of authentication plugin
  • Other security-related settings

To access this table, you need root privileges or SELECT permission on mysql.user.

3. Retrieving a List of MySQL Users

To get a list of MySQL users, you need to query the mysql.user table. Below, we’ll explain the basic method for listing users and how to include their passwords.

3.1 Basic Query to List Users

Here’s a simple query to display all users from the mysql.user table:

SELECT Host, User FROM mysql.user;

Running this query will list all users along with the host each user connects from.

3.2 Displaying Encrypted Passwords

To include encrypted passwords in the list, use the following query:

SELECT Host, User, Password FROM mysql.user;

This query displays the host, username, and encrypted password for each user. Note that the passwords are encrypted and you cannot retrieve the actual password.

4. Retrieving Additional User Information

MySQL also allows you to retrieve further information about users. In particular, it’s useful to check user privileges and the details of currently connected users.

4.1 Checking User Privileges

To see the privileges assigned to a specific user, use the SHOW GRANTS statement:

SHOW GRANTS FOR 'username'@'host';

This command displays all privileges granted to the specified user. Proper privilege management helps strengthen database security.

4.2 Checking the Current Connected User

To check information about the currently connected user, use the user() and current_user() functions:

SELECT user(), current_user();

This query returns both the username used for the connection and the actual authenticated username. Usually these are the same, but they can differ in certain cases, such as with anonymous users.

5. Practical Example

Here’s a practical example of displaying the user list in MySQL.

5.1 Steps to Display the User List

  1. Log in to MySQL. For example, using the root user:
    mysql -u root -p
  2. To display the user list, run the following query:
    SELECT Host, User FROM mysql.user;
  3. If you want to include encrypted passwords, use this query:
    SELECT Host, User, Password FROM mysql.user;

5.2 Checking User Privileges

To check a specific user’s privileges, use the SHOW GRANTS statement:

SHOW GRANTS FOR 'tanaka'@'localhost';

This command displays the privileges granted to the user tanaka.

6. Best Practices for MySQL User Management

There are several best practices to keep in mind when managing MySQL users.

6.1 Regularly Review the User List

Regularly check the mysql.user table to make sure there are no unnecessary users or users with improper privileges.

6.2 Use Strong Passwords

Set strong, complex passwords for every user. This reduces the risk of unauthorized access.

6.3 Grant Only the Minimum Necessary Privileges

Assign users only the minimum privileges needed for their tasks. This enhances database security.

7. Conclusion

This article provided a detailed explanation of MySQL user management. Understanding where user information is stored and how to display it is vital for maintaining database security. Knowing how to check user privileges and connection details will help you manage MySQL more efficiently.