CREATE MASK

The CREATE MASK statement creates a column mask at the current server. A column mask is used for column access control and specifies the value that should be returned for a specified column.

Invocation

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.

Authorization

The privilege set that is defined below must include the following authority:

  • 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 query a table, and the EXECUTE privilege is not needed to invoke a user-defined function.

Privilege set: If the statement is embedded in an application program, the privilege set is the set of privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. However, if the process is running in a trusted context that is defined with the ROLE AS OBJECT OWNER AND QUALIFIER clause, the privilege set is the set of privileges that are held by the role that is in effect.

Syntax

Read syntax diagramSkip visual syntax diagramCREATE MASKmask-nameONtable-name AScorrelation-nameFOR COLUMNcolumn-nameRETURNcase-expressionDISABLEENABLE

Description

mask-name
Specifies the names the column mask. The name, including the implicit or explicit qualifier, must not identify a column mask or a row permission that already exists at the current server.
ON table-name
Identifies the table for which the column mask is created. The name must identify a table that exists at the current server. It must not identify any of the following objects:
  • An auxiliary table
  • A created or declared temporary table
  • A view
  • A catalog table
  • An alias
  • A synonym
  • A materialized query table or table that is directly or indirectly referenced in the definition of a materialized query table
  • A table that was implicitly created for an XML column
  • A table that contains a period
  • A history table
  • An accelerator-only table
  • An archive-enabled table
  • An archive table
correlation-name
Specifies a correlation name that can be used within case-expression to designate the table. For information about correlation-name, see Correlation names.
FOR COLUMN column-name
Identifies the column to which the mask applies. column-name must be an unqualified name that identifies a column of the specified table. A mask must not already exist for the column. The column must not be:
  • a LOB column or a distinct type column that is based on a LOB
  • an XML column
  • defined with a FIELDPROC
RETURN case-expression
Specifies a CASE expression that determines the value that is returned for the column. The result of the CASE expression is returned in place of the column value in a row. The result data type, null attribute, data length, subtype, encoding scheme, and CCSID of the CASE expression must be identical to those attributes of the column that is specified by column-name. If the data type of column-name is a user-defined data type, the result data type of the CASE expression must be the same user-defined type. The CASE expression must not reference any of the following objects:
  • A remote object
  • The table for which the column mask is being defined
  • A created global temporary table or a declared global temporary table
  • An auxiliary table
  • A table that was implicitly created for an XML column
  • A column that is defined with a FIELDPROC
  • A LOB column or a distinct type column that is based on a LOB
  • An XML column
  • A select list notation * or name.* in the SELECT clause
  • A table function
  • A collection-derived table (UNNEST)
  • A user-defined function that is defined as not secure
  • A function that is not deterministic or that has an external action or is defined with the MODIFIES SQL DATA option
  • An aggregate function, unless it is specified in a subquery
  • A built-in table function
  • An XMLTABLE table function
  • An XMLEXISTS predicate
  • An OLAP specification
  • A ROW CHANGE expression
  • A sequence reference
  • A host variable, SQL variable, SQL parameter, or trigger transition variable
  • A parameter marker
  • A table reference that contains a period specification
  • A view that includes any of the preceding restrictions in its definition
  • An accelerator-only table

Start of changeThe encoding scheme of the table is used to evaluate the CASE expression. Tables and language elements that require multiple encoding scheme evaluation, other than EBCDIC tables with Unicode columns, must not be referenced in the CASE expression. See Determining the encoding scheme and CCSID of a string for language elements that require multiple evaluation.End of change

If the CASE expression references tables for which row or column access control is active, access controls for those tables are not cascaded.

DISABLE or ENABLE
Specifies that the column mask is to be enabled or disabled for column access control.
DISABLE
Specifies that the column mask is to be disabled for column access control. The column mask will remain disabled regardless of whether column access control is activated for the table.

DISABLE is the default.

ENABLE
Specifies that the column mask is to be enabled for column access control. If column access control is not currently active for the table, the column mask will become enabled when column access control is activated for the table. If column access control is currently active for the table, the column mask becomes enabled immediately and all packages and statements in the dynamic statement cache that reference the table are invalidated. For more information, see Changes that invalidate packages.

