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)
