All Products
Search
Document Center

REVOKE

Last Updated: Jun 18, 2021

Description

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

The following usage description is provided:

  • A user must have the permission to be revoked. For example, if user1 needs to revoke the SELECT permission on table t1 from user2, user1 must have the SELECT permission on table t1. The user must also have the GRANT OPTION permission.

  • When the ALL PRIVILEGES and GRANT OPTION permissions are revoked, the current user must have the global GRANT OPTION permission or the UPDATE and DELETE permissions on the permission list.

  • If the GRANT OPTION permission is unavailable, cascading is not performed for the revocation operation For example, user1 grants some permissions to user2. When the permissions are revoked from user1, the permissions are not revoked from user2. If the GRANT OPTION permission is available, cascading is performed for the revocation operation

Syntax

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

user_list:
  user [, user ...]

obj_privileges:
  obj_privilege [, obj_privilege ...]
  
obj_privilege:
    ALTER
  | AUDIT
  | COMMENT
  | DELETE
  | GRANT
  | INDEX
  | INSERT
  | LOCK
  | RENAME
  | SELECT 
  | UPDATE
  | REFERENCES
  | EXECUTE
  | CREATE
  | FLASHBACK
  | READ
  | WRITE
  | DEBUG

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

relation_name:
  STR_VALUE
  
/*Revoke system permissions*/
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 [, role ...]

Parameter description

Parameter

Description

obj_privileges

Specifies the type of the object permission to be revoked. For more information about permission types and description, see the following table for permission type description.

If you revoke multiple permissions at a time, separate the permission types with commas (,).

system_privilege

Specifies the type of the system permission to be revoked.

If you revoke multiple permissions at a time, separate the permission types with commas (,).

obj_clause

Specifies the level of the permission to be revoked. relation_name specifies the name of the specific object. Permissions can be divided into the following levels:

  • Global level: The permissions apply to all the databases.

  • Database level: The permissions apply to all the objects in a specified database.

  • Table level: The permissions apply to all the columns in a specified table.

The following table describes the types of permissions that can be revoked.

Table for permission type description

Permission

Description

ALL PRIVILEGES

All the permissions except the GRANT OPTION permission.

ALTER

The ALTER TABLE permission.

CREATE

The CREATE TABLE permission.

DELETE

The DELETE permission.

DROP

The DROP permission.

GRANT OPTION

The GRANT OPTION permission.

INSERT

The INSERT permission.

UPDATE

The UPDATE permission.

SELECT

The SELECT permission.

INDEX

The CREATE INDEX and DROP INDEX permissions.

SHOW VIEW

The SHOW CREATE VIEW permission.

SHOW DATABASES

The global SHOW DATABASES permission.

SUPER

The permission to execute the SET GLOBAL statement to modify global system parameters.

REFERENCES

The permission to create a constraint that refers to the table.

EXECUTE

The permission to execute the preprocessor program.

FLASHBACK

The FLASHBACK permission.

READ

The READ permission.

WRITE

The WRITE permission.

CREATE SESSION

The permission to connect to the database.

EXEMPT REDACTION POLICY

The permission to bypass existing redaction policies and view data.

SYSDBA

The SYSDBA permission.

SYSOPER

The SYSOPER permission.

SYSBACKUP

The SYSBACKUP permission.

CREATE TABLE

The permission to create a table in the specified user schema.

CREATE ANY TABLE

The permission to create tables in all the user schemas except SYS.

ALTER ANY TABLE

The permission to modify tables in all the user schemas except SYS.

BACKUP ANY TABLE

The permission to create tables in all the user schemas except SYS.

DROP ANY TABLE

The permission to back up tables in all the user schemas except SYS.

LOCK ANY TABLE

The permission to lock tables in all the user schemas except SYS.

COMMENT ANY TABLE

The permission to comment tables in all the user schemas except SYS.

SELECT ANY TABLE

The permission to view tables in all the user schemas except SYS.

INSERT ANY TABLE

The permission to insert rows into tables in all the user schemas except SYS.

UPDATE ANY TABLE

The permission to update rows in tables in all the user schemas except SYS.

DELETE ANY TABLE

The permission to delete tables in all the user schemas except SYS.

FLASHBACK ANY TABLE

The permission to flash back tables in all the user schemas except SYS.

CREATE ROLE

The permission to create a role.

DROP ANY ROLE

The permission to delete a role.

GRANT ANY ROLE

The permission to grant a role.

