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
>>-CREATE MASK--mask-name--ON--table-name-----------------------> >--+--------------------------+--FOR COLUMN--column-name--------> | .-AS-. | '-+----+--correlation-name-' .-DISABLE-. >--RETURN--case-expression--+---------+------------------------>< '-ENABLE--'
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
- 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 user-defined external table function or a user-defined SQL table function
- 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 specified table is used to evaluate the CASE expression. Tables and language elements that require multiple encoding scheme evaluation 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.
- 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.
Notes
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(SSESSION_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;