ข้ามไปยังเนื้อหาหลัก

MySQL REVOKE Statement

The `REVOKE` statement in MySQL is used to remove previously granted privileges from a user account. It is an essential part of managing database security, allowing administrators to control access and permissions.

Usage

The `REVOKE` statement is employed when you need to withdraw permissions from a user or role in a database. It helps in maintaining security by ensuring users have only the necessary access for their roles.

sql
REVOKE privilege_type [(column_list)]
ON [object_type] privilege_level
FROM user_account [, user_account] ...;

In this syntax:

  • privilege_type: Specifies the type of privilege to be revoked.
  • column_list: An optional list of columns for which the privilege is being revoked, applicable when dealing with specific column-level privileges.
  • object_type: An optional specification indicating the type of database object, such as TABLE or FUNCTION, from which the privilege is revoked.
  • privilege_level: Defines the scope of the privilege being revoked, such as a specific database or table.
  • user_account: Identifies the user account from which the privilege is being removed.

Examples

1. Basic Revoke Privilege

sql
REVOKE SELECT ON database_name.table_name FROM 'username'@'host';

This example revokes the `SELECT` permission from the specified user for a particular table.

2. Revoke Multiple Privileges

sql
REVOKE INSERT, DELETE ON database_name.* FROM 'username'@'host';

This statement removes both `INSERT` and `DELETE` privileges for the specified user across all tables in a specified database.

3. Revoke All Privileges

sql
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

This command revokes all privileges and the ability to grant them from a user, effectively removing all access rights.

Tips and Best Practices

  • Review granted privileges regularly. Regularly audit user privileges to ensure they are aligned with current security policies.
  • Be specific. Specify only the exact privileges you wish to revoke to avoid inadvertently removing necessary permissions.
  • Limit user access. Revoke unnecessary privileges to minimize security risks and ensure users have only the permissions they need.
  • Test changes. After revoking privileges, verify that the user's access aligns with expectations to prevent disruptions.
  • Common pitfalls. Ensure the correct user and host are specified to avoid errors.
  • Transaction handling. Note that `REVOKE` statements are not transactional, meaning changes take effect immediately and cannot be rolled back as part of a transaction.