Column mask

A column mask is a database object that describes a specific column access control rule for a column. In the form of an SQL CASE expression, the rule specifies the condition under which a user, group, or role can receive the masked values that are returned for a column.

Stored in the system catalog, column masks can be created on all base tables except materialized query tables and maintained on an individual basis. The definition of each column mask may reference the user, group, or role in the search conditions in the CASE WHEN clause.

While multiple columns in a table may have column masks, only one column mask can be created for a single column. When column access control is activated for the table, the CASE expression in the column mask definition is applied to an output column to determine the masked values that are returned to an application. The application of column masks affects the final output only; it does not impact the operations, such as predicates and ordering, in an SQL statement. In addition, the application of column masks must not change the values in a row that is being inserted or updated in an INSERT, UPDATE, or MERGE statement.

Only an authorization ID or role with the SECADM or SYSADM authority can manage column masks. 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, alter, or drop column masks. If SEPARATE_SECURITY is set to NO, you must have the SECADM or SYSADM authority.