REVOKE|V4.2.5| docs|Distributed Database

REVOKE

Last Updated：2026-02-11 07:43:39 Updated

Purpose

This statement is used by system administrators to revoke certain privileges from users.

Limitations and considerations

The user must have the revoked privilege (for example, if user1 is to revoke the SELECT privilege on table tbl1 from user2, then user1 must have the SELECT privilege on table tbl1) and the GRANT OPTION privilege.

Privilege requirements

If the GRANT OPTION is not specified, the revocation does not cascade. For example, if user user1 grants some privileges to user user2, revoking privileges from user1 does not also revoke the corresponding privileges from user2. If the GRANT OPTION is specified, the revocation cascades.
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.

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

Syntax

/*Revoke object privileges*/

REVOKE {obj_privilege [, obj_privilege...]} ON obj_clause FROM {user_name [, user_name ...]}

/*Revoke system privileges*/
REVOKE {system_privilege_list | ALL PRIVILEGES} FROM user_name [, user_name ...];

REVOKE ALL [PRIVILEGES], GRANT_OPTION FROM user_name [, user_name ...];

/*Revoke roles*/
REVOKE role_name [, role_name ...] FROM user_name;

obj_clause:
      * [. *]
    | relation_name . {*  | relation_name}
    | [DIRECTORY] relation_name

Parameters

Parameter	Description
obj_privilege	Specifies the type of object privilege to revoke. For more information about the privilege types, see the table below. When revoking multiple privileges, separate the privilege types with commas.
obj_clause	Specifies the privilege level to revoke. `relation_name` specifies the name of the specific object. Privileges can be categorized as follows: Global level: applies to all databases. Database level: applies to all targets within a specified database. Table level: applies to all columns within a specified table. For more information about the privilege levels, see obj_clause.
system_privilege_list	Specifies the system privilege to revoke. When revoking multiple privileges, separate the privilege types with commas.
ALL [PRIVILEGES]	Specifies all privileges except `GRANT OPTION`.

obj_clause

