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.
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 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.
- 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
- 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
- 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
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
The 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.
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.
- 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.
- 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.
- 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 YesAPPLICATION_
The CCSID of the application environment ENCODING bind option CURRENT APPLICATION ENCODING SCHEME special register YesORIGINAL_
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 NoSQL_
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 YesDECIMAL_
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 YesNote: 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:
- The 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.
- 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.
- 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
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;