RCAC in DB2 For i, Part 2: Column Masks
September 1, 2015 Michael Sansoterra
In part 1 of this series, I discussed the row permissions portion of the new row and column access control (RCAC) security feature that was introduced in DB2 for i 7.2. In this tip, I will demonstrate how to use RCAC to hide sensitive information without hiding entire rows from a user.
To sum up the last tip, RCAC is beneficial for protecting an integral business asset: data. Row permissions allow a database administrator to limit the rows users can view or modify in a table by defining access rules. Permissions to access rows are generally based on user profile or group profile membership, but can also be based on time of day, values in the rows, etc.
This tip assumes you have the prerequisites met (covered in the prior tip) and that you have one or more users assigned to the database security administrator function. Only a database security administrator (a user profile assigned the function usage QIBM_DB_SECADM) can create a column mask using the CREATE MASK statement. It also assumes you are familiar with the various user special registers (USER, SESSION_USER, CURRENT_USER, SYSTEM_USER) and the VERIFY_GROUP_FOR_USER function.
Column masks increase security by protecting sensitive information in a column (assuming the user profile has access to the rows). Examples of columns worthy of protection are social security numbers, salaries and credit card numbers. While column masks may be used for any column in a database table, there is overhead incurred to use them so their implementation should be limited to sensitive information. Because RCAC is maintained at the database engine level, DB2 can hide rows and mask data in columns regardless of the application or interface used to access the data.
Preparing a Table for Column Masks
Before a table can have column masks applied, it must be enabled for “column access control” using the ALTER TABLE statement:
ALTER TABLE CORPDATA.EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL
Exclusive access to the table is required for this change. Column access control can be turned off in a similar fashion:
ALTER TABLE CORPDATA.EMPLOYEE DEACTIVATE COLUMN ACCESS CONTROL
At this early RCAC release, CREATE MASK seems to often leave an exclusive lock on the table such that I have to kill the session before using the table again. I also found that issuing an ALTER TABLE to add or drop a column or constraint may deactivate the column access control on a table.
Creating a Column Mask
A column mask is defined by setting up a rule that defines when a column’s data can be viewed in its normal (i.e., unprotected) form. If the data request does not meet the rule’s requirement, a “masked” or protected value is supplied in place of the real value. The mask’s rule is defined with a CASE statement.
In this example, a mask is created on the CORPDATA.EMPLOYEE table’s SALARY column. If the requesting user is a member of the human resources (HR_GRP) group profile, then the salary will be returned as expected. If the user is not part of that group membership, a zero will be returned 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 0 END ENABLE;
The results will be returned as follows:
Result Set 1: The salary column value appears as normal when the user is a member of HR_GRP.
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:
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.