Column mask clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above check marked yes Unity Catalog

Preview

This feature is in Public Preview.

Specifies a function which is applied to a column whenever rows are fetched from the table. All future queries from that column will receive the result of evaluating that function over the column in place of the column’s original value. This can be useful for fine-grained access control purposes wherein the function can inspect the identity and/or group memberships of the invoking user in order to decide whether to redact the value.

You can add column masks when you:

..important:

The mask is applied as soon as each row is fetched from the data source.
Any expressions, predicates, or ordering are applied after the masking.
For example, joining between the masked column against another a column from
another table will use the masked values for the join comparison.

For more information on how to use column masks see Filter sensitive table data with row filters and column masks.

Syntax 

MASK func_name [ USING COLUMNS ( other_column_name [, ...] ) ]

Parameters

  • func_name

    A scalar SQL UDF with at least one parameter.

    The first parameter of the SQL UDF maps 1:1 with the masked column. The masked column’s type must be castable to the SQL UDF parameter type. If func_name requires more parameters, arguments must be provided by the USING COLUMNS clause.

    The return type of the function must be castable to the data type of the masked column.

  • other_column_name

    Optionally specifies additional columns of the masked column’s table to pass to func_name. Each other_column_name must be castable to the corresponding parameter of func_name.

    Use a column mask to selectively anonymize the value of column_identifier based on the user executing a query against table_name, the value of column_identifier and the optional other_columns.

Examples

You can find more examples in Filter sensitive table data with row filters and column masks.

-- Create a table with a masked column
> CREATE FUNCTION mask_ssn(ssn STRING) RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;
> CREATE TABLE persons(name STRING, ssn STRING MASK ssn);
> INSERT INTO persons('James', '123-45-6789';

-- As a non-member of 'HumanResourceDept'
> SELECT * FROM persons;
  James  ***-**-****

-- As a member of 'HumanResourceDept'
> SELECT * FROM persons;
  James  123-45-6789