Column masks

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.

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 can create, alter, or drop column masks the SECADM or SYSADM authority.

How column masks affect queries

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, or ORDER BY. The rows that are returned in the final result table remain the same, except that the values in the resulting rows might have been masked by the column masks. As such, if the masked column also appears in an ORDER BY clause with a sort-key expression, the order is based on the original values of the column 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 a SELECT DISTINCT statement or a COUNT (DISTINCT expression) function. 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.

Examples of column masks changing LISTAGG results

  • Start of changeFL 506 The following example shows how the result returned by the LISTAGG function can change if the sort-key expression references a column for which a column mask is defined. For this example, assume that an EMPLOYEE table is created with the following statement.
    CREATE TABLE EMPLOYEE ( STATE CHAR(2) NOT NULL,
                        SALARY INTEGER,             
                        BONUS  INTEGER,             
                        NAME  VARCHAR(20) NOT NULL);

    Also assume that a column mask is defined on the SALARY column with the following CREATE MASK statement. 

    CREATE MASK SALARY_MASK ON EMPLOYEE
    FOR COLUMN SALARY
        RETURN
            CASE
                WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, 'MANAGER') = 1) THEN
                    SALARY
                ELSE  0
            END
    ENABLE;

    Also assume that the following query returns the following result when a manager issues it.

    SELECT STATE,                            
             CASE WHEN(SALARY >50000) THEN NAME
                      ELSE 'XXXX'                  
                  END AS NAME,                      
              SALARY                            
      FROM EMPLOYEE; 

    
       +---------------------------------------------------------+ 
       | STATE |     NAME    |         SALARY                    | 
       +---------------------------------------------------------+ 
       | CA    |     Jerry   |         80000                     | 
       | CA    |     Tom     |         70000                     | 
       | LU    |     XXXX    |         40000                     | 
       | LU    |     XXXX    |         10000                     | 
       | LU    |     XXXX    |         50000                     | 
       | MA    |     Sarah   |         90000                     | 
       + --------------------------------------------------------+

    Now consider the following LISTAGG statement.

    SELECT STATE,
           LISTAGG (NAME,',') WITHIN GROUP (ORDER BY SALARY ASC)
    FROM EMPLOYEE
    GROUP BY STATE;

    If a manager issues the example SELECT statement, it returns the following result. The column mask did not apply, so the names in each row are ordered based on the unmasked SALARY values.

    
    +----------------------------------------+ 
    | STATE |                                | 
    +----------------------------------------| 
    | CA    | Tom,Jerry                      | 
    | LU    | Ben,Linda,Mary                 | 
    | MA    | Sarah                          |
    +----------------------------------------+

    However, if a non-manager employee issues the example SELECT statement, the column mask applies before the sort-key expression is evaluated. The result is that the masked SALARY value 0 is used for every employee when the ORDER BY is evaluated, so the names in each row are returned in a different order.

    
    +----------------------------------------+ 
    | STATE    |                             | 
    +----------------------------------------| 
    | CA       | Jerry,Tom                   | 
    | LU       | Linda,Ben,Mary              | 
    | MA       | Sarah                       | 
    +----------------------------------------+
    End of change