REVOKE

REVOKE

Syntax

revoke ::=

revoke_system_privilege ::=

revoke_object_privilege ::=

Prerequisites

The SYS user or the user who originally granted the privilege to be revoked can revoke privileges.

Description

This statement is used to revokes system privileges, object privileges on particular objects or roles from a specified user. This statement can also revoke system privileges or object privileges from roles.

This statement can only revoke system privileges, object privileges and roles that were granted with the GRANT command.

revoke_system_privilege

role

This is used to specify the role to be revoked.

system_privilege

This is used to specify the system privilege(s) to be revoked. Please refer to the description of the GRANT statement for the complete list of system privileges.

ALL PRIVILEGES

This is used to specify that all system privileges that have been granted by the user executing this revoke statement are to be revoked.

System privileges that were granted using the ALL PRIVILEGES clause can be rocked using the ALL PRIVILEGES clause.

FROM user

This is used to identify the user from whom the privilege(s) will be revoked.

FROM role

This is used to specify the role from which system privileges are to be revoked.

FROM PUBLIC

Use the PUBLIC keyword to revoke the privilege(s) from all users.

Note: System privileges granted using the PUBLIC keyword can be revoked using the PUBLIC keyword.

revoke_object_privilege

role

This is used to specify the role to be revoked.

object_privilege

This is used to specify the object privilege that is to be revoked. Please refer to the table in the description of the GRANT statement for more information about object privileges.

ALL [PRIVILEGES]

The ALL PRIVILEGES (or merely ALL) clause is used to revoke all object privileges that have been granted to the user by the user executing this revoke statement.

When revoking privileges using the ALL [PRIVILEGES] clause, all object access privileges granted to the user are revoked. This even includes object privileges that were not granted using the ALL [PRIVILEGES] clause. For example, an object privilege granted to a user in this way:

GRANT SELECT ON object TO user;

can of course be explicitly revoked in this way:

REVOKE SELECT ON object FROM user;

It can also be revoked together with all other privileges in this way:

REVOKE ALL ON object FROM user;

ON object

This is used to specify the object (table, sequence, stored procedure, etc.) for which the permissions are to be revoked.

ON DIRECTORY directory_name

This clause is used to revoke privileges from the specified directory object.

FROM user

This is used to identify the user from whom the privilege(s) will be revoked.

FROM role

This clause is used to identify the user(s) from whom the privilege(s) will be revoked.

FROM PUBLIC

The PUBLIC keyword is used to revoke the privilege(s) from all users.

CASCADE CONSTRAINTS

This clause is relevant only when revoking the REFERENCES privilege or using the ALL [PRIVILEGES] clause. It is used to specify that any related referential integrity constraints are also to be dropped. These were granted either explicitly or implicitly using the ALL [PRIVILEGES] clause.

Example 1: Revoke object privileges

<Query 1> Revoke object privileges.

iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> REVOKE SELECT, DELETE ON sys.employees
  FROM uare7, uare8;
Revoke success.
iSQL> CONNECT uare7/rose7;
Connect success.
iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 15;
[ERR-311B1: The user must have the SELECT_ANY_TABLE privilege(s) to execute this statement.]

After the SELECT and DELETE privileges for the employees table have been revoked, an error message is displayed when an attempt is made to execute a SELECT statement on that table.

Example 2: Revoking System Privileges from a Role

<Query 2> Among the CREATE USER and DROP USER system privileges granted to the role, revoke the CREATE USER privilege.

iSQL> create role alti_role;
Create success.
iSQL> grant create user, drop user to alti_role;
Grant success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> grant alti_role to user01;
Grant success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> drop user user02;
Drop success.
iSQL> connect sys/manager
Connect success.
iSQL> revoke create user from alti_role;
Revoke success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
[ERR-311B1 : The user must have CREATE_USER privilege(s) to execute this statement.]

Example 3: Revoking Object Privileges from a Role

<Query 3> Revoke the DELETE object privilege on table t1 of user01 from the alti_role role.

iSQL> create role alti_role;
Create success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> create user user02 identified by user02;
Create success.

iSQL> connect user01/user01 
Connect success. 
iSQL> create table t1 (i1 integer); 
Create success. 
iSQL> grant select,insert,update,delete on t1 to alti_role; 
Grant success. 

iSQL> connect sys/manager 
Connect success. 
iSQL> grant alti_role to user02; 
Grant success. 

iSQL> connect user02/user02; 
Connect success. 
iSQL> insert into user01.t1 values (1); 
1 row inserted. 
iSQL> insert into user01.t1 values (2); 
1 row inserted. 

iSQL> select * from user01.t1; 
I1           
-------------- 
1            
2            
2 rows selected. 
iSQL> update user01.t1 set i1=3 where i1=1; 
1 row updated. 
iSQL> select * from user01.t1; 
I1           
-------------- 
2            
3            
2 rows selected. 
iSQL> delete from user01.t1 where i1=2; 
1 row deleted. 
iSQL> select * from user01.t1; 
I1           
-------------- 
3           
1 row selected. 

iSQL> connect user01/user01 
Connect success. 
iSQL> revoke delete on t1 from alti_role; 
Revoke success. 

iSQL> connect user02/user02 
Connect success. 
iSQL> delete from user01.t1 where i1=3; 
[ERR-311B1 : The user must have DELETE_ANY_TABLE privilege(s) to execute this statement.]