REVOKE|V4.3.5| docs|Distributed Database

REVOKE

Last Updated：2025-11-14 07:33:32 Updated

Purpose

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

Limitations and considerations

An object privilege revocation operation does not cascade. For example, if user user1 grants some privileges to user user2, revoking the privileges from user1 does not revoke the corresponding privileges from user2.
The user or role to be revoked must exist. Otherwise, an error is returned.
Roles in the mandatory_roles system variable cannot be revoked.
The REVOKE statement takes effect immediately for the current user.

Privilege requirements

Revoke privileges at a specific privilege level: The user must have the privileges to be revoked (for example, user1 must have the SELECT privilege on table t1 to revoke the SELECT privilege on table t1 from user2), and must have the GRANT OPTION privilege.
Revoke all privileges and grant options: To revoke all privileges and grant options, the user must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the privilege table.
Revoke privileges from a user or role: To revoke a role from a user or role, the user must have the revoked role and the ADMIN OPTION privilege.

For more information about privileges in OceanBase Database, see Privilege types 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

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

Parameter	Description
revoke_stmt1	An SQL statement to revoke privileges at a specific privilege level from a user or role.
revoke_stmt2	An SQL statement to revoke all privileges and grant options from a user or role.
revoke_stmt3	An SQL statement to revoke a role from a user or role.
grant_privileges	The type of privileges to be revoked. It can be a list of specific privilege types or all privileges (excluding `GRANT OPTION`).
priv_type_list	A list of specific privilege types. Separate multiple privileges with commas.
priv_type	The specific type of privilege. For more information about the different privilege types, see Privileges in MySQL mode. Note: In the current version, the PROXY parameter is only supported as a keyword in the syntax and does not take effect.
column_name_list	Optional. A list of columns from which to revoke column-level privileges. If this parameter is not specified, privileges on the entire table or object are revoked.
ALL [PRIVILEGES]	All privileges except `GRANT OPTION`.
priv_level	The privilege level. For more information, see priv_level.
user_or_role_list	A list of users or roles from which to revoke privileges. Separate multiple users and roles with commas.
user_name	The name of a user.
role_name	The name of a role.
IF EXISTS	If this option is specified, a warning is returned when the revoked role does not exist. Otherwise, an error is returned.
role_name_list	A list of roles from which to revoke privileges. Separate multiple roles with commas.
IGNORE UNKNOWN USER	If this option is specified, a warning is returned when the grantee (user or role) does not exist. Otherwise, an error is returned.
proxy	In the current version, this keyword is only supported in the syntax and does not take effect.

priv_level

*.*: all tables in all databases.

Here is an example:

Revoke the SELECT and INSERT privileges from users user001 and user002.
```
REVOKE SELECT, INSERT ON *.* FROM user001, user002;
```
db_name.*: all tables in a specified database. db_name is the name of the specified database.

Here is an 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 specified database.

Here is an 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: the directory in the catalog.

Here is an example:

Revoke the USE CATALOG privilege on the Catalog object test_odps_catalog from the user user004.
```
REVOKE USE CATALOG ON CATALOG test_odps_catalog FROM user004;
```

Examples

Example 1: Revoke object privileges

Revoke the SELECT privilege from user test_user001 and view the privileges granted to ALL PRIVILEGES.

Grant the ALL PRIVILEGES privilege to user test_user001.
```
GRANT ALL PRIVILEGES ON *.* TO test_user001;
```

View the privileges granted to user test_user001.

SHOW GRANTS FOR test_user001;

The return result is as follows:

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

Revoke the SELECT privilege from user test_user001.
```
REVOKE SELECT ON *.* FROM test_user001;
```

View the privileges granted to user test_user001 again.

SHOW GRANTS FOR test_user001;