1  GRANT_ROLES
   Grants a role to a user, another role, or the PUBLIC user.
 

2  Environment
   You can use the GRANT 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

 GRANT --+----> <role-name> --------+---> TO ----+
         +-------- , <--------------+            |
 +-------------------<---------------------------+
 +-------+-+--> <username> ------+--+------------>
         | +--> <role-name> -----+  |
         | +--> PUBLIC ----------+  |
         +--------- , <-------------+

 

2  Arguments
 

3  role-name

   The name of a role previously created with the CREATE ROLE
   statement. If the role name exists as an operating system
   group or rights identifier, then Oracle Rdb will automatically
   create the role when you issue the GRANT statement. A role that
   is created automatically always has the attribute IDENTIFIED
   EXTERNALLY.
 

3  TO

Syntax options:

   TO username | TO role-name | TO PUBLIC

   Specifies the user name, role name, or the PUBLIC user to which
   you want to grant the role. The PUBLIC user is the user name
   associated with all anonymous users who access the database.

   If the user name or role name exists as an operating system user
   or rights identifier, then Oracle Rdb will automatically create
   the user name or role name when you issue the GRANT statement.
   A role or user that is created automatically is IDENTIFIED
   EXTERNALLY.
 

2  Example
   Example 1: Granting and Revoking Roles

   SQL> -- Create three users and two roles. Oracle Rdb automatically
   SQL> -- generates users and 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,
   SQL> -- BGREMBO, and to the 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;