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
(B)0[m[1;4mGRANT[m[1m qqwqqqq> <role-name> qqqqqqqqwqqq> [1;4mTO[m[1m qqqqk [m [1m mqqqqqqqq , <qqqqqqqqqqqqqqj x [m [1mlqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqqqqqqqwqwqq> <username> qqqqqqwqqwqqqqqqqqqqqq> [m [1m x tqq> <role-name> qqqqqu x [m [1m x mqq> [1;4mPUBLIC[m[1m qqqqqqqqqqj x [m [1m mqqqqqqqqq , <qqqqqqqqqqqqqj [m
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;