Notes

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. 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, a column mask on column SSN might change the result of the aggregate function COUNT(DISTINCT SSN) because the DISTINCT operation is performed on the unmasked values.
Conflicts between the definition of a column mask and SQL:
A column mask is created as a stand alone object, without knowing all of the contexts in which it might be used. To mask the value of a column in the final result table, the definition of the column mask is merged into a query by Db2. When the definition of the column mask 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 can return an error. When this happens, either the statement needs to be modified or the column mask must be dropped or re-created with a different definition. See ALTER TABLE for those situations in which a bind time error might be issued for the statement.
Column masks and null columns:
If the column is not nullable, the definition of its column mask will not, most likely, consider a null value for the column. After the column access control is activated for the table, if the table is the null-padded table in an outer join, the value of he column in the final result table might be a null. To ensure that the column mask can mask a null value, if the table is the null-padded table in an outer join, Db2 will add "WHEN target-column IS NULL THEN NULL" as the first WHEN clause to the column mask definition. This forces a null value to always be masked as a null value. For a nullable column, this removes the ability to mask a null value as something else. Example 5 shows this added WHEN clause.
Column mask values for SQL data change statements
When columns are used to derive new values for an INSERT, UPDATE, MERGE, or a SET transition-variable assignment statement, the original values of the column, not the masked values, are used to derive the new values. If the columns have column masks, those column masks are applied to ensure that 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 that 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. The rules that are used to apply column masks in order to derive the new values follow the same rules for the final result table of a query.
Column masks that are created before column access control is activated:
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 Db2 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 Db2 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.

Tip: To avoid multiple invalidations of packages and dynamic cached statements that reference the table, creating column masks before activating column access control for a table .

Column masks that are created after column access control is activated:
The enabled column masks become effective as soon as they are committed. All the packages and dynamic cached statements that reference the table are invalidated. Thereafter, when the table is referenced in a data manipulation statement, all enabled column masks are implicitly applied by Db2 to the statement. Any disabled column mask remains ineffective even when column access control is activated for the table.
No cascaded effect when column or row access control enforced tables are referenced in column mask definitions:
A column mask definition may reference tables and columns that are currently enforced by row or column access control. Access control from those tables and columns are ignored when the table for which the column mask is being created is referenced in a data manipulation statement.
Multiple column masks and row permissions sharing the same environment variables:
Multiple column masks and row permissions can be created for a table. They must use the same set of environment variables. The set of environment variables is determined when the first column mask or row permission is created for the table.

The catalog table SYSENVIRONMENT contains the list of environment variables. The following table shows which environment variable must be the same among the multiple column masks and row permissions.

