REVOKE

Purpose

This statement is used to revoke privileges previously granted to a user or role, or to revoke a role from a user or role.

Limitations and considerations

• Revoking object privileges does not cascade. For example, if user user1 grants certain privileges to user2, revoking user1's privileges will not also revoke the corresponding privileges from user2.
• The user or role whose privileges are being revoked must exist; otherwise, an error will be returned.
• Roles specified in the system variable mandatory_roles cannot be revoked.
• The execution of the REVOKE statement takes effect immediately for the currently logged-in user.

Privilege requirements

• Revoke specific privilege level privileges: The user must have the privilege being revoked (e.g., user1 must have the SELECT privilege on table t1 to revoke user2's SELECT privilege on t1), and must also have the GRANT OPTION privilege.

• Revoke all privileges and grant option: When revoking ALL PRIVILEGES and GRANT OPTION, the current user must have the global GRANT OPTION privilege, or the UPDATE and DELETE privileges on the privilege table.

• Revoke role privileges from a user or role: To successfully revoke a role, the current user must have the role being revoked and the ADMIN OPTION privilege.

For more information about privileges in OceanBase Database, see Privilege classification in MySQL mode.

Syntax

Revoke object privileges from a user or role

revoke_stmt1:
    REVOKE grant_privileges ON priv_level FROM user_or_role_list;

revoke_stmt2:
    REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user_or_role_list;

revoke_stmt3:
    REVOKE [IF EXISTS] role_name_list FROM user_or_role_list [IGNORE UNKNOWN USER];

grant_privileges:
    priv_type_list
    | ALL [PRIVILEGES]

priv_type_list:
    priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]

column_name_list:
    column_name [, column_name ...]

priv_level:
    *.*
    | db_name.*
    | db_name.table_name
    | CATALOG external_catalog_name
    | LOCATION location_name

user_or_role_list:
      user_or_role [, user_or_role ...]

user_or_role:
    user_name | role_name

role_name_list:
    role_name [, role_name ...]

Parameters

priv_level

• *.*: All tables in all databases.

  Example:

  Revoke the global SELECT and INSERT privileges from users user001 and user002.

  REVOKE SELECT, INSERT ON *.* FROM user001, user002;
  

• db_name.*: All tables in the specified database. db_name is the name of the specified database.

  Example:

  Revoke the insert privilege on all tables in the test database from user user003.

  REVOKE INSERT ON test.* FROM user003;
  

• db_name.table_name: A specific table in a specific database.

  Example:

  Revoke the SELECT privilege on the tbl2 table in the test database from user user003.

  REVOKE SELECT ON test.tbl2 FROM user003;
  

• CATALOG external_catalog_name: A catalog in the specified catalog.

  Example:

  Revoke the USE CATALOG privilege on the test_odps_catalog catalog from user user004.

  REVOKE USE CATALOG ON CATALOG test_odps_catalog FROM user004;
  

• LOCATION location_name: A location object.

  Note

  For OceanBase Database V4.4.x, the REVOKE statement supports the LOCATION parameter and the corresponding READ and WRITE privileges starting from V4.4.1.

  Example:

  Revoke the READ privilege on the test_location_local location from user user005.

  REVOKE READ ON LOCATION test_location_local FROM user005;
  

Examples

Example 1: Revoke object privileges

Revoke the SELECT privilege from user test_user001 and check which privileges are granted by ALL PRIVILEGES.

1. Grant the ALL PRIVILEGES privilege to user test_user001.

   GRANT ALL PRIVILEGES ON *.* TO test_user001;
   

2. View the privileges granted to user test_user001.

   SHOW GRANTS FOR test_user001;
   

   The returned result is as follows:

   +-----------------------------------------------+
   | Grants for test_user001@%                     |
   +-----------------------------------------------+
   | GRANT ALL PRIVILEGES ON *.* TO 'test_user001' |
   +-----------------------------------------------+
   1 row in set
   

3. Revoke the SELECT privilege from user test_user001.

   REVOKE SELECT ON *.* FROM test_user001;
   

4. View the privileges granted to user test_user001 again.

   SHOW GRANTS FOR test_user001;
   

   The returned result is as follows:

   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Grants for test_user001@%                                                                                                                                                                                                                                                                                                                                                                                                                                       |
   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | GRANT ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, INDEX, CREATE VIEW, SHOW VIEW, SHOW DATABASES, SUPER, PROCESS, CREATE_SYNONYM, REFERENCES, FILE, ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT, REPLICATION SLAVE, REPLICATION CLIENT, DROP DATABASE LINK, CREATE DATABASE LINK, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TABLESPACE, SHUTDOWN, RELOAD, CREATE ROLE, DROP ROLE, TRIGGER ON *.* TO 'test_user001' |
   +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Example 2: Revoke all privileges from a user

Revoke all privileges from user test_user002.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM test_user002;

Example 3: Revoke a role from a user or role

• Revoke role role001 from role role002.

  REVOKE role001 FROM role002;
  

• Revoke role role001 from user user001.

  REVOKE role001 FROM user001;
  

References

• Revoke privileges from a role
• Revoke a role from a user or role
• Modify user privileges
• GRANT