Example 1: Creating a Role

    SQL> ALTER DATABASE FILENAME 'mf_personnel.rdb'
    cont> SECURITY CHECKING IS INTERNAL;
    SQL> ATTACH 'FILENAME mf_personnel.rdb';
    SQL> CREATE ROLE WRITER;
    SQL> SHOW ROLES;
    Roles in database with filename mf_personnel.rdb
         WRITER

    Example 2: Creating Roles and Granting Privileges to Those Roles

    SQL> ALTER DATABASE FILENAME mf_personnel.rdb
    cont> SECURITY CHECKING IS INTERNAL;
    SQL> -- Create a role for employees in the payroll department
    SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
    SQL> CREATE ROLE PAYROLL
    cont> COMMENT IS 'This role allows access to various tables'
    cont> /          'and procedures for use by the PAYROLL dept.';
    SQL> -- Create another role for a subset of employees.
    SQL> CREATE ROLE ANNUAL_LEAVE
    cont> COMMENT IS 'This role is granted to PAYROLL personnel'
    cont> /          'who adjust the annual leave data';
    SQL> -- Grant EXECUTE privilege on module and ALL privilege on table
    SQL> -- SALARY_HISTORY to all employees to whom the PAYROLL role has
    SQL> -- been granted.  Grant EXECUTE privilege on module LEAVE_ADJUSTMENT
    SQL> --  only to those employees who have been granted both the PAYROLL
    SQL> -- and ANNUAL_LEAVE roles.
    SQL> GRANT EXECUTE ON MODULE PAYROLL_UTILITIES TO PAYROLL;
    SQL> GRANT ALL ON TABLE SALARY_HISTORY TO PAYROLL;
    SQL> GRANT EXECUTE ON MODULE LEAVE_ADJUSTMENT
    cont> to PAYROLL, ANNUAL_LEAVE;
    SQL> -- User STUART joins the personnel department.  Grant him
    SQL> -- the PAYROLL and ANNUAL_LEAVE roles so that he can
    SQL> -- perform all functions in the payroll department.
    SQL> CREATE USER STUART
    cont> IDENTIFIED EXTERNALLY
    SQL> GRANT PAYROLL, ANNUAL_LEAVE TO STUART;
    SQL> -- User STUART is promoted to supervisor and thus
    SQL> -- no longer needs access to the objects controlled by
    SQL> -- the ANNUAL_LEAVE role.  Revoke that role from user
    SQL> -- STUART.
    SQL> REVOKE ANNUAL_LEAVE FROM STUART;

    Example 3: Creating roles explictly using CREATE ROLE and
    implicitly using GRANT

    This examples demonstrates creating roles that match an OpenVMS
    rights identifiers. The CREATE ROLE statement is used first,
    and then the GRANT statement. GRANT issues a warning message to
    alert the database administrator of the side-effect of the GRANT
    statement.

    SQL> create database
    cont>   filename SAMPLE
    cont>   security checking is internal;
    SQL> show roles;
    Roles in database with filename sample
     No Roles found
    SQL> create role dba_mgr identified externally;
    SQL> show roles;
    Roles in database with filename sample
         DBA_MGR
    SQL> grant saldb_user to smith;
    %RDB-W-META_WARN, metadata successfully updated with the reported warning
    -RDMS-W-PRFCREATED, some users or roles were created
    SQL> show roles;
    Roles in database with filename sample
         DBA_MGR
         SALDB_USER
    SQL>