Table 1. Environment Variables in SYSIBM.SYSENVIRONMENT
Environment variables shown as SYSENVIRONMENT columns Description Static create statement Dynamic create statement Must be the same among multiple column masks and row permissions?
ENVID Internal identifier of the environment Assigned by Db2 Assigned by Db2 Yes
CURRENT_SCHEMA The qualifier used to qualify unqualified objects such as tables, views. etc. Package owner Value of CURRENT_SCHEMA special register Yes
PATHSCHEMAS The schema path used to qualify unqualified object such as user-defined functions and CAST functions for user-defined data types. PATH bind option Value of CURRENT_PATH special register Yes
APPLICATION_
ENCODING_
CCSID
The CCSID of the application environment ENCODING bind option CURRENT APPLICATION ENCODING SCHEME special register Yes
ORIGINAL_
ENCODING_
CCSID
The original CCSID of the statement text string CCSID(n) pre-compiler option or EBCDIC CCSID on DSNTIPF installation panel CCSID based on DEF ENCODING SCHEME on DSNTIPF installation panel Yes
DECIMAL_POINT The decimal point indicator COMMA or PERIOD precompiler option or DECIMAL POINT IS on DSNTIPF installation panelv DECIMAL POINT IS on DSNTIPF installation panel Yes
MIN_DIVIDE_SCALE The minimum divide scale MINIMUM DIVIDE SCALE on DSNTIP4 installation panel MINIMUM DIVIDE SCALE on DSNTIP4 installation panelv Yesv
STRING_DELIMITER The string delimiter that is used in COBOL string constants APOST precompiler option or STRING DELIMITER on DSNTIPF installation panel STRING DELIMITER on DSNTIPF installation panel No
SQL_
STRING_
DELIMITER
The SQL string delimiter that is used in constants APOSTSQL pre-compiler option or SQL STRING DELIMITER on DSNTIPF installation panel SQL STRING DELIMITER on DSNTIPF installation panel Yes
MIXED_DATA Uses mixed DBCS data MIXED DATA on DSNTIPF installation panel MIXED DATA on DSNTIPF installation panel Yes
DECIMAL_
ARITHMETIC
The rules that are to be used for CURRENT PRECISION and when both operands in a decimal operation have a precision of 15 or less. DEC(15) or DEC(31) precompiler option or DECIMAL ARITHMETIC on DSNTIP4 installation panel DECIMAL ARITHMETIC on DSNTIP4 installation panel Yes
DATE_FORMAT The date format DATE pre-compiler option or DATE FORMAT on DSNTIP4 installation panel DATE FORMAT on DSNTIP4 installation panel Yes
TIME_FORMAT The time format TIME pre-compiler option or TIME FORMAT on DSNTIP4 installation panel TIME FORMAT on DSNTIP4 installation panel Yes
FLOAT_FORMAT The floating point format FLOAT (S390 | IEEE) pre-compiler option or default of FLOAT S390 Default of FLOAT S390 No
HOST_LANGUAGE The host language HOST pre-compiler option or LANGUAGE DEFAULT on DSNTIPF installation panel LANGUAGE DEFAULT on DSNTIPF installation panel No
CHARSET The character set CCSID(n) pre-compiler option or EBCDIC CCSID on DSNTIPF installation panel EBCDIC CCSID on DSNTIPF installation panel No
FOLD FOLD is only applicable when HOST_LANGUAGE is C or CPP. Otherwise FOLD is blank. HOST(C(FOL D) precompiler option or default of NO FOLD default of NO FOLD No
ROUNDING The rounding mode that is used when arithmetic and casting operations are performed on DECFLOAT data. ROUNDING bind option CURRENT DECFLOAT ROUNDING MODE special register Yes
Note: In a data sharing environment, if a separate DSNHDECP module is provided for each member of the group, the DSNHDECP settings for each environment variable should be the same in all members of the data sharing group, otherwise an error might be issued when multiple column masks or row permissions are created.
Ordinary SQL identifiers specified in a static CREATE MASK statement in a COBOL application:
If the CREATE MASK statement is a static statement in a COBOL application, the ordinary SQL identifiers used in the column mask definition must not follow the rules for naming COBOL words. They must follow the rules for naming SQL identifiers. For example, the COBOL word 1ST-TIME is not allowed as an ordinary SQL identifier in a column mask definition; change it to FIRST_TIME or put it in the delimiters.
Encoding scheme and CCSIDs of the data manipulation statement after column masks are applied:
Start of changeThe encoding scheme and CCSIDs of the data manipulation statement are not affected by the column masks that are implicitly applied by Db2 for the column access control. For a target table or a referenced table that is not an EBCDIC table with Unicode columns, the column mask definition is evaluated using its table's encoding scheme and CCSIDs. For a target table or a referenced table that is an EBCDIC table with Unicode columns, the column mask definition is evaluated using the rules for multiple encoding schemes.End of change
Consideration for Db2 limits:
If the data manipulation statement already approaches some Db2 limits in the statement, it should be noted that the more enabled column masks and enabled row permissions are created, the more likely they would impact some limits. For example, they may cause the statement to exceed the maximum total length (32600 bytes) of columns of a query operation requiring sort and evaluating aggregate functions (MULTIPLE DISTINCT and GROUP BY). This is because the enabled column mask and enabled row permission definitions are implicitly merged into the statement when the table is referenced in a data manipulation statement. See "Limits in Db2 for z/OS®" in SQL Reference for the limits of a statement.
Restrictions involving pending definition changes:
CREATE MASK is not allowed if the mask is defined on a table or references a table that has pending definition changes.

Examples

Example 1
After column access control is activated for table EMPLOYEE, Paul from the payroll department can see the social security number of the employee whose employee number is 123456. Mary who is a manager can see the last four characters only of the social security number. Peter who is neither cannot see the social security number.
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;

COMMIT;

ALTER TABLE EMPLOYEE 
    ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SSN FROM EMPLOYEE 
    WHERE EMPNO = 123456;
Example 2
In the SELECT statement, column SSN is embedded in an expression that is the same as the expression used in the column mask SSN_MASK. After column access control is activated for table EMPLOYEE, the column mask SSN_MASK is applied to column SSN in the SELECT statement. For this particular expression, the SELECT statement produces the same result as before column access control is activated for all users. The user can replace the expression in the SELECT statement with column SSN to avoid the same expression gets evaluated twice.
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;

COMMIT;

ALTER TABLE EMPLOYEE 
    ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT 'XXX-XX-' || SUBSTR(SSN,8,4) FROM EMPLOYEE 
    WHERE EMPNO = 123456;
Example 3
A state government conducted a survey for the library usage of the households in each city. Fifty households in each city were sampled in the survey. Each household was given an option, opt-in or opt-out, whether to show their usage in any reports generated from the result of the survey.

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;

COMMIT;

ALTER TABLE LIBRARY_USAGE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT CITY, AVG(LIBRARY_TIME) FROM LIBRARY_USAGE 
   GROUP BY CITY;
Example 4
Employee with EMPNO 123456 earns bonus $8000 and salary $80000 in May. When the manager retrieves his salary, the manager receives his salary, not the null value. This is because of no cascaded effect when column mask SALARY_MASK references column BONUS for which column mask BONUS_MASK is defined.
CREATE MASK SALARY_MASK ON EMPLOYEE
   FOR COLUMN SALARY RETURN 
       CASE 
            WHEN (BONUS < 10000) 
             THEN SALARY
            ELSE NULL
       END
   ENABLE;

COMMIT;

CREATE MASK BONUS_MASK ON EMPLOYEE
   FOR COLUMN BONUS RETURN
       CASE 
            WHEN (BONUS > 5000) 
             THEN NULL
            ELSE BONUS
       END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE 
    ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SALARY FROM EMPLOYEE 
    WHERE EMPNO = 123456;
Example 5
This example shows Db2 adds "WHEN target-column IS NULL THEN NULL" as the first WHEN clause to the column mask definition then merges the column mask definition into the statement.
CREATE EMPLOYEE (EMPID INT,
                 DEPTID CHAR(8),
                 SALARY DEC(9,2) NOT NULL,
                 BONUS DEC(9,2));

CREATE MASK SALARY_MASK ON EMPLOYEE
    FOR COLUMN SALARY RETURN
       CASE 
            WHEN SALARY < 10000 
             THEN CAST(SALARY*2 AS DEC(9,2))
            ELSE COALESCE(CAST(SALARY/2 AS DEC(9,2)), BONUS)
       END
    ENABLE;

COMMIT;

CREATE MASK BONUS_MASK ON EMPLOYEE
    FOR COLUMN BONUS RETURN 
      CASE 
          WHEN BONUS > 1000 
           THEN BONUS
          ELSE NULL
      END
    ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
    ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SALARY FROM DEPT 
    LEFT JOIN EMPLOYEE ON DEPTNO = DEPTID;

/* When SALARY_MASK is merged into the above statement,
 * 'WHEN SALARY IS NULL THEN NULL' is added as the 
 * first WHEN clause, as follows:
 */

SELECT CASE WHEN SALARY IS NULL THEN NULL
            WHEN SALARY < 10000 THEN CAST(SALARY*2 AS DEC(9,2))
            ELSE COALESCE(CAST(SALARY/2 AS DEC(9,2)), BONUS)
       END SALARY
       FROM DEPT 
         LEFT JOIN EMPLOYEE ON DEPTNO = DEPTID;