Practical MySQL & MariaDB

How to List MySQL Users: Query mysql.user and Check Privileges

Users and Permissions
目次

1. Overview of MySQL Users

MySQL user management is a critical aspect of database security and operations. Proper user management helps prevent unauthorized access and improves operational efficiency by assigning appropriate privileges. 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 Why MySQL User Management Matters

Because databases store sensitive information, proper user management is essential. By configuring access privileges in detail for each user, you can strengthen security and maintain data integrity.

2. Where MySQL Stores User Information

MySQL user information is stored in the user table inside the mysql database. This table contains various user-related details such as usernames, hostnames, passwords (encrypted), and authentication plugins.

2.1 Structure of the mysql.user Table

The mysql.user table contains many columns, each storing different information related to a user. It includes columns such as:

  • Host: The hostname from which the user is allowed to connect
  • User: The username
  • Password: The encrypted password
  • plugin: The type of authentication plugin
  • Other security-related settings

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

3. How to Get a List of MySQL Users

To retrieve a list of MySQL users, you need to query the mysql.user table. Below, we’ll explain everything from the basic query for listing users to how to display encrypted passwords.

3.1 Basic Query to List Users

First, here is the basic query to retrieve a user list from the mysql.user table.

SELECT Host, User FROM mysql.user;

When you run this query, it displays a list of all users and their corresponding hosts.

3.2 Displaying Encrypted Passwords

To retrieve a list that includes encrypted passwords, use the following query.

SELECT Host, User, Password FROM mysql.user;

This query displays the hostname, username, and encrypted password. However, because the password is encrypted, you cannot determine the actual password from this value.

4. Retrieving Other User Information

In MySQL, you can retrieve additional information about users. In particular, it’s useful to check user privileges and identify the currently connected user.

4.1 Checking User Privileges

To check the privileges assigned to each user, use the SHOW GRANTS statement.

SHOW GRANTS FOR 'username'@'host';

This command displays all privileges granted to the specified user. Properly managing user privileges helps strengthen database security.

4.2 Checking the Currently Connected User

To check information about the current connected user, use the user() and current_user() functions.

SELECT user(), current_user();

This query returns the username specified at connection time and the actual authenticated username. In most cases, they are the same, but they may differ in certain situations such as when using anonymous users.

5. Practical Example

Next, here is a practical example of how to display a list of users in MySQL.

5.1 Steps to Display the User List

  1. Log in to MySQL. In this example, we will use the root user.
    mysql -u root -p
  2. Run the following query to display the user list.
    SELECT Host, User FROM mysql.user;
  3. If you want to display the list including encrypted passwords, use the following query.
    SELECT Host, User, Password FROM mysql.user;

5.2 Checking Privileges

To check the privileges for a specific user, 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

When managing MySQL users, it’s important to follow several best practices.

6.1 Review the User List Regularly

We recommend regularly checking the mysql.user table to ensure there are no unnecessary users and that no users have incorrect privileges assigned.

6.2 Use Strong Passwords

Make sure each user has a strong and complex password. This reduces the risk of unauthorized access.

6.3 Grant the Minimum Required Privileges

Keep user privileges to the minimum necessary and allow only the operations required. This improves overall database security.

7. Summary

In this article, we explained MySQL user management in detail. Understanding where user information is stored and how to list it is important for maintaining database security. In addition, knowing how to check user privileges and connection details helps you manage MySQL more efficiently.