ALTER ANY ROLE

The permission to modify a role.

AUDIT ANY

The permission to modify objects in all the user schemas except SYS.

GRANT ANY PRIVILEGE

The permission to grant a system permission.

GRANT ANY OBJECT PRIVILEGE

The permission to grant an object permission.

CREATE ANY INDEX

The permission to create indexes in all the user schemas except SYS.

ALTER ANY INDEX

The permission to modify indexes in all the user schemas except SYS.

DROP ANY INDEX

The permission to delete indexes in all the user schemas except SYS.

CREATE ANY VIEW

The permission to create views in all the user schemas except SYS.

DROP ANY VIEW

The permission to delete indexes in all the user schemas except SYS.

CREATE VIEW

The permission to create a view in the specified user schema.

SELECT ANY DICTIONARY

The permission to query a dictionary in the specified user schema.

CREATE PROCEDURE

The permission to create a procedure in the specified user schema.

CREATE ANY PROCEDURE

The permission to create procedures in all the user schemas except SYS.

ALTER ANY PROCEDURE

The permission to modify procedures in all the user schemas except SYS.

DROP ANY PROCEDURE

The permission to delete procedures in all the user schemas except SYS.

EXECUTE ANY PROCEDURE

The permission to perform procedures in all the user schemas except SYS.

CREATE SYNONYM

The permission to create a synonym in the specified user schema.

CREATE ANY SYNONYM

The permission to create synonyms in all the user schemas except SYS.

DROP ANY SYNONYM

The permission to delete synonyms in all the user schemas except SYS.

CREATE PUBLIC SYNONYM

The permission to create a public synonym.

DROP PUBLIC SYNONYM

The permission to delete a public synonym.

CREATE SEQUENCE

The permission to create a sequence in the specified user schema.

CREATE ANY SEQUENCE

The permission to create sequences in all the user schemas except SYS.

ALTER ANY SEQUENCE

The permission to modify sequences in all the user schemas except SYS.

DROP ANY SEQUENCE

The permission to delete sequences in all the user schemas except SYS.

SELECT ANY SEQUENCE

The permission to query sequences in all the user schemas except SYS.

CREATE TRIGGER

The permission to create a trigger in the specified user schema.

CREATE ANY TRIGGER

The permission to create triggers in all the user schemas except SYS.

ALTER ANY TRIGGER

The permission to modify triggers in all the user schemas except SYS.

DROP ANY TRIGGER

The permission to delete triggers in all the user schemas except SYS.

CREATE PROFILE

The permission to create a profile.

ALTER PROFILE

The permission to modify a profile.

DROP PROFILE

The permission to delete a profile.

CREATE USER

The permission to create a user.

ALTER USER

The permission to modify a user.

DROP USER

The permission to delete a user.

CREATE TYPE

The permission to create a type in the specified user schema.

CREATE ANY TYPE

The permission to create types in all the user schemas except SYS.

ALTER ANY TYPE

The permission to modify types in all the user schemas except SYS.

DROP ANY TYPE

The permission to delete types in all the user schemas except SYS.

EXECUTE ANY TYPE

The permission to execute types in all the user schemas except SYS.

UNDER ANY TYPE

The permission to create subtypes on the basis of the types in all the user schemas except SYS.

PURGE DBA_RECYCLEBIN

The permission to delete all the objects from the system recycle bin.

CREATE ANY OUTLINE

The permission to create outlines in all the user schemas except SYS.

ALTER ANY OUTLINE

The permission to modify outlines in all the user schemas except SYS.

DROP ANY OUTLINE

The permission to delete outlines in all the user schemas except SYS.

SYSKM

The SYSKM permission.

CREATE TABLESPACE

The permission to create a tablespace.

ALTER TABLESPACE

The permission to modify a tablespace.

DROP TABLESPACE

The permission to delete a tablespace.

ALTER SYSTEM

The ALTER SYSTEM permission.

CREATE DATABASE LINK

The permission to create a database link in the specified user schema.

CREATE PUBLIC DATABASE LINK

The permission to create a public database link.

DROP DATABASE LINK

The permission to delete a database link in the specified user schema.

ALTER SESSION

The permission to modify a session.

ALTER DATABASE

The permission to modify a database.

Examples

Run the following command to revoke all the permissions from the obsqluser01 user:

OceanBase(admin@TEST)>REVOKE ALL PRIVILEGES FROM sqluser;
Query OK, 0 rows affected (0.10 sec)