REVOKE|V4.4.2| docs|Distributed Database

REVOKE

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

Purpose

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

Limitations and considerations

The user must have the revoked privileges (for example, if user1 is to revoke the SELECT privilege on table tbl1 from user2, 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 user2, revoking the privileges from user1 does not also revoke the corresponding privileges from user2. If the GRANT OPTION is specified, the revocation cascades.
To revoke the ALL PRIVILEGES and GRANT OPTION privileges, 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-compatible 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
    | LOCATION location_name

Parameters

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

obj_clause

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

Here is an example:

Revoke the global SELECT and INSERT privileges from user 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.

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

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';
```
LOCATION location_name : specifies 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.

Here is an example:

Revoke the READ privilege on the location object local_t from user user005.
```
REVOKE READ ON LOCATION local_t FROM user005;
```

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

Privilege type table

Privilege	Description
ALL PRIVILEGES	All privileges except `GRANT OPTION`.
ALTER	The privilege to execute `ALTER TABLE`.
CREATE	The privilege to execute `CREATE TABLE`.
DELETE	The privilege to execute `DELETE`.
DROP	The privilege to execute `DROP`.
GRANT OPTION	The privilege to execute `GRANT OPTION`.
INSERT	The privilege to execute `INSERT`.
UPDATE	The privilege to execute `UPDATE`.
SELECT	The privilege to execute `SELECT`.
INDEX	The privilege to execute `CREATE INDEX` and `DROP INDEX`.
SHOW VIEW	The privilege to execute `SHOW CREATE VIEW`.
SHOW DATABASES	The global `SHOW DATABASES` privilege.
SUPER	The privilege to execute `SET GLOBAL` to modify global system parameters.
REFERENCES	The privilege to create constraints that reference tables.
EXECUTE	The privilege to execute stored procedures.
FLASHBACK	The privilege to execute `FLASHBACK`.
READ	The privilege to execute `READ`.
WRITE	The privilege to execute `WRITE`.
CREATE SESSION	The privilege to connect to the 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 tables in the specified user schema.
CREATE ANY TABLE	The privilege to create tables in all user schemas except the SYS schema.
ALTER ANY TABLE	The privilege to modify tables in all user schemas except the SYS schema.
BACKUP ANY TABLE	The privilege to create tables in all user schemas except the SYS schema.
DROP ANY TABLE	The privilege to backup tables in all user schemas except the SYS schema.
LOCK ANY TABLE	The privilege to lock tables in all user schemas except the SYS schema.
COMMENT ANY TABLE	The privilege to comment on tables in all user schemas except the SYS schema.
SELECT ANY TABLE	The privilege to view tables in all user schemas except the SYS schema.
INSERT ANY TABLE	The privilege to insert rows into tables in all user schemas except the SYS schema.
UPDATE ANY TABLE	The privilege to update rows in tables in all user schemas except the SYS schema.
DELETE ANY TABLE	The privilege to delete tables in all user schemas except the SYS schema.
FLASHBACK ANY TABLE	The privilege to `FLASHBACK` tables in all user schemas 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 right to modify any role.
AUDIT ANY	The right to modify objects in the schemas of all users except the SYS user.
GRANT ANY PRIVILEGE	The right to grant any system privilege.
GRANT ANY OBJECT PRIVILEGE	The right to grant any object privilege.
CREATE ANY INDEX	The right to create indexes in the schemas of all users except the SYS user.
ALTER ANY INDEX	The right to modify indexes in the schemas of all users except the SYS user.
DROP ANY INDEX	The right to drop indexes in the schemas of all users except the SYS user.
CREATE ANY VIEW	The right to create views in the schemas of all users except the SYS user.
DROP ANY VIEW	The right to drop views in the schemas of all users except the SYS user.
CREATE VIEW	The right to create views in the specified user schema.
SELECT ANY DICTIONARY	The right to query `DICTIONARY` in the specified user schema.
CREATE PROCEDURE	The right to create `PROCEDURE` in the specified user schema.
CREATE ANY PROCEDURE	The right to create `PROCEDURE` in the schemas of all users except the SYS user.
ALTER ANY PROCEDURE	The right to modify `PROCEDURE` in the schemas of all users except the SYS user.
DROP ANY PROCEDURE	The right to drop `PROCEDURE` in the schemas of all users except the SYS user.
EXECUTE ANY PROCEDURE	The right to execute `PROCEDURE` in the schemas of all users except the SYS user.
CREATE SYNONYM	The right to create `SYNONYM` in the specified user schema.
CREATE ANY SYNONYM	The right to create `SYNONYM` in the schemas of all users except the SYS user.
DROP ANY SYNONYM	The right to drop `SYNONYM` in the schemas of all users except the SYS user.
CREATE PUBLIC SYNONYM	The right to create a public `SYNONYM`.
DROP PUBLIC SYNONYM	The right to drop a public `SYNONYM`.
CREATE SEQUENCE	The right to create `SEQUENCE` in the specified user schema.
CREATE ANY SEQUENCE	The right to create `SEQUENCE` in the schemas of all users except the SYS user.
ALTER ANY SEQUENCE	The right to modify `SEQUENCE` in the schemas of all users except the SYS user.
DROP ANY SEQUENCE	The right to drop `SEQUENCE` in the schemas of all users except the SYS user.
SELECT ANY SEQUENCE	The right to query `SEQUENCE` in the schemas of all users except the SYS user.
CREATE TRIGGER	The right to create `TRIGGER` in the specified user schema.
CREATE ANY TRIGGER	The right to create `TRIGGER` in the schemas of all users except the SYS user.
ALTER ANY TRIGGER	The right to modify `TRIGGER` in the schemas of all users except the SYS user.
DROP ANY TRIGGER	The right to drop `TRIGGER` in the schemas of all users except the SYS user.
CREATE PROFILE	The right to create `PROFILE`.
ALTER PROFILE	The right to modify `PROFILE`.
DROP PROFILE	The right to drop `PROFILE`.
CREATE USER	The right to create a user.
ALTER USER	The right to modify a user.
DROP USER	The right to drop a user.
CREATE TYPE	The right to create `TYPE` in the specified user schema.
CREATE ANY TYPE	The right to create `TYPE` in the schemas of all users except the SYS user.
ALTER ANY TYPE	The right to modify `TYPE` in the schemas of all users except the SYS user.
DROP ANY TYPE	The right to drop `TYPE` in the schemas of all users except the SYS user.
EXECUTE ANY TYPE	The right to execute `TYPE` in the schemas of all users except the SYS user.
UNDER ANY TYPE	The right to create `SUBTYPE` based on `TYPE` in the schemas 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 schemas of all users except the SYS user.
ALTER ANY OUTLINE	The right to modify `OUTLINE` in the schemas of all users except the SYS user.
DROP ANY OUTLINE	The right to delete `OUTLINE` in the schemas 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 privileges on tables from the role.
```
REVOKE SELECT ON user1.tbl1 FROM role1;
```

The preceding steps show how to grant and revoke privileges.

The following examples show how to revoke all privileges, revoke object privileges, and revoke system privileges.

Revoke all privileges from user1.

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

Revoke the SELECT object privilege on user1.tbl1 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 information about how to view user permissions, see View user permissions.
For information about how to view roles and the permissions in roles, see View roles.
You can view the information about the created users from the dba_users table. For more information about the dba_users table, see DBA_USERS.