Propagation of masked data

Performing an insert or update operation into a base table with active column access control, the operation may fail because the data is the masked data.

This can happen when the data to be inserted or updated contains the masked value, and the masked data was selected from a table with active column access control and the select was done in the same SQL statement. As an example, assume that both TABLE1 and TABLE2 have active column access control and for the insert, selecting from TABLE2 would return the masked data. The following statement would return an error:

INSERT INTO TABLE1 SELECT * FROM TABLE2

The statement would fail with SQ20478 – Row or column access control is not valid.

However, assume for this example, TABLE1 and TABLE2 contain two columns, NAME and SSN. For the user doing the INSERT, the mask is defined to return the string ‘XXX-XX-nnnn’ when querying TABLE2.

SELECT NAME, SSN INTO :name, :ssn FROM TABLE2;
INSERT INTO TABLE1 VALUES(:name, :ssn);

This same type of problem can also occur if the user is running a native database application. A READ from TABLE2 followed by a WRITE into TABLE1 could result in masked data that is written to the file. Or in the case of an update, even if the SSN column is not intended to change on the UPDATE, the record being updated contains the masked value for the SSN column and the SSN column changes.

Before Trigger Solution

The trigger solution checks the new data that is written into a column and conditionally sets the column to the current value, or sets it to the DEFAULT.

This is an example of a before insert/update trigger for preventing masked data:

     CREATE SCHEMA MY_LIB
     CREATE TABLE  MY_LIB.EMP_INFO
                  (COL1_name CHAR(10) WITH DEFAULT 'DEFAULT',
                   COL2_ssn  CHAR(11) WITH DEFAULT 'DEFAULT')


     /********************************************************************/
     /* Create a mask to give COL2_ssn for DBMGR, but for any other user */
     /* mask the column. This table will contain a trigger to ensure the */
     /* column can never contain a masked value.                         */
     /********************************************************************/

     CREATE MASK MASK_SSN ON MY_LIB.EMP_INFO
                 FOR COLUMN COL2_ssn
                 RETURN
                 CASE
                   WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'DBMGR') = 1  
                        THEN COL2_ssn
                        ELSE 'XXX-XX-'||SUBSTR(COL2_ssn,8,4) 
                 END
                 ENABLE

     ALTER TABLE MY_LIB.EMP_INFO ACTIVATE COLUMN ACCESS CONTROL


     CREATE TRIGGER PREVENT_MASK_SSN BEFORE INSERT OR UPDATE ON MY_LIB.EMP_INFO
                    REFERENCING NEW ROW AS N OLD ROW AS O
                    FOR EACH ROW MODE DB2ROW
                    SECURED
                    WHEN(SUBSTR(N.COL2_ssn,1,7) = 'XXX-XX-')
                     BEGIN
                       IF INSERTING THEN SET N.COL2_ssn = DEFAULT;
                       ELSEIF UPDATING THEN SET N.COL2_ssn = O.COL2_ssn;
                       END IF;
                     END

Attempting an insert or update operation causes the before trigger to be executed and ensure the correct data into column COL2_ssn.

Check Constraint Solution

The check constraint-based solution provides new SQL syntax to allow the specification of an action to perform when a violation of the check constraint’s check-condition occurs instead of returning a hard error. However, if the check-condition continues to fail after the action is taken, a hard error will be returned and the SQL statement fails with the existing constraint failure, (SQLSTATE=23513, SQLCODE=-545).

A check constraint with the on-violation-clause is allowed on both the CREATE TABLE and ALTER TABLE statements.

In the following example, the mask is defined to return a value of ‘XXX-XX-nnnn’ for any query that is not done by a user profile in the ‘DBMGR’ group. The constraint checks that the column SSN does not have the masked value.

     CREATE SCHEMA MY_LIB
     SET    SCHEMA MY_LIB
     CREATE TABLE  MY_LIB.EMP_INFO
                  (COL1_name CHAR(10) WITH DEFAULT 'DEFAULT',
                   COL2_ssn  CHAR(11) WITH DEFAULT 'DEFAULT')  

     CREATE MASK MASK_ssn ON  MY_LIB.EMP_INFO 
	               FOR  COLUMN COL2_ssn 	RETURN  
                   CASE 
                      WHEN VERIFY_GROUP_FOR_USER ( SESSION_USER , 'DBMGR' ) = 1
                      THEN COL2_ssn  
                      ELSE 'XXX-XX-'||SUBSTR(COL2_ssn,8,4) 
                   END    
	               ENABLE


     /* Check constraint for the update and insert.*/
     ALTER TABLE MY_LIB.EMP_INFO                 
                 ADD CONSTRAINT  MASK_ssn_preserve
                 CHECK(SUBSTR(COL2_ssn,1,7)<>’XXX-XX-’)
                 ON UPDATE VIOLATION PRESERVE  COL2_ssn
                 ON INSERT VIOLATION SET COL2_ssn = DEFAULT