CREATE MASK statement

The CREATE MASK statement creates a column mask at the current server. A column mask specifies the value to 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 privileges held by the authorization ID of the statement must include 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 to retrieve from a table, and the EXECUTE privilege is not needed to call a user-defined function.

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACE MASKmask-nameONtable-name AScorrelation-nameFOR COLUMNcolumn-nameRETURN case-expressionDISABLEENABLE

Description

OR REPLACE
Specifies to replace the definition for the column mask if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog.
mask-name
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 (SQLSTATE 42710).
table-name
Identifies the table on which the column mask is created. The name must identify a table that exists at the current server (SQLSTATE 42704). It must not identify a nickname, created or declared temporary table, view, synonym, typed table, alias (SQLSTATE 42809), shadow table or base table of a shadow table (SQLSTATE 428HZ), external table (SQLSTATE 42858), or catalog table (SQLSTATE 42832).
correlation-name
Specifies a correlation name that can be used within case-expression to designate the table.
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 table (SQLSTATE 42703). A mask must not already exist for the column (SQLSTATE 428HC). The column must not be any of the following columns:
  • A LOB column or a distinct type column that is based on a LOB (SQLSTATE 42962).
  • An XML column (SQLSTATE 42962).
  • A column referenced in an expression that defines a generated column (SQLSTATE 428HB).
RETURN case-expression
Specifies a CASE expression to be evaluated to determine the value to return for the column (SQLSTATE 42601). The result of the CASE expression is returned in place of the column value in a row. The result data type, null attribute, and length attribute of the CASE expression must be identical or promotable to those of column-name (SQLSTATE 428HB). 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 data type. The CASE expression must not reference any of the following objects or elements (SQLSTATE 428HB):
  • A created global temporary table or a declared global temporary table.
  • A shadow table.
  • An external table.
  • A nickname.
  • A table function.
  • A method.
  • A parameter marker (SQLSTATE 42601).
  • A user-defined function that is defined as not secure.
  • A function or expression (such as row change expression, sequence expression) that is non-deterministic or has an external action.
  • An XMLQUERY scalar function.
  • An XMLEXISTS predicate.
  • An OLAP specification.
  • A * or name.* in a SELECT clause.
  • A pseudo-column.
  • An aggregate function without specifying the SELECT clause.
  • A view that includes any of the previously listed restrictions in its definition.
If the CASE expression references tables for which row or column access control is currently activated, access control from those tables are not cascaded. See the Notes section for details.
ENABLE or DISABLE
Specifies that the column mask is to be enabled or disabled for column access control. The default is DISABLE.
DISABLE
Specifies that the column mask is to be disabled for column access control. If column access control is not currently activated for the table, the column mask will remain ineffective when column access control is activated for the table.
ENABLE
Specifies that the column mask is to be enabled for column access control. If column access control is not currently activated for the table, the column mask will become effective when column access control is activated for the table. If column access control is currently activated for the table, the column mask becomes effective immediately and all packages and dynamic cached statements that reference the table are invalidated.

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, and ORDER BY. The rows returned in the final result table remain the same, except that the values in the resulting rows might be masked by the column masks. As such, if the masked column also appears in an ORDER BY sort-key, the order is based on the original column values 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 SELECT DISTINCT. 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, applying a column mask on column SSN might change the result of aggregate function COUNT(DISTINCT SSN) because the DISTINCT operation is performed on the masked values. On the other hand, if the expression in the query is the same as the expression used to mask the column value in the column mask definition, the result of the expression might remain unchanged. For example, the expression in the query is 'XXX-XX-' || SUBSTR(SSN, 8, 4) and the same expression appears in the column mask definition. In this particular example, you can replace the expression in the query with column SSN to avoid the same expression getting evaluated twice.

A column mask is created as a stand alone object without knowing all of the contexts in which it might be used. To mask a column value in the final result table, the column mask definition is merged into a query by the database manager. When the column mask definition 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 might return an error (SQLSTATE 428HD). When this happens, either the statement needs to be modified or the column mask must be dropped or recreated with a different definition. See the ALTER TABLE statement description for those situations where a bind time error might be issued for the statement.

If the column is not nullable, its column mask definition will not consider a null value for the column. After column access control is activated for the target table, if the target table is the null-padded table in an outer join operation, the column value in the final result table might be a null. To ensure the column mask has the ability to mask a null value, when the database manager merges the column mask definition into the query, if the target table is the null-padded table in an outer join operation, "WHEN target-column IS NULL THEN NULL" will be added as the first WHEN clause to the column mask definition. This forces a null value to be always masked to a null. For a nullable column, this takes away the ability to mask a null value to something else but it is an acceptable restriction from security and usability standpoints.

When a column is used to derive the new value for an INSERT, UPDATE, MERGE, or a SET transition-variable assignment statement, the original column value, not the masked value, is used to derive the new value. If the column has a column mask, that column mask is applied to ensure 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 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 (SQLSTATE 428HD). If there is a requirement for masked data to be inserted into a table, it can be done by first assigning the data to a variable. For example, an array variable can be created with the array elements having a row data type. Table data with column masks applied can be assigned to the array variable, which can then be used to insert the data into some other table. The rules that are used to apply column masks in to derive the new values follow the same rules described previously for the final result table of a query. See the INSERT, UPDATE, and MERGE statements for how the column masks are used to affect the insert and update operation.

See the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause for information about how to activate column access control for the table and how a column mask is applied.

Notes

  • Column masks that are created before column access control is activated for a table: 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 database 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 the database manager 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.

    Creating column masks before activating column access control for a table is the recommended sequence to avoid multiple invalidations of packages and dynamic cached statements that reference the table.

  • Column masks that are created after column access control is activated for a table: 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 the database manager to the statement. Any disabled column masks remain 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 can 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.
  • Consideration for database limits: If the data manipulation statement already approaches some database limits in the statement, the more enabled column masks and enabled row permissions are created, the more likely they might affect some limits. 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.
  • Column masks that are enabled but in the invalid state: If a column mask is enabled for column access control but its state is set to invalid, access to the table on which the column mask is defined is blocked until this situation is resolved (SQLSTATE 560D0).
  • Column masks that return data which is not assignable to the column the mask is defined on: A column mask can be defined so it can return data which is not assignable to the data type of the column the mask is defined on. When this occurs, the CREATE MASK statement is successful but a cast error will be reported when the mask is applied in a user query.

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 only the last four characters of the social security number. Peter who is neither role 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;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
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 getting 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;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
SELECT 'XXX-XX-' || SUBSTR(SSN,8,4) FROM EMPLOYEE WHERE EMPNO = 123456;
  • Example 3: The California 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, to show whether 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;
ALTER TABLE LIBRARY_USAGE ACTIVATE COLUMN ACCESS CONTROL;
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;
CREATE MASK BONUS_MASK ON EMPLOYEE
  FOR COLUMN BONUS RETURN 
    CASE WHEN (BONUS > 5000) THEN NULL
         ELSE BONUS
    END
  ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL;
SELECT SALARY FROM EMPLOYEE WHERE EMPNO = 123456;