REVOKE|V4.3.1| docs|Distributed Database

REVOKE

Last Updated：2024-06-28 05:30:31 Updated

Purpose

You can use this statement as the system administrator to revoke privileges from a user.

Take note of the following considerations:

You must have the privileges to be revoked and the GRANT OPTION privilege. For example, if user1 wants to revoke the SELECT privilege on the tbl1 table from user2, user1 must have the SELECT privilege on tbl1.
To revoke the ALL PRIVILEGES and GRANT OPTION privileges, you must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the table.
If GRANT OPTION was not specified when privileges were granted, then the privilege revocation takes effect only on the current user. For example, if user1 has granted some privileges to user2, when you revoke the privileges from user1, the privileges will not be granted from user2. If GRANT OPTION was specified when privileges were granted, the privilege revocation extends to other users to which the current user has granted privileges.

Syntax

/*Revoke object privileges*/
REVOKE obj_privileges
  ON obj_clause FROM user_list;

user_list:
  user_name [, user_name ...]

obj_privileges:
  obj_privilege [, obj_privilege ...]

obj_privilege:
    ALTER
  | DELETE
  | INDEX
  | INSERT
  | SELECT
  | UPDATE
  | REFERENCES
  | EXECUTE

obj_clause:
  relation_name
  | relation_name '.' relation_name
  | DIRECTORY relation_name

relation_name:
  STR_VALUE

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

system_privilege_list:
  system_privilege [, system_privilege ...]

system_privilege:
  CREATE SESSION
  | EXEMPT REDACTION POLICY
  | SYSDBA
  | SYSOPER
  | SYSBACKUP
  | CREATE TABLE
  | CREATE ANY TABLE
  | ALTER ANY TABLE
  | BACKUP ANY TABLE
  | DROP ANY TABLE
  | LOCK ANY TABLE
  | COMMENT ANY TABLE
  | SELECT ANY TABLE
  | INSERT ANY TABLE
  | UPDATE ANY TABLE
  | DELETE ANY TABLE
  | FLASHBACK ANY TABLE
  | CREATE ROLE
  | DROP ANY ROLE
  | GRANT ANY ROLE
  | ALTER ANY ROLE
  | AUDIT ANY
  | GRANT ANY PRIVILEGE
  | GRANT ANY OBJECT PRIVILEGE
  | CREATE ANY INDEX
  | ALTER ANY INDEX
  | DROP ANY INDEX
  | CREATE ANY VIEW
  | DROP ANY VIEW
  | CREATE VIEW
  | SELECT ANY DICTIONARY
  | CREATE PROCEDURE
  | CREATE ANY PROCEDURE
  | ALTER ANY PROCEDURE
  | DROP ANY PROCEDURE
  | EXECUTE ANY PROCEDURE
  | CREATE SYNONYM
  | CREATE ANY SYNONYM
  | DROP ANY SYNONYM
  | CREATE PUBLIC SYNONYM
  | DROP PUBLIC SYNONYM
  | CREATE SEQUENCE
  | CREATE ANY SEQUENCE
  | ALTER ANY SEQUENCE
  | DROP ANY SEQUENCE
  | SELECT ANY SEQUENCE
  | CREATE TRIGGER
  | CREATE ANY TRIGGER
  | ALTER ANY TRIGGER
  | DROP ANY TRIGGER
  | CREATE PROFILE
  | ALTER PROFILE
  | DROP PROFILE
  | CREATE USER
  | ALTER USER
  | DROP USER
  | CREATE TYPE
  | CREATE ANY TYPE
  | ALTER ANY TYPE
  | DROP ANY TYPE
  | EXECUTE ANY TYPE
  | UNDER ANY TYPE
  | PURGE DBA_RECYCLEBIN
  | CREATE ANY OUTLINE
  | ALTER ANY OUTLINE
  | DROP ANY OUTLINE
  | SYSKM
  | CREATE TABLESPACE
  | ALTER TABLESPACE
  | DROP TABLESPACE
  | SHOW PROCESS
  | ALTER SYSTEM
  | CREATE DATABASE LINK
  | CREATE PUBLIC DATABASE LINK
  | DROP DATABASE LINK
  | ALTER SESSION
  | ALTER DATABASE

/*Revoke roles*/
REVOKE role_list FROM user;

role_list:
role_name [, role_name ...]

Parameters

Parameter	Description
obj_privileges	The type of the object privilege to be revoked. For information about the specific privilege types and their description, see the following table. To revoke multiple types of privileges, separate them with commas (,).
system_privilege	The type of the system privilege to be revoked. To revoke multiple types of privileges, separate them with commas (,).
obj_clause	The level of the privilege to be revoked. `relation_name` specifies the name of the object. Privileges can be divided into the following levels: Global: Privileges at this level apply to all databases. Database level: Privileges at this level apply to all objects in a specified database. Table level: Privileges at this level apply to all columns in a specified table.
role_list	The role list.

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

Privilege types

