2026年11月12日

MySQL User Privileges: How to Use GRANT and REVOKE

Database Servers

This article is a localized edition of the Japanese original. Read the Japanese original

Introduction

In MySQL database administration, it matters which privileges each user holds. Proper access control keeps the whole system secure and lets administrators protect important information. This article explains how to set user privileges with the GRANT and REVOKE commands.

Symptoms and Background

Typical situations where MySQL users need proper access control:

A user should have read-only access to a specific database or table
You need to create a new user account and give it the privileges it needs
You need to reduce or change the privileges of an existing user
Your system's security policy requires specific access privileges

Steps and Configuration

Step 1: Create a user and grant basic access

# Create a new user. 'username' is the user name and 'password' is the password.
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

# Grant the newly created user read-only access to a specific database.
GRANT SELECT ON database_name.* TO 'username'@'localhost';

Step 2: Finer-grained privileges and allowing remote connections

# Grant the new user full access (SELECT, INSERT, UPDATE, DELETE) to a specific database.
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.* TO 'username'@'localhost';

# Allow the user to connect from a remote machine.
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

Step 3: Set restricted access to a specific table

# Grant the new user read-only access to a specific table in a specific database.
GRANT SELECT ON database_name.table_name TO 'username'@'localhost';

# Give the user INSERT and DELETE privileges on the specified table.
GRANT INSERT, DELETE ON database_name.table_name TO 'username'@'%';

Step 4: Remove or change privileges

# Revoke all privileges from a specific user.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';

# Revoke a more specific privilege (example: SELECT)
REVOKE SELECT ON database_name.* FROM 'username'@'%';

Caveats

When you set real user names and passwords, follow your management tools and security policy.
Before you use GRANT and REVOKE, it is a good idea to check the user's existing privileges. This helps prevent unintended access from being allowed.
Security note: To allow connections from a remote machine you use "%", but this is not the most secure setting. Restricting access to a specific IP address or host name is strongly recommended.
Performance and operations note: User privileges should not be changed frequently. Do a proper initial setup so that few changes are needed afterward.

Summary

1. Use the GRANT command to give a new user the access privileges it needs.

2. Use the REVOKE command to remove privileges an existing user no longer needs.

3. Understand the difference between '%*' and '@localhost' and choose the right connection setting.

4. Be careful not to give users more privileges than they need. Running with the minimum privileges is important for security.

5. Review user privileges and access control lists regularly, and update them in line with your security policy.

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)