* [. *] : specifies 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';
```
relation_name . {* | relation_name} : specifies all tables in the specified database. db_name specifies 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';
```
[DIRECTORY] relation_name : specifies 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';
```

The following table lists the types of privileges that can be revoked.

Privilege type description table

Privilege	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	Privilege to execute `ALTER TABLE`.
CREATE	Privilege to execute `CREATE TABLE`.
DELETE	Privilege to execute `DELETE`.
DROP	Privilege to execute `DROP`.
GRANT OPTION	Privilege to execute `GRANT OPTION`.
INSERT	Privilege to execute `INSERT`.
UPDATE	Privilege to execute `UPDATE`.
SELECT	Privilege to execute `SELECT`.
INDEX	Privilege to execute `CREATE INDEX` and `DROP INDEX`.
SHOW VIEW	Privilege to execute `SHOW CREATE VIEW`.
SHOW DATABASES	Global privilege to execute `SHOW DATABASES`.
SUPER	Privilege to modify global system parameters by using `SET GLOBAL`.
REFERENCES	Privilege to create constraints that point to tables.
EXECUTE	Privilege to execute stored procedures.
FLASHBACK	Privilege to execute `FLASHBACK`.
READ	Privilege to execute `READ`.
WRITE	Privilege to execute `WRITE`.
CREATE SESSION	Privilege to connect to the database.
EXEMPT REDACTION POLICY	Privilege to bypass any existing `REDACTION POLICY` and view data.
SYSDBA	Privilege to execute `SYSDBA`.
SYSOPER	Privilege to execute `SYSOPER`.
SYSBACKUP	Privilege to execute `SYSBACKUP`.
CREATE TABLE	Privilege to create tables in the specified user schema.
CREATE ANY TABLE	Privilege to create tables in all user schemas except the SYS schema.
ALTER ANY TABLE	Privilege to modify tables in all user schemas except the SYS schema.
BACKUP ANY TABLE	Privilege to create tables in all user schemas except the SYS schema.
DROP ANY TABLE	Privilege to backup tables in all user schemas except the SYS schema.
LOCK ANY TABLE	Privilege to lock tables in all user schemas except the SYS schema.
COMMENT ANY TABLE	Privilege to comment on tables in all user schemas except the SYS schema.
SELECT ANY TABLE	Privilege to view tables in all user schemas except the SYS schema.
INSERT ANY TABLE	Privilege to insert rows into tables in all user schemas except the SYS schema.
UPDATE ANY TABLE	Privilege to update rows in tables in all user schemas except the SYS schema.
DELETE ANY TABLE	Privilege to delete tables in all user schemas except the SYS schema.
FLASHBACK ANY TABLE	Privilege to `FLASHBACK` tables in all user schemas except the SYS schema.
CREATE ROLE	Privilege to create roles.
DROP ANY ROLE	Privilege to drop any role.
GRANT ANY ROLE	Privilege to grant any role.
ALTER ANY ROLE	Grants the right to alter any role.
AUDIT ANY	Grants the right to audit any object in any schema except the SYS schema.
GRANT ANY PRIVILEGE	Grants the right to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	Grants the right to grant any object privilege.
CREATE ANY INDEX	Grants the right to create any index in any schema except the SYS schema.
ALTER ANY INDEX	Grants the right to alter any index in any schema except the SYS schema.
DROP ANY INDEX	Grants the right to drop any index in any schema except the SYS schema.
CREATE ANY VIEW	Grants the right to create any view in any schema except the SYS schema.
DROP ANY VIEW	Grants the right to drop any view in any schema except the SYS schema.
CREATE VIEW	Grants the right to create a view in the specified schema.
SELECT ANY DICTIONARY	Grants the right to query the `DICTIONARY` object in the specified schema.
CREATE PROCEDURE	Grants the right to create a `PROCEDURE` object in the specified schema.
CREATE ANY PROCEDURE	Grants the right to create a `PROCEDURE` object in any schema except the SYS schema.
ALTER ANY PROCEDURE	Grants the right to alter a `PROCEDURE` object in any schema except the SYS schema.
DROP ANY PROCEDURE	Grants the right to drop a `PROCEDURE` object in any schema except the SYS schema.
EXECUTE ANY PROCEDURE	Grants the right to execute a `PROCEDURE` object in any schema except the SYS schema.
CREATE SYNONYM	Grants the right to create a `SYNONYM` object in the specified schema.
CREATE ANY SYNONYM	Grants the right to create a `SYNONYM` object in any schema except the SYS schema.
DROP ANY SYNONYM	Grants the right to drop a `SYNONYM` object in any schema except the SYS schema.
CREATE PUBLIC SYNONYM	Grants the right to create a public `SYNONYM` object.
DROP PUBLIC SYNONYM	Grants the right to drop a public `SYNONYM` object.
CREATE SEQUENCE	Grants the right to create a `SEQUENCE` object in the specified schema.
CREATE ANY SEQUENCE	Grants the right to create a `SEQUENCE` object in any schema except the SYS schema.
ALTER ANY SEQUENCE	Grants the right to alter a `SEQUENCE` object in any schema except the SYS schema.
DROP ANY SEQUENCE	Grants the right to drop a `SEQUENCE` object in any schema except the SYS schema.
SELECT ANY SEQUENCE	Grants the right to query a `SEQUENCE` object in any schema except the SYS schema.
CREATE TRIGGER	Grants the right to create a `TRIGGER` object in the specified schema.
CREATE ANY TRIGGER	Grants the right to create a `TRIGGER` object in any schema except the SYS schema.
ALTER ANY TRIGGER	Grants the right to alter a `TRIGGER` object in any schema except the SYS schema.
DROP ANY TRIGGER	Grants the right to drop a `TRIGGER` object in any schema except the SYS schema.
CREATE PROFILE	Grants the right to create a `PROFILE` object.
ALTER PROFILE	Grants the right to alter a `PROFILE` object.
DROP PROFILE	Grants the right to drop a `PROFILE` object.
CREATE USER	Grants the right to create a user.
ALTER USER	Grants the right to alter a user.
DROP USER	Grants the right to drop a user.
CREATE TYPE	Grants the right to create a `TYPE` object in the specified schema.
CREATE ANY TYPE	Grants the right to create a `TYPE` object in any schema except the SYS schema.
ALTER ANY TYPE	Grants the right to alter a `TYPE` object in any schema except the SYS schema.
DROP ANY TYPE	Grants the right to drop a `TYPE` object in any schema except the SYS schema.
EXECUTE ANY TYPE	The right to execute `TYPE` in the schema of all users except the SYS user.
UNDER ANY TYPE	The right to create `SUBTYPE` based on `TYPE` in the schema of all users except the SYS user.
PURGE DBA_RECYCLEBIN	The right to delete all objects from the system recycle bin.
CREATE ANY OUTLINE	The right to create `OUTLINE` in the schema of all users except the SYS user.
ALTER ANY OUTLINE	The right to modify `OUTLINE` in the schema of all users except the SYS user.
DROP ANY OUTLINE	The right to delete `OUTLINE` in the schema of all users except the SYS user.
SYSKM	The right to `SYSKM`.
CREATE TABLESPACE	The right to create a tablespace.
ALTER TABLESPACE	The right to modify a tablespace.
DROP TABLESPACE	The right to drop a tablespace.
ALTER SYSTEM	The right to `ALTER SYSTEM`.
CREATE DATABASE LINK	The right to create a `DATABASE LINK` in the specified user schema.
CREATE PUBLIC DATABASE LINK	The right to create a `PUBLIC DATABASE LINK`.
DROP DATABASE LINK	The right to drop a `DATABASE LINK` in the specified user schema.
ALTER SESSION	The right to modify a `SESSION`.
ALTER DATABASE	The right to modify a `DATABASE`.

Examples

Create a user and a role.

CREATE USER user1 IDENTIFIED BY password;
CREATE ROLE role1;
CREATE TABLE user1.tbl1 (id INT);

Grant privileges to the role.
```
GRANT SELECT ON user1.tbl1 TO role1;
```
Grant the role to the user.
```
GRANT role1 TO user1;
```
Revoke the role's privileges on the table.
```
REVOKE SELECT ON user1.tbl1 FROM role1;
```

The preceding steps complete the granting and revoking of privileges.

The following examples show the SQL statements for revoking all privileges, revoking object privileges, and revoking system privileges.

Revoke all privileges from user user1.

obclient> REVOKE ALL PRIVILEGES FROM user1;
Query OK, 0 rows affected

Revoke the SELECT object privilege on table user1.tbl1 from role role1.

obclient> REVOKE SELECT ON user1.tbl1 FROM role1;
Query OK, 0 rows affected

Revoke the DROP ANY TABLE system privilege from users user1 and user2.

obclient> REVOKE DROP ANY TABLE FROM user1,user2;
Query OK, 0 rows affected

References

For information about how to view user privileges, see View user privileges.
For information about how to view roles and the privileges of roles, see View roles.
You can query the dba_users view to obtain information about the created users. For more information about the dba_users view, see DBA_USERS.