Revoke a role from another user or role.

1  –  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 ------+ |
                                            +---------- , <-------+

3  –  Arguments

3.1  –  ALL_ROLES

    Revokes all roles assigned to the users listed.

3.2  –  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.3  –  role-name

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

4  –  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;