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

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

3  –  Arguments

3.1  –  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.2  –  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.

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