Databricks Unity Catalog: Protect PII with Column Masks while Preserving Approved Analyst Access

This tutorial demonstrates how to utilize Unity Catalog’s column masking feature to protect Personally Identifiable Information (PII) within a Databricks SQL environment while maintaining access for authorized analysts.

Script 1: Initial Setup and Masking

This script creates a simple table with PII and applies a column mask to hide the sensitive information for all users except the database administrator.

CREATE TABLE IF NOT EXISTS customer_data (
    customer_id STRING,
    first_name STRING,
    last_name STRING,
    email STRING
)
WITH CONNECTION_OPTIONS (
    ROW_LEVEL_SECURITY = 'ENABLED'
)
INCLUDED_SCHEMA DATABASE customer_schema;

ALTER TABLE customer_data
SET VARIANT(mask_column = 'first_name');

SELECT COUNT() FROM customer_data;

Explanation:

• `CREATE TABLE …`: This creates a table named `customer_data` with columns for `customer_id`, `first_name`, `last_name`, and `email`.
• `WITH CONNECTION_OPTIONS`: This enables Row Level Security (RLS).
• `ALTER TABLE … SET VARIANT(mask_column = ‘first_name’)`: This applies a column mask to the `first_name` column. Only the database administrator can access this column. The other columns are accessible to all users.
• `SELECT COUNT()`: This is a simple validation query to ensure the table was created and the mask was applied.

The `mask_column` setting in the VARIANT definition specifies the column to be masked. The RLS policy ensures that only the administrator can execute queries that access this masked column.

3

Script 2: Analyst Access and Querying

This script demonstrates how analysts can query the `customer_data` table without accessing the masked `first_name` column.

CREATE TABLE IF NOT EXISTS customer_data (
    customer_id STRING,
    first_name STRING,
    last_name STRING,
    email STRING
)
WITH CONNECTION_OPTIONS (
    ROW_LEVEL_SECURITY = 'ENABLED'
)
INCLUDED_SCHEMA DATABASE customer_schema;

ALTER TABLE customer_data
SET VARIANT(mask_column = 'first_name');

INSERT INTO customer_data (customer_id, first_name, last_name, email)
VALUES
('123', 'Alice', 'Smith', 'alice.smith@example.com'),
('456', 'Bob', 'Johnson', 'bob.johnson@example.com');

SELECT COUNT() FROM customer_data;

Explanation:

• `INSERT INTO …`: Populates the table with sample data.
• `SELECT COUNT()`: Verifies data insertion and that the table exists.

An analyst can now run queries against the table, but they will not see the values in the `first_name` column due to the column masking applied in the previous script.

2

Script 3: Advanced Masking with Different Masking Types

This script demonstrates how to utilize different masking types for masking the `email` column, which is a sensitive column.

CREATE TABLE IF NOT EXISTS customer_data (
    customer_id STRING,
    first_name STRING,
    last_name STRING,
    email STRING
)
WITH CONNECTION_OPTIONS (
    ROW_LEVEL_SECURITY = 'ENABLED'
)
INCLUDED_SCHEMA DATABASE customer_schema;

ALTER TABLE customer_data
SET VARIANT(mask_column = 'first_name');
ALTER TABLE customer_data
SET VARIANT(mask_column = 'email', mask_type = 'EMAIL');

SELECT COUNT() FROM customer_data;

Explanation:

• `ALTER TABLE customer_data SET VARIANT(mask_column = ’email’, mask_type = ‘EMAIL’)`: This line applies a specific mask type (‘EMAIL’) to the `email` column. This type of masking ensures that only the email format is visible, obscuring the actual email addresses.
• `SELECT COUNT()`: This is a simple validation query to ensure the table was created and the mask was applied.

The `mask_type` parameter allows you to choose a more suitable masking approach for different data types.

3