GRANT (role) statement
This form of the GRANT statement grants roles to users, groups, or to other roles.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- The WITH ADMIN OPTION on the role
- SECADM authority
Syntax
Description
- ROLE role-name,...
- Identifies one or more roles to be granted. Each role-name must identify an existing role at the current server (SQLSTATE 42704).
- TO
- Specifies to whom the role is granted.
- USER
- Specifies that the authorization-name identifies a user.
- GROUP
- Specifies that the authorization-name identifies a group.
- ROLE
- Specifies that the authorization-name identifies an existing role at the current server (SQLSTATE 42704).
- authorization-name,...
- Lists the authorization IDs of one or more users, groups, or roles. The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).
- PUBLIC
- Grants the specified roles to a set of users (authorization IDs).
- WITH ADMIN OPTION
- Allows the specified authorization-name to grant or revoke the role-name to or from others, or to associate a comment with the role. It does not allow the specified authorization-name to drop the role.
Rules
- For each authorization-name specified, if
none of the keywords USER, GROUP, or ROLE is specified:
- If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE in the database and as either GROUP or USER in the operating system, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as both USER and GROUP according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as USER only according to the security plug-in in effect, or if it is undefined, USER is assumed.
- If the authorization-name is defined as GROUP only according to the security plug-in in effect, GROUP is assumed.
- If the authorization-name is defined in the database as ROLE only, ROLE is assumed.
- Hierarchies of roles can be built by granting one role to another role. However, cycles are not allowed (SQLSTATE 428GF). For example, if role R1 is granted to another role R2, then role R2 (or some other role Rn that contains R2) cannot be granted back to R1, because this would produce a cycle.
- The USER, GROUP, or ROLE names must not begin with the characters 'SYS' and must not be 'ACCESSCTRL', 'DATAACCESS', 'DBADM', 'NONE', 'NULL', 'PUBLIC', 'SECADM', 'SQLADM', 'SCHEMAADM', or 'WLMADM' (SQLSTATE 42939).
Notes
- When role R1 is granted to another role R2, then R2 contains R1.
- DBADM authority cannot be granted to PUBLIC. Therefore:
- Granting role R1 to PUBLIC fails (SQLSTATE 42508) if role R1 holds
DBADM authority either directly or indirectly.
- Role R1 holds DBADM authority directly if the following statement
has been issued:
GRANT DBADM ON DATABASE TO ROLE R1
- Role R1 holds DBADM authority indirectly if the following statements
have been issued:
GRANT DBADM ON DATABASE TO ROLE R2 GRANT ROLE R2 TO ROLE R1
- Role R1 holds DBADM authority directly if the following statement
has been issued:
- Granting role R1, which holds DBADM authority, to role R2 fails
(SQLSTATE 42508) if role R2 is granted to PUBLIC either directly or
indirectly.
- Role R2 is granted to PUBLIC directly if the following statement
has been issued:
GRANT ROLE R2 TO PUBLIC
- Role R2 is granted to PUBLIC indirectly if the following statements
have been issued:
GRANT ROLE R2 TO ROLE R3 GRANT ROLE R3 TO PUBLIC
- Role R2 is granted to PUBLIC directly if the following statement
has been issued:
- Granting role R1 to PUBLIC fails (SQLSTATE 42508) if role R1 holds
DBADM authority either directly or indirectly.
- Privileges granted to a group: A privilege that
is granted to a group is not used for authorization checking on:
- Static DML statements in a package
- A base table while processing a CREATE VIEW statement
- A base table while processing a CREATE TABLE statement for a materialized query table
- Create SQL routine
- Create trigger
Examples
- Example 1: Grant role INTERN to role DOCTOR and role DOCTOR
to role SPECIALIST.
GRANT ROLE INTERN TO ROLE DOCTOR GRANT ROLE DOCTOR TO ROLE SPECIALIST
- Example 2: Grant role INTERN to PUBLIC.
GRANT ROLE INTERN TO PUBLIC
- Example 3: Grant role SPECIALIST to user BOB and group
TORONTO.
GRANT ROLE SPECIALIST TO USER BOB, GROUP TORONTO