1  REVOKE_ROLES
   Revoke a role from another user or role.
 

2  Environment
   You can use the REVOKE statement for roles:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a nonstored procedure in a nonstored SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 REVOKE ---+--> <role-name> -+----> FROM --+-+--> <username> --+-+--->
           +--> ALL ROLES ---+             | +--> <role-name> -+ |
           +------- , <------+             | +--> PUBLIC ------+ |
                                           +---------- , <-------+

 

2  Arguments
 

3  ALL_ROLES

   Revokes all roles assigned to the users listed.
 

3  FROM

Syntax options:

   FROM username | FROM role-name | FROM PUBLIC

   Specifies the user, role, or the PUBLIC user from which the
   specified role is to be revoked.
 

3  role-name

   The name of an existing role created with the CREATE ROLE
   statement or created automatically by the GRANT statement.
 

2  Example
   Example 1: Granting and Revoking Roles

   SQL> -- Optionally, create three users and two roles.
   SQL> -- Oracle Rdb automatically generates users and
   SQL> -- roles if they are identified externally.
   SQL> CREATE USER ABLOWNEY IDENTIFIED EXTERNALLY;
   SQL> CREATE USER BGREMBO IDENTIFIED EXTERNALLY;
   SQL> CREATE USER LWARD IDENTIFIED EXTERNALLY;
   SQL> CREATE ROLE SALES_MANAGER IDENTIFIED EXTERNALLY;
   SQL> CREATE ROLE DIVISION_MANAGER IDENTIFIED EXTERNALLY;
   SQL> -- Grant the SALES_MANAGER role to users ABLOWNEY and
   SQL> -- BGREMBO. Also grant the SALES_MANAGER role to the
   SQL> -- DIVISION MANAGER ROLE.
   SQL> GRANT SALES_MANAGER TO ABLOWNEY, BGREMBO, DIVISION_MANAGER;
   SQL> -- Grant the DIVISION_MANAGER role to LWARD. LWARD now
   SQL> -- has both the SALES_MANAGER and DIVISION_MANAGER roles.
   SQL> GRANT DIVISION_MANAGER TO LWARD;
   SQL> -- Revoke the DIVISION_MANAGER role from LWARD. He has
   SQL> -- left the company.
   SQL> REVOKE DIVISION_MANAGER FROM LWARD;
   SQL> -- Grant the DIVISION_MANAGER role to BGREMBO.  She
   SQL> -- has been promoted to division manager.
   SQL> GRANT DIVISION_MANAGER TO BGREMBO;