Creating row permissions

A row permission specifies the conditions under which users can access a row. With the SECADM authority, you can use the CREATE PERMISSION statement to create a row permission.

To create a row permission:

1. Issue the CREATE TABLE statement to create table HOSPITAL.PATIENT.

   The HOSPITAL.PATIENT table contains columns for recording a patient's social security number (SSN), account authorization ID (USERID), name (NAME), address (ADDRESS), pharmacy (PHARMACY), account balance (ACCT_BALANCE), and doctor (PCP_ID), as shown below:

   

   CREATE TABLE HOSPITAL.PATIENT (
          SSN CHAR(11),
          USERID VARCHAR(18),
          NAME VARCHAR(128),
          ADDRESS VARCHAR(128),
          PHARMACY VARCHAR(5000),
          ACCT_BALANCE DECIMAL(12,2) WITH DEFAULT,
          PCP_ID VARCHAR(18));

   
2. Issue the CREATE ROLE statements to create the following roles and determine the rules for each role to access the HOSPITAL.PATIENT table

   The row access control rules specify the specific types of information that users in a specific role can access and the conditions under which the role can access the information.

   

   CREATE ROLE PCP;
   CREATE ROLE DRUG_RESEARCH;
   CREATE ROLE ACCOUNTING;
   CREATE ROLE MEMBERSHIP;
   CREATE ROLE PATIENT;

   
3. Issue the CREATE PERMISSION statement to create row permissions that allow each role to access data in specific rows.

   You can use the built-in function VERIFY_ TRUSTED_CONTEXT_ROLE_FOR_USER to determine whether the user identified in special register SESSION_USER is associated with a particular ROLE that is specified as the input argument to the function.

   In the following example, Role PATIENT is allowed to access his or her own row. Role PCP is allowed to access his or her patients' rows. Roles MEMBERSHIP, ACCOUNTING, and DRUG_RESEARCH are allowed to access all rows.

   

   CREATE PERMISSION NETHMO.ROW_ACCESS ON HOSPITAL.PATIENT
      FOR ROWS WHERE (VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'PATIENT') = 1 AND
                         HOSPITAL.PATIENT.USERID = SESSION_USER) OR
                     (VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'PCP') = 1 AND
                         HOSPITAL.PATIENT.PCP_ID = SESSION_USER) OR
                     (VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'MEMBERSHIP') = 1 OR
                     VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'ACCOUNTING') = 1 OR
                     VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'DRUG_RESEARCH') = 1)
         ENFORCED FOR ALL ACCESS
     	 ENABLE;
   
   COMMIT;

   

   The definitions of the new row permissions are stored in the new catalog table SYSIBM.SYSCONTROLS.

4. Issue the ALTER TABLE statement with the ACTIVATE ROW ACCESS CONTROL clause to activate row access control for table HOSPITAL.PATIENT.

   

   ALTER TABLE HOSPITAL.PATIENT ACTIVATE ROW ACCESS CONTROL;
       
   COMMIT;

   

   The ALTER TABLE serialization process takes place and invalidates all packages and dynamic cached statements that reference table HOSPITAL.PATIENT. The value 'R' in the new column SYSTABLES.CONTROL indicates that the table is activated for row access control.

   Db2 also implicitly creates a default row permission which restricts all access from HOSPITAL.PATIENT. The default row permission definition is stored in the new catalog table SYSIBM.SYSCONTROLS.

   Whenever table HOSPITAL.PATIENT is referenced in a data manipulation statement, all row permissions that have been created for it, including the default row permission, are implicitly applied by Db2 to control the rows in the table that are accessible. A row access control search condition is derived from the logical OR operators that connect the search condition in each row permission. This search condition acts as a filter to HOSPITAL.PATIENT before any user-specified operations, such as predicates, grouping, ordering. are processed.

   If necessary, SECADM can deactivate row access control from table HOSPITAL.PATIENT by simply issuing the following ALTER TABLE statement:

   

   ALTER TABLE HOSPITAL.PATIENT DEACTIVATE ROW ACCESS CONTROL;
       
   COMMIT;

   

   Db2 invalidates all packages and dynamic cached statements that reference HOSPITAL.PATIENT. Db2 reflects the removal of row access control by setting SYSTABLES.CONTROL to blank. This also means that table HOSPITAL.PATIENT does not have any access control and that users can retrieve data from all its rows.