The World of MySQL & MariaDB

MySQL User Permissions: Simple Guide & Best Practices

Users and Permissions
目次

1. What are MySQL user privileges

MySQL user privileges are settings that control what actions each user can perform within the database. Proper privilege configuration helps prevent unauthorized access and unintended operations.

Common Types of Privileges

  • SELECT: Allows reading data.
  • INSERT: Allows adding data.
  • UPDATE: Allows modifying existing data.
  • DELETE: Allows deleting data.
  • CREATE: Allows creating new databases or tables.
  • DROP: Allows dropping existing databases or tables.
  • ALL PRIVILEGES: Grants all privileges, including the above, to the user.
It is important to configure privileges properly so that each user can perform only the necessary actions, thereby strengthening database security.

2. How to Check User Privileges

In MySQL, you can check user privileges with specific commands.

Checking the Current User

To see the username used in the current session, use the following command.
SELECT USER();
Also, to view the authenticated username and host, use this command.
SELECT CURRENT_USER();

Checking a Specific User’s Privileges

To see the privileges granted to a specific user, use the following command.
SHOW GRANTS FOR 'username'@'hostname';
For example, to check the privileges of example_user:
SHOW GRANTS FOR 'example_user'@'localhost';
This command displays all privileges granted to that user.

Listing All Users

To view all users and host information in the database, use the following command.
SELECT user, host FROM mysql.user;
This command retrieves usernames and hostnames from the user table in the mysql database and displays a list of all users.

3. Granting and Revoking User Privileges

In MySQL, you can grant or revoke privileges for users. Proper privilege management can improve security.

Granting Privileges

To grant privileges to a new user, use the GRANT statement.
GRANT privilege_name ON database_name.* TO 'user_name'@'host_name';
For example, to grant the SELECT privilege on all tables of example_db to example_user:
GRANT SELECT ON example_db.* TO 'example_user'@'localhost';

Granting Multiple Privileges at Once

You can also grant multiple privileges simultaneously.
GRANT SELECT, INSERT, UPDATE ON example_db.* TO 'example_user'@'localhost';
In this way, you can efficiently grant multiple privileges with a single command.

Revoking Privileges

To remove unnecessary privileges, use the REVOKE statement.
REVOKE privilege_name ON database_name.* FROM 'user_name'@'host_name';
For example, to revoke the INSERT privilege on example_db from example_user:
REVOKE INSERT ON example_db.* FROM 'example_user'@'localhost';
To revoke all privileges, use ALL PRIVILEGES as follows.
REVOKE ALL PRIVILEGES ON example_db.* FROM 'example_user'@'localhost';

4. Best Practices for Permission Management

Effective permission management enhances database security and operational efficiency. Below are the best practices.

Least Privilege Principle

By granting users only the minimal permissions necessary to perform their duties, you reduce the risk of mistakes and unauthorized access.

Regular Review of Permissions

Regularly review permissions in line with changes in user roles and responsibilities, and promptly remove any unnecessary permissions.

Implementing Role-Based Access Control (RBAC)

Create user groups (roles) with common permission sets and assign roles to users, making permission management simple and efficient.

Utilizing Audit Logs

By recording user activity as audit logs and reviewing them regularly, you can detect unauthorized actions or abnormal behavior early.

5. Frequently Asked Questions (FAQ)

Q1: How do I verify whether a specific user can access multiple databases?

SHOW GRANTS FOR 'username'@'hostname'; By using this, you can list all privileges that have been granted to that user for every database.

Q2: How can I grant or revoke multiple privileges at once?

GRANT and REVOKE statements can be used, separating privileges with commas to operate on multiple privileges at once. For example, you can write GRANT SELECT, INSERT, UPDATE ON example_db.* TO 'username'@'localhost';.

Q3: How do I restore privileges that were accidentally revoked?

If privileges have been revoked, you need to re‑grant them using the GRANT command. It is recommended to note the revoked privileges or keep a backup of the configuration before changes.

Q4: Is it possible to grant privileges only on a specific table?

Yes. By using the format GRANT privilege_name ON database_name.table_name TO 'username'@'hostname';, you can grant privileges only on that specific table.

6. Summary

MySQL user privilege management has a major impact on database security and operational safety. Proper privilege management helps improve data safety and efficiency.

Key Points of Privilege Management

    • Follow the principle of least privilege
    • Conduct regular reviews
>Streamline privilege management with role‑based access control
  • Leverage audit logs to detect and monitor abnormal activities
  By following these best practices, aim for safe and efficient operation of your MySQL database.