DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
The CREATE MASK statement creates a column mask at the current server. A column mask specifies the value to be returned for a specified column.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
The privileges held by the authorization ID of the statement must include SECADM authority. SECADM authority can create a column mask in any schema. Additional privileges are not needed to reference other objects in the mask definition. For example, the SELECT privilege is not needed to retrieve from a table, and the EXECUTE privilege is not needed to call a user-defined function.
>>-CREATE--+------------+--MASK--mask-name--ON--table-name------> '-OR REPLACE-' >--+--------------------------+---------------------------------> | .-AS-. | '-+----+--correlation-name-' >--FOR COLUMN--column-name--RETURN--case-expression-------------> .-DISABLE-. >--+---------+------------------------------------------------->< '-ENABLE--'
The application of enabled column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT, and ORDER BY. The rows returned in the final result table remain the same, except that the values in the resulting rows might be masked by the column masks. As such, if the masked column also appears in an ORDER BY sort-key, the order is based on the original column values and the masked values in the final result table might not reflect that order. Similarly, the masked values might not reflect the uniqueness enforced by SELECT DISTINCT. If the masked column is embedded in an expression, the result of the expression might become different because the column mask is applied on the column before the expression evaluation can take place. For example, applying a column mask on column SSN might change the result of aggregate function COUNT(DISTINCT SSN) because the DISTINCT operation is performed on the masked values. On the other hand, if the expression in the query is the same as the expression used to mask the column value in the column mask definition, the result of the expression might remain unchanged. For example, the expression in the query is 'XXX-XX-' || SUBSTR(SSN, 8, 4) and the same expression appears in the column mask definition. In this particular example, you can replace the expression in the query with column SSN to avoid the same expression getting evaluated twice.
A column mask is created as a stand alone object without knowing all of the contexts in which it might be used. To mask a column value in the final result table, the column mask definition is merged into a query by the database manager. When the column mask definition is brought into the context of the statement, it might conflict with certain SQL semantics in the statement. Therefore, in some situations, the combination of the statement and the application of the column mask might return an error (SQLSTATE 428HD). When this happens, either the statement needs to be modified or the column mask must be dropped or recreated with a different definition. See the ALTER TABLE statement description for those situations where a bind time error might be issued for the statement.
If the column is not nullable, its column mask definition will not consider a null value for the column. After column access control is activated for the target table, if the target table is the null-padded table in an outer join operation, the column value in the final result table might be a null. To ensure the column mask has the ability to mask a null value, when the database manager merges the column mask definition into the query, if the target table is the null-padded table in an outer join operation, "WHEN target-column IS NULL THEN NULL" will be added as the first WHEN clause to the column mask definition. This forces a null value to be always masked to a null. For a nullable column, this takes away the ability to mask a null value to something else but it is an acceptable restriction from security and usability standpoints.
When a column is used to derive the new value for an INSERT, UPDATE, MERGE, or a SET transition-variable assignment statement, the original column value, not the masked value, is used to derive the new value. If the column has a column mask, that column mask is applied to ensure the evaluation of the access control rules at run time masks the column to itself, not to a constant or an expression. This is to ensure the masked values are the same as the original column values. If a column mask does not mask the column to itself, the existing row is not updated or the new row is not inserted and an error is returned at run time (SQLSTATE 428HD). If there is a requirement for masked data to be inserted into a table, it can be done by first assigning the data to a variable. For example, an array variable can be created with the array elements having a row data type. Table data with column masks applied can be assigned to the array variable, which can then be used to insert the data into some other table. The rules that are used to apply column masks in to derive the new values follow the same rules described previously for the final result table of a query. See the INSERT, UPDATE, and MERGE statements for how the column masks are used to affect the insert and update operation.
See the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause for information about how to activate column access control for the table and how a column mask is applied.
Column masks that are created before column access control is activated for a table: The CREATE MASK statement is an independent statement that can be used to create a column access control mask before column access control is activated for a table. The only requirement is that the table and the columns exist before the mask is created. Multiple column masks can be created for a table but a column can have one mask only.
The definition of a mask is stored in the database catalog. Dependency on the table for which the mask is being created and dependencies on other objects referenced in the definition are recorded. No package or dynamic cached statement is invalidated. A column mask can be created as enabled or disabled for column access control. An enabled column mask does not take effect until the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause is used to activate column access control for the table. SECADM authority is required to issue such an ALTER TABLE statement. A disabled column mask remains ineffective even when column access control is activated for the table. The ALTER MASK statement can be used to alter between ENABLE and DISABLE.
After column access control is activated for a table, when the table is referenced in a data manipulation statement, all enabled column masks that have been created for the table are implicitly applied by the database manager to mask the values returned for the columns referenced in the final result table of the queries or to determine the new values used in the data change statements.
Creating column masks before activating column access control for a table is the recommended sequence to avoid multiple invalidations of packages and dynamic cached statements that reference the table.
CREATE MASK SSN_MASK ON EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'PAYROLL') = 1)
THEN SSN
WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1)
THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
ELSE NULL
END
ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
SELECT SSN FROM EMPLOYEE WHERE EMPNO = 123456;CREATE MASK SSN_MASK ON EMPLOYEE
FOR COLUMN SSN RETURN
CASE WHEN (1 = 1) THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
ELSE NULL
END
ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
SELECT 'XXX-XX-' || SUBSTR(SSN,8,4) FROM EMPLOYEE WHERE EMPNO = 123456;A SELECT statement is used to generate a report to show the average hours used by households in each city. Column mask CITY_MASK is created to mask the city name based on the opt-in or opt-out information chosen by the sampled households. However, after column access control is activated for table LIBRARY_USAGE, the SELECT statement receives a bind time error. This is because column mask CITY_MASK references another column LIBRARY_OPT and LIBRARY_OPT does not identify a grouping column.
CREATE MASK CITY_MASK ON LIBRARY_USAGE
FOR COLUMN CITY RETURN
CASE WHEN (LIBRARY_OPT = 'OPT-IN') THEN CITY
ELSE ' '
END
ENABLE;
ALTER TABLE LIBRARY_USAGE ACTIVATE COLUMN ACCESS CONTROL;
SELECT CITY, AVG(LIBRARY_TIME) FROM LIBRARY_USAGE GROUP BY CITY;CREATE MASK SALARY_MASK ON EMPLOYEE
FOR COLUMN SALARY RETURN
CASE WHEN (BONUS < 10000) THEN SALARY
ELSE NULL
END
ENABLE;
CREATE MASK BONUS_MASK ON EMPLOYEE
FOR COLUMN BONUS RETURN
CASE WHEN (BONUS > 5000) THEN NULL
ELSE BONUS
END
ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
SELECT SALARY FROM EMPLOYEE WHERE EMPNO = 123456;