Protect sensitive data with ABAC policies
In this lab, you use attribute-based access control (ABAC) to protect a sample employees table for a fictional company. Without ABAC policies, any user with SELECT on the table reads raw salaries, Social Security numbers (SSNs), and full card numbers. Unity Catalog enforces ABAC policies on every query that runs on supported compute, from any tool.
You tag the sensitive columns, and then create policies to do the following:
- Hide salaries from everyone except HR and finance.
- Redact SSNs for everyone except HR and compliance.
- Mask email addresses so that only the domain shows, with hashing as an advanced option when analysts need to join records on email.
- Show only the last 4 digits of card and phone numbers.
- Filter rows, so that a regional manager sees only the employees in their own region.
You define the policies on the schema and match columns by tag, so the policies also apply to any new table that has the same tags.
Requirements
To complete this lab, you need the following compute, privileges, and permissions:
- A SQL warehouse, to run the statements in this lab in the SQL editor. See Compute requirements.
- The
CREATE CATALOGprivilege on the metastore, or theCREATE SCHEMAprivilege on an existing catalog. - The
CREATEpermission on governed tags at the account level. Account admins and workspace admins have this permission by default. See Create and manage governed tags. - The
ASSIGNpermission on thepiigoverned tag, so that you can apply it to columns. Account admins haveASSIGNby default. If you create thepiitag, you get theMANAGEpermission on the tag, which includesASSIGN. See Manage permissions on governed tags. - The
APPLY TAGprivilege on the tables whose columns you tag. See Unity Catalog privileges reference. - Permission to create account groups in the account console, or existing groups that you can use instead.
Protect the sample table
Create the sample table, tag its sensitive columns, and then create the masking and row filter policies.
Set up the sample table
Create a catalog for this lab, dac_lab, so that the lab doesn't affect your other catalogs. For how to organize catalogs for your own data, see Organize data for a small organization.
If you can't create a catalog, use an existing catalog where you have the CREATE SCHEMA privilege, and replace dac_lab with its name in every statement in this lab. You might not be able to create a catalog if you don't have the CREATE CATALOG privilege, or if your metastore has no metastore-level storage. The statements in this lab replace the objects in the hr_lab schema each time you run them, so confirm that the catalog doesn't already have a schema named hr_lab.
Create a schema, and an employees table with salary, SSN, email, card number, phone, and region columns. CREATE OR REPLACE TABLE resets the table to the same 4 rows each time you rerun the lab, so rows aren't duplicated.
CREATE CATALOG IF NOT EXISTS dac_lab;
USE CATALOG dac_lab;
CREATE SCHEMA IF NOT EXISTS hr_lab;
-- Tables and functions are stored here.
USE SCHEMA hr_lab;
CREATE OR REPLACE TABLE employees (
employee_id INT,
name STRING,
region STRING,
email STRING,
phone STRING,
ssn STRING,
card_number STRING,
salary DOUBLE
);
INSERT INTO employees VALUES
(1, 'Alice', 'EMEA', 'alice@example.com', '+1-415-555-0101', '123-45-6789', '4111-1111-1111-1001', 95000.0),
(2, 'Bob', 'AMER', 'bob@example.com', '+1-212-555-0102', '234-56-7890', '4111-1111-1111-1002', 87000.0),
(3, 'Carlos', 'APAC', 'carlos@example.com', '+1-650-555-0103', '345-67-8901', '4111-1111-1111-1003', 91000.0),
(4, 'Diana', 'EMEA', 'diana@example.com', '+1-312-555-0104', '456-78-9012', '4111-1111-1111-1004', 102000.0);
To verify the setup, run SELECT * FROM employees. The query returns 4 rows with all columns populated.
Tag the sensitive columns
ABAC policies match columns by governed tag, not by column name or by an ungoverned tag. See Governed tags. If your account already has a pii governed tag, see Troubleshooting. Before you tag any column, create the pii governed tag and its allowed values:
- In your Databricks workspace, click Catalog, click Govern, and then click Governed Tags.
- Click Create governed tag.
- For the tag key, enter
pii. - For allowed values, enter
salary,ssn,email,card_number,phone, andregion. Users can assign only these values to thepiitag. - Click Create.
Tag each sensitive column with its pii value:
ALTER TABLE employees ALTER COLUMN salary SET TAGS ('pii' = 'salary');
ALTER TABLE employees ALTER COLUMN ssn SET TAGS ('pii' = 'ssn');
ALTER TABLE employees ALTER COLUMN email SET TAGS ('pii' = 'email');
ALTER TABLE employees ALTER COLUMN card_number SET TAGS ('pii' = 'card_number');
ALTER TABLE employees ALTER COLUMN phone SET TAGS ('pii' = 'phone');
ALTER TABLE employees ALTER COLUMN region SET TAGS ('pii' = 'region');
To verify the tags, run the following query. It returns six rows, one for each tagged column:
SELECT column_name, tag_name, tag_value
FROM dac_lab.information_schema.column_tags
WHERE schema_name = 'hr_lab' AND table_name = 'employees';
Set up groups and grants
Create the groups that the policies in this lab use, add test users to them, and grant the test users access to the table:
-
In the account console, click User management, and then on the Groups tab, create the following account groups:
hr_admin: Sees real salaries and SSNs, and all rows.finance: Sees real salaries.compliance: Sees real SSNs, and all rows.data_owners: Sees full email addresses.fraud_team: Sees full card numbers.support_ops: Sees full phone numbers.region_emea,region_amer, andregion_apac: See only the rows for their region.
The policies check account-level group membership, so groups that exist only in a workspace don't work. See Manage Databricks groups.
To use existing groups instead, replace these group names in every policy in this lab.
-
Add three test users, so that you can check the results later in this lab:
- One user in
region_emeaonly. - One user in
hr_adminonly. - One user in no groups.
- One user in
-
Grant all account users access to the table, so that your test users can query it. ABAC policies change what users see in a table that they can already query, but they don't grant access to it. Run the following statement as the catalog owner:
SQLGRANT USE CATALOG, USE SCHEMA, SELECT ON CATALOG dac_lab TO `account users`;
For simple testing, this lab grants access to all account users. In production, grant SELECT only to the groups that need the table, and use ABAC policies to set what each group sees.
Create the masking functions and policies
For each sensitive column, you create a function that returns the masked value, and a policy that applies the function to columns with the matching tag. The policy's TO and EXCEPT clauses set which users see the masked value, so the function only has to return it.
This lab uses redaction for salaries and SSNs, partial masking for email addresses, card numbers, and phone numbers, and Secure Hash Algorithm 256 (SHA-256) hashing as an option for email addresses. To choose a strategy for your own data, see Choose a masking strategy.
Mask salaries for everyone except HR and finance
Redact salaries, so that users outside hr_admin and finance see NULL:
CREATE OR REPLACE FUNCTION mask_salary(salary DOUBLE)
RETURNS DOUBLE
RETURN NULL;
CREATE OR REPLACE POLICY policy_salary
ON SCHEMA hr_lab
COMMENT 'Hide salary from everyone except HR and finance'
COLUMN MASK mask_salary
TO `account users` EXCEPT hr_admin, finance
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'salary') AS salary
ON COLUMN salary;
Redact SSNs for everyone except HR and compliance
Redact SSNs, so that users outside hr_admin and compliance see XXX-XX-XXXX. Don't hash SSNs instead. SSNs come from a small, predictable set, so anyone can reverse an unkeyed SHA-256 hash of an SSN by hashing every possible SSN.
CREATE OR REPLACE FUNCTION mask_ssn(ssn STRING)
RETURNS STRING
RETURN 'XXX-XX-XXXX';
CREATE OR REPLACE POLICY policy_ssn
ON SCHEMA hr_lab
COMMENT 'Redact SSN from everyone except HR and compliance'
COLUMN MASK mask_ssn
TO `account users` EXCEPT hr_admin, compliance
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'ssn') AS ssn
ON COLUMN ssn;
Mask email addresses with domain masking or a SHA-256 hash
Mask email addresses, so that users outside data_owners see only the domain, such as ***@example.com. Option B hashes the address instead, so that analysts can join datasets on email without seeing the addresses. Anyone with a list of likely addresses can reverse an unkeyed SHA-256 hash of an email address by hashing each address and comparing the results. Use Option B only when you need the join, and treat the hashed column as sensitive.
Run Option A for this lab. To see the hash output instead, comment out Option A, uncomment Option B, and run the code again.
-- Option A: domain-only mask (default)
CREATE OR REPLACE FUNCTION mask_email(email STRING)
RETURNS STRING
RETURN CONCAT('***@', SPLIT(email, '@')[1]);
CREATE OR REPLACE POLICY policy_email
ON SCHEMA hr_lab
COMMENT 'Show only the email domain to everyone except data owners'
COLUMN MASK mask_email
TO `account users` EXCEPT data_owners
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'email') AS email
ON COLUMN email;
-- Option B: SHA-256 hash (advanced, preserves cross-dataset joins)
-- CREATE OR REPLACE FUNCTION hash_email(email STRING)
-- RETURNS STRING
-- RETURN SHA2(email, 256);
--
-- CREATE OR REPLACE POLICY policy_email
-- ON SCHEMA hr_lab
-- COLUMN MASK hash_email
-- TO `account users` EXCEPT data_owners
-- FOR TABLES
-- MATCH COLUMNS has_tag_value('pii', 'email') AS email
-- ON COLUMN email;
Show only the last 4 digits of card numbers
Mask card numbers, so that users outside fraud_team see only the last 4 digits, such as ****-****-****-1001. Members of fraud_team see full card numbers, so that they can match transactions.
CREATE OR REPLACE FUNCTION mask_card(card_number STRING)
RETURNS STRING
RETURN CONCAT('****-****-****-', RIGHT(REGEXP_REPLACE(card_number, '[^0-9]', ''), 4));
CREATE OR REPLACE POLICY policy_card
ON SCHEMA hr_lab
COMMENT 'Show only the last 4 digits of a card number, except for the fraud team'
COLUMN MASK mask_card
TO `account users` EXCEPT fraud_team
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'card_number') AS card_number
ON COLUMN card_number;
Show only the last 4 digits of phone numbers
Mask phone numbers, so that users outside support_ops see only the country code and the last 4 digits, such as +1-XXX-XXX-0101. Members of support_ops see full phone numbers, so that they can verify a caller's identity.
CREATE OR REPLACE FUNCTION mask_phone(phone STRING)
RETURNS STRING
RETURN CONCAT(SPLIT(phone, '-')[0], '-XXX-XXX-', RIGHT(REGEXP_REPLACE(phone, '[^0-9]', ''), 4));
CREATE OR REPLACE POLICY policy_phone
ON SCHEMA hr_lab
COMMENT 'Keep the last 4 digits of phone visible, except for support'
COLUMN MASK mask_phone
TO `account users` EXCEPT support_ops
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'phone') AS phone
ON COLUMN phone;
Verify the column masks
To verify the masks, sign in to the workspace as the test user in no groups, and run SELECT * FROM dac_lab.hr_lab.employees. The query returns the following values in every row:
Column | Expected value |
|---|---|
|
|
|
|
|
|
|
|
|
|
If you ran Option B for email, the email column shows a 64-character hexadecimal string. The same email address always produces the same string, so you can join this table to another table on SHA2(email, 256).
Add a row filter for regional managers
Column masks control what a visible row shows. A row filter controls which rows are visible at all. In this lab, regional managers see only the employees in their own region, and HR and compliance see all rows.
Return to the session that you used to create the lab objects.
The row filter function compares the user's regional group with each row's region value, so one function and one policy cover all three regions.
CREATE OR REPLACE FUNCTION filter_region(region STRING)
RETURNS BOOLEAN
RETURN
(is_account_group_member('region_emea') AND region = 'EMEA')
OR (is_account_group_member('region_amer') AND region = 'AMER')
OR (is_account_group_member('region_apac') AND region = 'APAC');
CREATE OR REPLACE POLICY policy_region
ON SCHEMA hr_lab
COMMENT 'Regional managers see only their own region; HR and compliance see everyone'
ROW FILTER filter_region
TO `account users` EXCEPT hr_admin, compliance
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'region') AS region
USING COLUMNS (region);
hr_admin and compliance are in the policy's EXCEPT clause, so the row filter doesn't apply to them, and they see all 4 rows. Other users see only the rows for which the function returns TRUE. A user who isn't in a regional group or an exempt group sees 0 rows, and the query doesn't return an error.
Verify the row filter and column masks
The row filter and the column masks apply together. To verify both, sign in as each test user from Set up groups and grants, and run SELECT * FROM dac_lab.hr_lab.employees:
Test user | Rows returned | Salary and SSN values |
|---|---|---|
In | Alice and Diana (2 rows) | Masked |
In | All 4 rows | Real values |
In no groups | 0 rows | None, because the query returns no rows |
Verify automatic inheritance
Return to the session that you used to create the lab objects.
Create a second table, and tag its sensitive columns with the same pii values. The policies from Create the masking functions and policies apply to the new table, so you don't need new functions or policies.
CREATE OR REPLACE TABLE contractors (
contractor_id INT,
name STRING,
email STRING,
ssn STRING,
salary DOUBLE
);
INSERT INTO contractors VALUES
(1, 'Eve', 'eve@example.org', '567-89-0123', 75000.0);
ALTER TABLE contractors ALTER COLUMN salary SET TAGS ('pii' = 'salary');
ALTER TABLE contractors ALTER COLUMN ssn SET TAGS ('pii' = 'ssn');
ALTER TABLE contractors ALTER COLUMN email SET TAGS ('pii' = 'email');
To verify inheritance, sign in as the test user in no groups, and run SELECT salary, ssn, email FROM dac_lab.hr_lab.contractors. The query returns NULL, XXX-XX-XXXX, and ***@example.org. The row filter doesn't apply, because contractors has no column with the pii tag value region, so the query returns Eve's row.
You define the policies one time, on the schema. For each new table, the only step is to tag its sensitive columns.
Clean up
Return to the session that you used to create the lab objects.
Remove the policies, functions, tables, and schema that you created in this lab. The USE statements set the catalog and schema, so the cleanup also works in a new session.
USE CATALOG dac_lab;
USE SCHEMA hr_lab;
-- Drop policies
DROP POLICY policy_salary ON SCHEMA hr_lab;
DROP POLICY policy_ssn ON SCHEMA hr_lab;
DROP POLICY policy_email ON SCHEMA hr_lab;
DROP POLICY policy_card ON SCHEMA hr_lab;
DROP POLICY policy_phone ON SCHEMA hr_lab;
DROP POLICY policy_region ON SCHEMA hr_lab;
-- Drop functions. IF EXISTS skips hash_email if you didn't run Option B for email.
DROP FUNCTION IF EXISTS mask_salary;
DROP FUNCTION IF EXISTS mask_ssn;
DROP FUNCTION IF EXISTS hash_email;
DROP FUNCTION IF EXISTS mask_email;
DROP FUNCTION IF EXISTS mask_card;
DROP FUNCTION IF EXISTS mask_phone;
DROP FUNCTION IF EXISTS filter_region;
-- Drop tables and schema
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS contractors;
DROP SCHEMA IF EXISTS hr_lab;
To verify the cleanup, run SHOW SCHEMAS IN dac_lab, or in the catalog that you used instead. The output doesn't list hr_lab.
If you created dac_lab for this lab in Set up the sample table, drop it. If you used an existing catalog, skip this statement and keep the catalog:
DROP CATALOG IF EXISTS dac_lab CASCADE;
Cleanup doesn't delete the nine groups and three test users that you created in Set up groups and grants, or the pii governed tag. If you don't need them, delete the groups and users in the account console, and delete the tag on the Governed Tags page. If you used an existing catalog, also review the grant to account users from that step, and revoke it if account users didn't already have that access.
Troubleshooting
Your account already has a pii governed tag
Governed tags are account-level, so another team might have already created pii. If its allowed values don't include the six values that this lab uses, you can't assign them to columns, and CREATE POLICY fails with an error that says the tag value salary is invalid for the key pii.
To fix this, open the pii tag on the Governed Tags page, and click Add value for each missing value. You need the MANAGE permission on the tag. Don't use ALTER GOVERNED TAG pii SET VALUES, because it replaces the tag's existing values, which other teams might use. See Create and manage governed tags.
If you can't edit the tag, use a different key, such as pii_lab, and replace pii with it in every statement in this lab.
Next steps
-
- Connect Databricks to your data
- Connect your cloud object storage, databases, and software as a service (SaaS) applications to Unity Catalog
-
- Common patterns for row filtering and column masking
- Learn reusable patterns for row filter and column mask policies