Privilege type	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 for modifying global system parameters.
REFERENCES	The privilege to create constraints on a table.
EXECUTE	The privilege to execute the precompiler program.
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 policies and to view data.
SYSDBA	The `SYSDBA` privilege.
SYSOPER	The `SYSOPER` privilege.
SYSBACKUP	The `SYSBACKUP` privilege.
CREATE TABLE	The privilege to create a table in the schema of a specified user.
CREATE ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
ALTER ANY TABLE	The privilege to modify a table in the schema of any user except the SYS user.
BACKUP ANY TABLE	The privilege to create a table in the schema of any user except the SYS user.
DROP ANY TABLE	The privilege to back up a table in the schema of any user except the SYS user.
LOCK ANY TABLE	The privilege to lock a table in the schema of any user except the SYS user.
COMMENT ANY TABLE	The privilege to comment on a table in the schema of any user except the SYS user.
SELECT ANY TABLE	The privilege to view a table in the schema of any user except the SYS user.
INSERT ANY TABLE	The privilege to insert rows into a table in the schema of any user except the SYS user.
UPDATE ANY TABLE	The privilege to update rows of a table in the schema of any user except the SYS user.
DELETE ANY TABLE	The privilege to drop a table from the schema of any user except the SYS user.
FLASHBACK ANY TABLE	The privilege to flash back a table in the schema of any user except the SYS user.
CREATE ROLE	The privilege to create a role.
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 the schema of any user except the SYS user.
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 an index in the schema of any user except the SYS user.
ALTER ANY INDEX	The privilege to modify an index in the schema of any user except the SYS user.
DROP ANY INDEX	The privilege to drop an index from the schema of any user except the SYS user.
CREATE ANY VIEW	The privilege to create a view in the schema of any user except the SYS user.
DROP ANY VIEW	The privilege to drop an index from the schema of any user except the SYS user.
CREATE VIEW	The privilege to create a view in the schema of a specified user.
SELECT ANY DICTIONARY	The privilege to query a dictionary in the schema of a specified user.
CREATE PROCEDURE	The privilege to create a procedure in the schema of a specified user.
CREATE ANY PROCEDURE	The privilege to create a procedure in the schema of any user except the SYS user.
ALTER ANY PROCEDURE	The privilege to modify a procedure in the schema of any user except the SYS user.
DROP ANY PROCEDURE	The privilege to drop a procedure from the schema of any user except the SYS user.
EXECUTE ANY PROCEDURE	The privilege to execute a procedure in the schema of any user except the SYS user.
CREATE SYNONYM	The privilege to create a synonym in the schema of a specified user.
CREATE ANY SYNONYM	The privilege to create a synonym in the schema of any user except the SYS user.
DROP ANY SYNONYM	The privilege to drop a synonym from the schema of any user except the SYS user.
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 the schema of a specified user.
CREATE ANY SEQUENCE	The privilege to create a sequence in the schema of any user except the SYS user.
ALTER ANY SEQUENCE	The privilege to modify a sequence in the schema of any user except the SYS user.
DROP ANY SEQUENCE	The privilege to drop a sequence from the schema of any user except the SYS user.
SELECT ANY SEQUENCE	The privilege to query a sequence in the schema of any user except the SYS user.
CREATE TRIGGER	The privilege to create a trigger in the schema of a specified user.
CREATE ANY TRIGGER	The privilege to create a trigger in the schema of any user except the SYS user.
ALTER ANY TRIGGER	The privilege to modify a trigger in the schema of any user except the SYS user.
DROP ANY TRIGGER	The privilege to drop a trigger in the schema of any user except the SYS user.
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 a user.
ALTER USER	The privilege to modify a user.
DROP USER	The privilege to drop a user.
CREATE TYPE	The privilege to create a type in the schema of a specified user.
CREATE ANY TYPE	The privilege to create a type in the schema of any user except the SYS user.
ALTER ANY TYPE	The privilege to modify a type in the schema of any user except the SYS user.
DROP ANY TYPE	The privilege to drop a type from the schema of any user except the SYS user.
EXECUTE ANY TYPE	The privilege to execute a type in the schema of any user except the SYS user.
UNDER ANY TYPE	The privilege to create subtypes under a type in the schema of any user except the SYS user.
PURGE DBA_RECYCLEBIN	The privilege to purge all objects from the recycle bin.
CREATE ANY OUTLINE	The privilege to create an outline in the schema of any user except the SYS user.
ALTER ANY OUTLINE	The privilege to modify an outline in the schema of any user except the SYS user.
DROP ANY OUTLINE	The privilege to drop an outline from the schema of any user except the SYS user.
SYSKM	The `SYSKM` privilege.
CREATE TABLESPACE	The privilege to create a tablespace.
ALTER TABLESPACE	The privilege to modify a tablespace.
DROP TABLESPACE	The privilege to drop a tablespace.
ALTER SYSTEM	The `ALTER SYSTEM` privilege.
CREATE DATABASE LINK	The privilege to create a DBLink in the schema of a specified user.
CREATE PUBLIC DATABASE LINK	The privilege to create a public DBLink.
DROP DATABASE LINK	The privilege to drop a DBLink from the schema of a specified user.
ALTER SESSION	The privilege to modify a session.
ALTER DATABASE	The privilege to modify a database.

Examples

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 privilege from user1 and user2.

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