REVOKE|V4.2.5| docs|Distributed Database

REVOKE

Last Updated：2025-12-04 07:10:03 Updated

Description

This statement allows a system administrator to revoke some privileges from a user.

Limitations and considerations

The user must have the privilege to be revoked (for example, 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 by commas.
obj_clause	The scope of the privilege revocation, in the format of `relation_name`. Privilege scopes are described as follows: Global scope: all databases. Database scope: all objects in a specified database. Table scope: all columns in a specified 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 by 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 the user001 and user002 users.
```
REVOKE SELECT, INSERT ON *.* FROM 'user001', 'user002';
```
relation_name . {* | relation_name}: represents all tables in the specified database. db_name represents 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 all 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 a specified user schema.
CREATE ANY TABLE	The privilege to create a table in any user schema except the SYS schema.
ALTER ANY TABLE	The privilege to modify a table in any user schema except the SYS schema.
BACKUP ANY TABLE	The privilege to back up a table in any user schema except the SYS schema.
DROP ANY TABLE	The privilege to drop a table in any user schema except the SYS schema.
LOCK ANY TABLE	The privilege to lock a table in any user schema except the SYS schema.
COMMENT ANY TABLE	The privilege to comment on a table in any user schema except the SYS schema.
SELECT ANY TABLE	The privilege to view tables in any user schema except the SYS schema.
INSERT ANY TABLE	The privilege to insert rows into tables in any user schema except the SYS schema.
UPDATE ANY TABLE	The privilege to update rows in tables in any user schema except the SYS schema.
DELETE ANY TABLE	The privilege to delete tables in any user schema except the SYS schema.
FLASHBACK ANY TABLE	The privilege to perform the `FLASHBACK` operation on tables in any user schema except the SYS 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 audit objects in any user schema except the SYS 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 schema.
ALTER ANY INDEX	The privilege to modify indexes in any user schema except the SYS schema.
DROP ANY INDEX	The privilege to drop indexes in any user schema except the SYS schema.
CREATE ANY VIEW	The privilege to create views in any user schema except the SYS schema.
DROP ANY VIEW	The privilege to drop views in any user schema except the SYS schema.
CREATE VIEW	The privilege to create a view in a specified user schema.
SELECT ANY DICTIONARY	The privilege to query the `DICTIONARY` view in a user schema other than the SYS schema.
CREATE PROCEDURE	The privilege to create a stored procedure in a specified user schema.
CREATE ANY PROCEDURE	The privilege to create a stored procedure in any user schema except the SYS schema.
ALTER ANY PROCEDURE	The privilege to modify a stored procedure in any user schema except the SYS schema.
DROP ANY PROCEDURE	The privilege to drop a stored procedure in any user schema except the SYS schema.
EXECUTE ANY PROCEDURE	The privilege to execute a stored procedure in any user schema except the SYS schema.
CREATE SYNONYM	The privilege to create a synonym in a specified user schema.
CREATE ANY SYNONYM	The privilege to create a synonym in any user schema except the SYS schema.
DROP ANY SYNONYM	The privilege to drop a synonym in any user schema except the SYS schema.
CREATE PUBLIC SYNONYM	The privilege to create a public synonym.
DROP PUBLIC SYNONYM	The privilege to drop a public synonym.
CREATE SEQUENCE	The privilege to create a sequence in a specified user schema.
CREATE ANY SEQUENCE	The privilege to create a sequence in any user schema except the SYS schema.
ALTER ANY SEQUENCE	The privilege to modify a sequence in any user schema except the SYS schema.
DROP ANY SEQUENCE	The privilege to drop a sequence in any user schema except the SYS schema.
SELECT ANY SEQUENCE	The privilege to query a sequence in any user schema except the SYS schema.
CREATE TRIGGER	The privilege to create a trigger in a specified user schema.
CREATE ANY TRIGGER	The privilege to create a trigger in any user schema except the SYS schema.
ALTER ANY TRIGGER	The privilege to modify a trigger in any user schema except the SYS schema.
DROP ANY TRIGGER	The privilege to drop a trigger in any user schema except the SYS schema.
CREATE PROFILE	The privilege to create a profile.
ALTER PROFILE	The privilege to modify a profile.
DROP PROFILE	The privilege to drop a profile.
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 a type in a specified user schema.
CREATE ANY TYPE	The privilege to create a type in any user schema except the SYS schema.
ALTER ANY TYPE	The privilege to modify a type in any user schema except the SYS schema.
DROP ANY TYPE	The privilege to drop a type in any user schema except the SYS schema.
EXECUTE ANY TYPE	The privilege to execute a type in any user schema except the SYS schema.
UNDER ANY TYPE	The privilege to create a subtype based on a type in any user schema except the SYS schema.
PURGE DBA_RECYCLEBIN	The privilege to drop all objects in the system recycle bin.
CREATE ANY OUTLINE	The privilege to create an outline in any user schema except the SYS schema.
ALTER ANY OUTLINE	The privilege to modify an outline in any user schema except the SYS schema.
DROP ANY OUTLINE	The privilege to drop an outline in any user schema except the SYS 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 a database link in a specified user schema.
CREATE PUBLIC DATABASE LINK	The privilege to create a public database link.
DROP DATABASE LINK	The privilege to drop a database link in a specified user schema.
ALTER SESSION	The privilege to modify a session.
ALTER DATABASE	The privilege to modify a database.

Examples

Create users and roles.

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

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

Revoke privileges from the 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 the user user1.

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

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

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

Revoke the DROP ANY TABLE system privilege from the users 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.