Creates a role to which privileges and other roles can be
granted. A role can be granted to a user or another role. For
example, you can create a role for members of a department.
When a user leaves the department, the departmental role can
be revoked from that user and thus exclude that user's access to
the departmental data.
1 – Environment
You can use the CREATE ROLE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m[1m [1;4mROLE[m[1m qqq> <role-name> qqqqqqqqqqqqqqqqqqqqqqk [m [1mlqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqk [m [1m tqq> [1;4mIDENTIFIED[m[1m [1;4mEXTERNALLY[m[1m qqqqqqu x [m [1m mqq> [1;4mNOT[m[1m [1;4mIDENTIFIED[m[1m qqqqqqqqqqqqqj x [m [1mlqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqq> [m [1m mqq> [1;4mCOMMENT[m[1m IS qqwq> 'string' qqwqj [m [1m mqqqqqq / <qqqqj [m
3 – Arguments
3.1 – COMMENT IS 'string'
Adds a comment about the role. SQL displays the text of the
comment when it executes a SHOW ROLES statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
3.2 – IDENTIFIED_EXTERNALLY
The IDENTIFIED EXERNALLY clause indicates that SQL should inherit
the roles defined by the facilities of the operating system,
such as rights identifiers. When a session is started, any role
that is defined externally is established as part of the current
user's profile.
3.3 – NOT_IDENTIFIED
Indicates that the role is used only with the database. The
database must have SECURITY CHECKING IS INTERNAL set before using
this clause.
3.4 – role-name
A user-supplied name that you assign to the role. The special
roles BATCH, DIALUP, INTERACTIVE, LOCAL, NETWORK, and REMOTE are
reserved names that cannot be specified as a role-name.
4 – Examples
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>