Mask personal data in Databricks: column masks, row filters and dynamic views

Part 1 put the shop's customers in silver.customers, with their emails. Run catalog.py from there first if you are starting here. Part 2 decided who reads which schema. That is not enough for a column: the support team needs the emails, and the analysts who read the same customers should not see them.

A mask is a function

A mask is a SQL function that takes the column's value and returns what the current reader may see. On Databricks it asks whether the reader is in a group, with is_account_group_member('pii_readers'). That function exists only on Databricks, so on your computer a small function stands in for it, and you can switch it to see both readers. Save this as masks.py:

"""A mask function for emails, used by a view, tried for a reader without and with access."""
from delta_session import delta_spark

spark = delta_spark()


def allow_pii(allowed: bool):
    """Stands in for is_account_group_member('pii_readers'), which only Databricks has."""
    spark.sql(f"CREATE OR REPLACE FUNCTION silver.can_see_pii() RETURNS BOOLEAN RETURN {allowed}")


allow_pii(False)
spark.sql("""
    CREATE OR REPLACE FUNCTION silver.mask_email(email STRING)
    RETURNS STRING
    RETURN CASE WHEN silver.can_see_pii() THEN email
                ELSE regexp_replace(email, '^[^@]+', '***') END
""")
spark.sql("""
    CREATE OR REPLACE VIEW silver.customers_safe AS
    SELECT customer_id, name, silver.mask_email(email) AS email, country
    FROM silver.customers
""")
spark.table("silver.customers_safe").orderBy("customer_id").show(truncate=False)

allow_pii(True)
spark.table("silver.customers_safe").orderBy("customer_id").show(truncate=False)

+-----------+----+---------------+-------+
|customer_id|name|email          |country|
+-----------+----+---------------+-------+
|C01        |Ana |***@example.com|ES     |
|C02        |Ben |***@example.org|IE     |
|C03        |Chen|***@example.net|DE     |
+-----------+----+---------------+-------+

+-----------+----+---------------------+-------+
|customer_id|name|email                |country|
+-----------+----+---------------------+-------+
|C01        |Ana |ana.silva@example.com|ES     |
|C02        |Ben |ben.byrne@example.org|IE     |
|C03        |Chen|chen.li@example.net  |DE     |
+-----------+----+---------------------+-------+

The same view gives 2 answers, because the function runs again for every query. The mask keeps the domain, which is often enough for support and analysis, and drops the part that identifies the person. A name is personal data too; mask it the same way if readers do not need it.

On Databricks: a column mask

In Unity Catalog the function lives in a schema like a table, and you attach it to the column itself:

CREATE OR REPLACE FUNCTION shop.silver.mask_email(email STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN email
            ELSE regexp_replace(email, '^[^@]+', '***') END;

ALTER TABLE shop.silver.customers ALTER COLUMN email SET MASK shop.silver.mask_email;

From then on, every query on the table runs the function as each row is read, with the identity of whoever runs the query. A notebook, a dashboard and a job all get the masked value unless their user is in pii_readers; there is no way around it through the table. Each column takes 1 mask, and ALTER COLUMN email DROP MASK removes it.

To attach a mask you need to own the table, or have MANAGE and SELECT on it, and EXECUTE on the function. It works on SQL warehouses, on compute in standard access mode from Databricks Runtime 12.2 LTS and in dedicated access mode from 15.4 LTS. It has costs worth knowing: a table with a mask or a row filter cannot be read with time travel or cloned, and a mask cannot be put on a view.

Row filters

A row filter is the same idea for rows: a function that returns true for the rows the reader may see. Support staff in Ireland see Irish customers, the data team sees all:

CREATE OR REPLACE FUNCTION shop.silver.customers_for_reader(country STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('pii_readers')
    OR (is_account_group_member('support_ie') AND country = 'IE');

ALTER TABLE shop.silver.customers SET ROW FILTER shop.silver.customers_for_reader ON (country);

A reader in neither group gets no rows at all. ALTER TABLE shop.silver.customers DROP ROW FILTER removes it.

Or a dynamic view

A dynamic view puts the logic in a view instead: masks.py above built one, and on Databricks its function would check the group with is_account_group_member. Readers get SELECT on the view, not on the table.

That last condition is the whole difference. A view protects only queries on the view; anyone who can read silver.customers itself sees every email. Databricks recommends not granting readers the tables a dynamic view reads, which part 2 did by granting only gold. Use a view to publish a curated slice, a join or a reshaped table; use a mask when a column must be protected wherever its table is read.

Tags and policies, for many tables

1 mask on 1 column is fine for the shop. With hundreds of tables, label the columns instead:

ALTER TABLE shop.silver.customers ALTER COLUMN email SET TAGS ('pii' = 'email');

Each catalog lists its tags in information_schema.column_tags. Databricks' attribute-based policies go a step further: a policy attached to a catalog or schema masks or filters the columns and tables whose tags match, and Databricks recommends them for consistent rules across many tables. Part 4 shows how to find out who read the data and where it went.