REVOKE|V4.4.2| docs|Distributed Database

REVOKE

Last Updated：2026-03-06 07:02:42 Updated

Purpose

This statement is used to revoke permissions 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 user user2, revoking privileges from user1 does not also revoke the corresponding privileges from user2.
The user or role from which privileges are to be 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 to be revoked (e.g., user1 must have the SELECT privilege on table t1 to revoke user2's SELECT privilege on t1), and also have the GRANT OPTION privilege.
Revoke all privileges and grant options: 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: When revoking a role, the current user must have the role to be revoked and also have the ADMIN OPTION privilege for the role.

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

Parameter	Description
revoke_stmt1	The SQL statement to revoke specific privilege-level privileges from a user or role.
revoke_stmt2	The SQL statement to revoke all privileges and the grant option from a user or role.
revoke_stmt3	The SQL statement to revoke a role from a user or role.
grant_privileges	The type of privilege to revoke. This can be a list of specific privilege types or all privileges except `GRANT OPTION`.
priv_type_list	The list of specific privilege types. When revoking multiple privileges, separate them with commas (`,`).
priv_type	The specific privilege type. For more information about privilege types, see Privilege types in MySQL-compatible mode. Note: The PROXY parameter is only recorded for syntax in the current version and does not take effect.
column_name_list	Optional. The list of column-level privileges to revoke. If not specified, the privileges for the entire table or object are revoked.
ALL [PRIVILEGES]	All privileges except `GRANT OPTION`.
priv_level	The privilege level. For more information about the specific values, see priv_level.
user_or_role_list	The list of users or roles from which to revoke privileges. When revoking privileges from multiple users or roles, separate them with commas (`,`).
user_name	The name of the user.
role_name	The name of the role.
IF EXISTS	Optional. If the role to be revoked does not exist, a warning is issued instead of an error message when this option is used.
role_name_list	The list of roles to revoke. When revoking multiple roles, separate them with commas (`,`).
IGNORE UNKNOWN USER	Optional. If the user or role to be revoked does not exist, a warning is issued instead of an error message when this option is used.
proxy	The syntax of this keyword is recorded in the current version, but the feature does not take effect.

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 directory in a 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 the test_user001 user and view the privileges granted by the ALL PRIVILEGES option.

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

View the privileges granted to the test_user001 user.

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

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

View the privileges granted to the test_user001 user again.

SHOW GRANTS FOR test_user001;