RCAC in DB2 For i, Part 2: Column Masks

FIRSTNME	LASTNAME	WORKDEPT	SALARY
SALLY	KWAN	C01	0
DELORES	QUINTANA	C01	0
HEATHER	NICHOLLS	C01	0
KIM	NATZ	C01	0

Result Set 2: The salary column value is masked with a zero when the user is a member of HR_GRP.

Note that the CASE expression in the column mask must return a data type that can be implicitly cast to the column’s data type. Since SALARY’s data type is DECIMAL(9,2), the mask’s case expression needs to return a value compatible with this data type. When you mask a column with a date, the result should be a date (perhaps a dummy date value like 1950-01-01). When it’s character, the mask should be character. For date masks, the date format is an important consideration. A mask involving a date in the 1800s would not work well with an application presenting a US *MDY format. Masking a character column offers the most versatility, as you can substitute special characters such as asterisks or hyphens to hide the data values.

Alternatively, instead of returning a zero, a NULL can be returned for the mask. However, note that legacy applications (such as RPG and COBOL programs) may have to be modified if they’re not equipped to handle NULL values in the salary column.

CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SALARY 
ON CORPDATA.EMPLOYEE
FOR COLUMN SALARY -- This column is DECIMAL(9,2)
RETURN
CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER,'HR_GRP') = 1
     THEN SALARY ELSE NULL END
ENABLE;

Now let’s say the Employee table has social security column SSN defined as CHAR(9):

ALTER TABLE CORPDATA.EMPLOYEE ADD COLUMN SSN CHAR(9)

A mask can be placed on the table to return the right-most four characters of the SSN preceded by Xs:

CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SSN ON CORPDATA.EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN VERIFY_GROUP_FOR_USER(CURRENT_USER,'HR_GRP' ) = 1 
THEN SSN ELSE 'XXXXX'||RIGHT(SSN,4)
END
ENABLE;

When applied, the mask will cause a SSN value of 9001110240 to show as XXXXX0240.

Note that in addition to making sure the data type is compatible, for binary and character data types, the CASE expression cannot return more characters than allowed by the column definition (in this case nine). In this example, formatting the column mask with hyphens will cause the mask to return a string longer than nine characters and will cause DB2 to generate a CPF5029 warning:

-- Invalid mask - result is too long
CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SSN ON CORPDATA.EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN VERIFY_GROUP_FOR_USER(CURRENT_USER,'HR_GRP' ) = 1 
THEN SSN ELSE 'XXX-XX-'||RIGHT(SSN,4)
END
ENABLE;

If the SSN column is defined as DECIMAL(9,0) instead of CHAR(9), the numeric mask could use 9s instead of Xs:

CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SSN 
ON CORPDATA.EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN VERIFY_GROUP_FOR_USER(CURRENT_USER,'HR_GRP' ) = 1 
THEN SSN ELSE 999990000+DEC(RIGHT(DIGITS(SSN),4),4,0)
END
ENABLE;

Now the mask will change a SSN value of 9001110240 to 999990240. Incidentally, a mask can be removed by using the DROP MASK statement:

DROP MASK CORPDATA.EMPLOYEE__SSN

Using More Than User Profile Based Rules For A Mask

Just like with row permissions, a column mask can be built on rules based on columns in the table or DB2 for i special registers. As a dumb example, if a rule was needed to mask a birth date (except in the month of the person’s birth), that could be accomplished as follows using the CURRENT_DATE special register:

CREATE OR REPLACE MASK CORPDATA.EMPLOYEEE__BIRTHDATE ON CORPDATA.EMPLOYEE
FOR COLUMN BIRTHDATE 
RETURN 
CASE WHEN VERIFY_GROUP_FOR_USER ( SESSION_USER , 'HR_GRP' ) = 1
  OR MONTH(BIRTHDATE)=MONTH(CURRENT_DATE)
THEN BIRTHDATE
ELSE DATE('1940-01-01')
END
ENABLE

Updating Tables With Column Masks

Updating data with column masks, including when using RPG or COBOL native I/O, can get you into some trouble. Consider this scenario: during a read operation, if a data value placed into an input buffer (or variable) has been masked, when the data is written back out, the masked data will potentially be saved instead of the original raw value. Oops!

To help avoid this issue, DB2 for i has a new CHECK constraint feature to protect against the insertion or updating of bad data (see this article for an explanation of the ON VIOLATION clause). Consider again the mask created for a numeric social security number:

CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SSN 
ON CORPDATA.EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN VERIFY_GROUP_FOR_USER(CURRENT_USER,'HR_GRP' ) = 1 
THEN SSN ELSE 999990000+DEC(RIGHT(DIGITS(SSN),4),4,0)
END

Say a legacy RPG “employee maintenance” program reads in a row from CORPDATA.EMPLOYEE and a masked value is returned. The user updates some data such as marital status and then saves it, the masked value would become the new permanent value in the row.

The CHECK constraint can test whether or not the data is likely to be a masked value, and if it is, the masked value can be ignored:

ALTER TABLE CORPDATA.EMPLOYEE
ADD CONSTRAINT CORPDATA.ck_EMPLOYEE__SSN_MASK_TEST
CHECK (SSN<999990000)
ON UPDATE VIOLATION PRESERVE SSN;

The check makes sure that the social security number doesn’t start with a group of 9s. The last part of the constraint definition (ON UPDATE VIOLATION) allows the row to be saved when the CHECK is violated, and in this case, it preserves the row’s original SSN value.

This handy feature allows legacy applications to continue working “as-is” without modifying them and without erroneously putting masked data back into the table. However, the ON UPDATE VIOLATION/ON INSERT VIOLATION clause of a CHECK constraint is only useful if it can distinguish a valid value from a masked value. If there is potential overlap between a masked and valid value (such that the CHECK can’t discern the difference with a simple WHERE clause), then this solution will not work. Yet, another way to prevent masked data is to create a before trigger that will reset the value to its original value.

Identifying Existing Column Masks

There are a few ways to find out about column masks defined on a partition:

• In the System i Navigator’s databases node: drill down into the desired schema and then inspect the entries under the “column masks” node.
• SYSCONTROLS catalog view (a CONTROL_TYPE of ‘M’ indicates a mask).

As a repeat from the last tip, implementing RCAC is not a substitute for securing applications and database objects in a responsible way. The drudgery of normal database and application permissions still apply. Column masks augment existing protection, they do not replace it.

Creating column masks can potentially cause performance problems as DB2 now has to evaluate a function and possibly change data on the fly.

Column masks are applied by DB2 to the final results of a query (such that operations such as JOINs and filtering aren’t affected by the mask). However, if a GROUP BY is specified on a masked column, then the masked value is used in the GROUP BY processing.

In the end, RCAC can be used to fine tune database security by blocking users from viewing rows and specific column values in a table. RCAC is implemented at the database engine level such that in many cases existing applications will not require changes to partake of the benefit. However, this is not always the case.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

RCAC in DB2 for i, Part 1: Row Access Permissions

DB2 for i 7.2 Features And Fun: Part 2