GRANT (exemption) statement

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

>>-GRANT EXEMPTION ON RULE-------------------------------------->

>--+-DB2LBACREADARRAY-----------------+--FOR--policy-name------->
   +-DB2LBACREADSET-------------------+                     
   +-DB2LBACREADTREE------------------+                     
   +-DB2LBACWRITEARRAY--+-WRITEDOWN-+-+                     
   |                    '-WRITEUP---' |                     
   +-DB2LBACWRITESET------------------+                     
   +-DB2LBACWRITETREE-----------------+                     
   '-ALL------------------------------'                     

       .-,-----------------------------.   
       V                               |   
>--TO----+-------+--authorization-name-+-----------------------><
         +-USER--+                         
         +-GROUP-+                         
         '-ROLE--'                         

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