REVOKE|V4.3.1| docs|Distributed Database

REVOKE

Last Updated：2025-12-04 08:27:57 Updated

Description

This statement is used by a system administrator to revoke some privileges from a user.

Limitations and considerations

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

Privilege requirements

When a privilege does not have the GRANT OPTION, revocation does not cascade. For example, if user user1 grants a privilege to user2, revocation of the privilege from user1 does not affect the corresponding privilege of user2. If a privilege includes the GRANT OPTION, revocation does cascade.
To revoke the ALL PRIVILEGES or GRANT OPTION privilege, the current user must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the privilege table.

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

Parameter explanation

Parameter	Description
obj_privilege	The type of object privilege to revoke. For more information, see the table below. If multiple object privileges are revoked, they must be separated with commas.
obj_clause	The level at which the privilege is revoked. The `relation_name` parameter specifies the name of the specific object. Privilege levels are as follows: Global level: applicable to all databases. Database level: applicable to all objects in a specific database. Table level: applicable to all columns in a specific table. For more information, see obj_clause.
system_privilege_list	The types of system privileges to revoke. If multiple system privileges are revoked, they must be separated with commas.
ALL [PRIVILEGES]	All privileges except `GRANT OPTION`.

obj_clause

* [. *]: represents all tables in all databases.

Here is an example:

Revoke the global SELECT and INSERT privileges from users user001 and user002.
```
REVOKE SELECT, INSERT ON *.* FROM 'user001', 'user002';
```
relation_name . {* | relation_name}: represents all tables in the specified database. db_name indicates the name of the specified database.

Here is an example:

Revoke the INSERT privilege on all tables in the test database from the user003 user.
```
REVOKE INSERT ON 'test'.* FROM 'user003';
```
[DIRECTORY] relation_name: represents a specific table in a specific database.

Here is an example:

Revoke the SELECT privilege on the tbl2 table in the test database from the user003 user.
```
REVOKE SELECT ON 'test'.'tbl2' FROM 'user003';
```

You can revoke the following privileges.

Privilege types

