Creating column masks

A column mask specifies the rules for users to receive the masked values that are returned for the column. With the SECADM authority, you can use the CREATE MASK statement to create a column mask.

Before you begin

If the SEPARATE_SECURITY system parameter on panel DSNTIPP1 is set to YES during installation or migration, you must have the SECADM authority to create a column mask. If SEPARATE_SECURITY is set to NO, you must have the SECADM or SYSADM authority.

About this task

Suppose that you are a data security administrator (SECADM) for a national health organization (NetHMO) and responsible for safeguarding sensitive patient information. You want to create a data privacy and security policy and implement it through column masks on tables that are enabled with column access control. The mask definitions prescribe the conditions under which patients, physicians, pharmacists, or account administrators can only receive certain masked values from the column based on their roles or account authentication IDs.

To create a column mask:

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 that access the HOSPITAL.PATIENT table.

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

3. Issue the CREATE MASK statement to create column masks that allow each role to receive certain masked values from specific columns.

   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.

   The following example shows how column mask SSN_MASK is created. Roles PATIENT and ACCOUNTING are allowed to receive column values from column SSN. Other roles that access the column will receive masked values.

   CREATE MASK NETHMO.SSN_MASK ON HOSPITAL.PATIENT FOR
      COLUMN SSN RETURN
         CASE WHEN VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'PATIENT') = 1 OR
                   VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                         'ACCOUNTING') = 1
              THEN SSN
              ELSE CHAR('XXX-XX-') || SUBSTR(SSN,8,4)
              END
         ENABLE;
   
   COMMIT;

   You can issue the CREATE MASK statements to create column masks USERID_MASK, NAME_MASK, and ADDRESS_MASK. The definitions of all these column masks are stored in the new catalog table SYSIBM.SYSCONTROLS.

4. Use the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause to activate column access control for table HOSPITAL.PATIENT

   ALTER TABLE HOSPITAL.PATIENT ACTIVATE COLUMN 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 'C' in the new column SYSTABLES.CONTROL indicates that the table is activated for column access control.

   Whenever column SSN of table HOSPITAL.PATIENT is referenced in the outermost SELECT clause of a data manipulation statement, column mask SSN_MASK is implicitly applied by DB2® to control the masked values that are returned for it.

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

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

   DB2 invalidates all packages and dynamic cached statements that reference HOSPITAL.PATIENT. DB2 reflects the removal of column access control by setting SYSTABLES.CONTROL to blank.