GRANT (exemption) statement

This form of the GRANT statement grants to a user, group, or role an exemption on an access rule for a specified label-based access control (LBAC) security policy.

When the user holding the exemption accesses data in a table protected by that security policy the indicated rule will not be enforced when deciding if they can access the data.

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 SECADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramGRANT EXEMPTION ON RULEDB2LBACREADARRAYDB2LBACREADSETDB2LBACREADTREEDB2LBACWRITEARRAYWRITEDOWNWRITEUPDB2LBACWRITESETDB2LBACWRITETREEALLFORpolicy-nameTO ,USERGROUPROLEauthorization-name

Description

EXEMPTION ON RULE
Grants an exemption on an access rule.
DB2LBACREADARRAY
Grants an exemption on the predefined DB2LBACREADARRAY rule.
DB2LBACREADSET
Grants an exemption on the predefined DB2LBACREADSET rule.
DB2LBACREADTREE
Grants an exemption on the predefined DB2LBACREADTREE rule.
DB2LBACWRITEARRAY
Grants an exemption on the predefined DB2LBACWRITEARRAY rule.
WRITEDOWN
Specifies that the exemption only applies to write down.
WRITEUP
Specifies that the exemption only applies to write up.
DB2LBACWRITESET
Grants an exemption on the predefined DB2LBACWRITESET rule.
DB2LBACWRITETREE
Grants an exemption on the predefined DB2LBACWRITETREE rule.
ALL
Grants an exemption on all of the predefined rules.
FOR policy-name
Identifies the security policy for which the exemption is being granted. The exemption will only be effective for tables that are protected by this security policy. The name must identify a security policy already described in the catalog (SQLSTATE 42704).
TO
Specifies to whom the exemption is granted.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
authorization-name
Lists the authorization IDs of one or more users, groups, or roles.

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • 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 according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.
  • If the security policy is not defined to consider access through groups or roles, any exemption granted to a group or role is ignored when access is attempted.

Notes

  • By default when a security policy is created, only exemptions granted to an individual user are considered. To have groups or roles considered for the security policy, you must issue the ALTER SECURITY POLICY statement and specify USE GROUP AUTHORIZATION or USE ROLE AUTHORIZATION as applicable.

Examples

  • Example 1: Grant an exemption on access rule DB2LBACREADSET for security policy DATA_ACCESS to user WALID. 
       GRANT EXEMPTION ON RULE DB2LBACREADSET FOR DATA_ACCESS TO USER WALID
  • Example 2: Grant an exemption on access rule DB2LBACWRITEARRAY with the WRITEDOWN option for security policy DATA_ACCESS to user BOBBY. 
       GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN
     FOR DATA_ACCESS TO USER BOBBY
  • Example 3: Grant an exemption on access rule DB2LBACWRITEARRAY with the WRITEUP option for security policy DATA_ACCESS to user BOBBY. 
       GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEUP
     FOR DATA_ACCESS TO USER BOBBY