Privilege	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	The `ALTER TABLE` privilege.
CREATE	The `CREATE TABLE` privilege.
DELETE	The `DELETE` privilege.
DROP	The `DROP` privilege.
GRANT OPTION	The `GRANT OPTION` privilege.
INSERT	The `INSERT` privilege.
UPDATE	The `UPDATE` privilege.
SELECT	The `SELECT` privilege.
INDEX	The `CREATE INDEX` and `DROP INDEX` privileges.
SHOW VIEW	The `SHOW CREATE VIEW` privilege.
SHOW DATABASES	The global `SHOW DATABASES` privilege.
SUPER	The `SET GLOBAL` privilege to modify global system parameters.
REFERENCES	The privilege to create constraints that reference tables.
EXECUTE	The privilege to execute stored procedures.
FLASHBACK	The `FLASHBACK` privilege.
READ	The `READ` privilege.
WRITE	The `WRITE` privilege.
CREATE SESSION	The privilege to connect to a database.
EXEMPT REDACTION POLICY	The privilege to bypass any existing `REDACTION POLICY` and view data.
SYSDBA	The `SYSDBA` privilege.
SYSOPER	The `SYSOPER` privilege.
SYSBACKUP	The `SYSBACKUP` privilege.
CREATE TABLE	The privilege to create a table in the specified user schema.
CREATE ANY TABLE	The privilege to create a table in any user schema except the SYS user schema.
ALTER ANY TABLE	The privilege to modify a table in any user schema except the SYS user schema.
BACKUP ANY TABLE	The privilege to back up a table in any user schema except the SYS user schema.
DROP ANY TABLE	The privilege to drop a table in any user schema except the SYS user schema.
LOCK ANY TABLE	The privilege to lock a table in any user schema except the SYS user schema.
COMMENT ANY TABLE	The privilege to comment on a table in any user schema except the SYS user schema.
SELECT ANY TABLE	The privilege to view tables in any user schema except the SYS user schema.
INSERT ANY TABLE	The privilege to insert rows into tables in any user schema except the SYS user schema.
UPDATE ANY TABLE	The privilege to update rows in tables in any user schema except the SYS user schema.
DELETE ANY TABLE	The privilege to delete tables in any user schema except the SYS user schema.
FLASHBACK ANY TABLE	The privilege to perform the `FLASHBACK` operation on tables in any user schema except the SYS user schema.
CREATE ROLE	The privilege to create roles.
DROP ANY ROLE	The privilege to drop any role.
GRANT ANY ROLE	The privilege to grant any role.
ALTER ANY ROLE	The privilege to modify any role.
AUDIT ANY	The privilege to modify the privileges and attributes of objects in any user schema except the SYS user schema.
GRANT ANY PRIVILEGE	The privilege to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	The privilege to grant any object privilege.
CREATE ANY INDEX	The privilege to create indexes in any user schema except the SYS user schema.
ALTER ANY INDEX	The privilege to modify indexes in any user schema except the SYS user schema.
DROP ANY INDEX	The privilege to drop indexes in any user schema except the SYS user schema.
CREATE ANY VIEW	The privilege to create views in any user schema except the SYS user schema.
DROP ANY VIEW	The privilege to drop views in any user schema except the SYS user schema.
CREATE VIEW	The privilege to create a view in the specified user schema.
SELECT ANY DICTIONARY	The privilege to query the `DICTIONARY` view in any user schema except the SYS user schema.
CREATE PROCEDURE	The privilege to create stored procedures in the specified user schema.
CREATE ANY PROCEDURE	The privilege to create stored procedures in any user schema except the SYS user schema.
ALTER ANY PROCEDURE	The privilege to modify stored procedures in any user schema except the SYS user schema.
DROP ANY PROCEDURE	The privilege to drop stored procedures in any user schema except the SYS user schema.
EXECUTE ANY PROCEDURE	The privilege to execute stored procedures in any user schema except the SYS user schema.
CREATE SYNONYM	The privilege to create synonyms in the specified user schema.
CREATE ANY SYNONYM	The privilege to create synonyms in any user schema except the SYS user schema.
DROP ANY SYNONYM	The privilege to drop synonyms in any user schema except the SYS user schema.
CREATE PUBLIC SYNONYM	The privilege to create public synonyms.
DROP PUBLIC SYNONYM	The privilege to drop public synonyms.
CREATE SEQUENCE	The privilege to create sequences in the specified user schema.
CREATE ANY SEQUENCE	The privilege to create sequences in any user schema except the SYS user schema.
ALTER ANY SEQUENCE	The privilege to modify sequences in any user schema except the SYS user schema.
DROP ANY SEQUENCE	The privilege to drop sequences in any user schema except the SYS user schema.
SELECT ANY SEQUENCE	The privilege to query sequences in any user schema except the SYS user schema.
CREATE TRIGGER	The privilege to create triggers in the specified user schema.
CREATE ANY TRIGGER	The privilege to create triggers in any user schema except the SYS user schema.
ALTER ANY TRIGGER	The privilege to modify triggers in any user schema except the SYS user schema.
DROP ANY TRIGGER	The privilege to drop triggers in any user schema except the SYS user schema.
CREATE PROFILE	The privilege to create profiles.
ALTER PROFILE	The privilege to modify profiles.
DROP PROFILE	The privilege to drop profiles.
CREATE USER	The privilege to create users.
ALTER USER	The privilege to modify users.
DROP USER	The privilege to drop users.
CREATE TYPE	The privilege to create types in the specified user schema.
CREATE ANY TYPE	The privilege to create types in any user schema except the SYS user schema.
ALTER ANY TYPE	The privilege to modify types in any user schema except the SYS user schema.
DROP ANY TYPE	The privilege to drop types in any user schema except the SYS user schema.
EXECUTE ANY TYPE	The privilege to execute types in any user schema except the SYS user schema.
UNDER ANY TYPE	The privilege to create subtypes based on types in any user schema except the SYS user schema.
PURGE DBA_RECYCLEBIN	The privilege to drop all objects in the system recycle bin.
CREATE ANY OUTLINE	The privilege to create outlines in any user schema except the SYS user schema.
ALTER ANY OUTLINE	The privilege to modify outlines in any user schema except the SYS user schema.
DROP ANY OUTLINE	The privilege to drop outlines in any user schema except the SYS user schema.
SYSKM	The `SYSKM` privilege.
CREATE TABLESPACE	The privilege to create tablespaces.
ALTER TABLESPACE	The privilege to modify tablespaces.
DROP TABLESPACE	The privilege to drop tablespaces.
ALTER SYSTEM	The `ALTER SYSTEM` privilege.
CREATE DATABASE LINK	The privilege to create database links in the specified user schema.
CREATE PUBLIC DATABASE LINK	The privilege to create public database links.
DROP DATABASE LINK	The privilege to drop database links in the specified user schema.
ALTER SESSION	The privilege to modify sessions.
ALTER DATABASE	The privilege to modify databases.

Examples

Create users and roles.

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

Grant privileges to roles.
```
GRANT SELECT ON user1.tbl1 TO role1;
```
Grant roles to users.
```
GRANT role1 TO user1;
```

Revoke privileges from a table.

REVOKE SELECT ON user1.tbl1 FROM role1;

You can perform both privilege granting and revocation by following the preceding steps.

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

Revoke all privileges from user1.

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

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

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

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

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

References

For more information about how to view user privileges, see View user privileges.
For more information about how to view roles and the privileges in the roles, see View roles.
For more information about the users created, query the dba_users table. For more information about the dba_users table, see DBA_USERS.