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
(B)0[m[1;4mREVOKE[m[1m qqqwqq> <role-name> qwqqqq> [1;4mFROM[m[1m qqwqwqq> <username> qqwqwqqq> [m [1m tqq> [1;4mALL[m[1m [1;4mROLES[m[1m qqqu x tqq> <role-name> qu x [m [1m mqqqqqqq , <qqqqqqj x mqq> [1;4mPUBLIC[m[1m qqqqqqj x [m [1m mqqqqqqqqqq , <qqqqqqqj [m
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;