Skip to main content
Last updated on

Tutorial: Configure ABAC with SQL

This tutorial shows you how to configure row filter and column mask ABAC policies in Unity Catalog using SQL. For the Catalog Explorer UI-based version, see Tutorial: Configure ABAC.

In this example, an analytics team cannot access EU customer records, and SSNs are always masked. Customers who have consented to data sharing have their full email shown. Others see a masked version only.

This tutorial includes the following steps:

  1. Create governed tags
  2. Create a Unity Catalog catalog, schema, and table
  3. Apply governed tags to columns
  4. Create a UDF to detect EU addresses
  5. Create a row filter policy
  6. Test the row filter
  7. Create a UDF to mask SSNs
  8. Create a column mask policy
  9. Test the column mask

After completing these steps, you can optionally extend the tutorial with conditional email masking (steps 10–12).

Prerequisites​

  • Databricks Runtime 16.4 or above, or serverless compute.
  • Account admin or workspace admin permissions (to create governed tags).
  • MANAGE permission on the target catalog or schema.
  • EXECUTE on the UDFs.

Compute running older runtimes cannot access tables secured by ABAC.

Step 1: Create governed tags​

Governed tags are key-value pairs defined at the account level. ABAC policies use them to discover which columns to filter or mask. In this tutorial, you create two governed tags:

  • A pii tag with three allowed values: ssn, address, and email
  • A consent key-only tag (no allowed values) to identify consent columns

To create a governed tag, you must have the governed tag CREATE permission at the account level. Account and workspace admins have CREATE by default.

  1. In your Databricks workspace, click Data icon. Catalog.
  2. Click the Shield icon. Govern button.
  3. In the dropdown menu, click Governed Tags.
  4. Click Create governed tag.
  5. For the tag key, enter pii.
  6. Enter a description for the governed tag.
  7. For allowed values, enter: ssn, address, and email. Only these values can be assigned to this tag key.
  8. Click Create.
  9. Repeat steps 4–8 to create a second governed tag with the key consent. Leave the allowed values empty (key-only tag).
warning

Tag data is stored as plain text and may be replicated globally. Do not use tag names, values, or descriptors that could compromise the security of your resources. For example, do not use tag names, values or descriptors that contain personal or sensitive information.

Step 2: Create the customers table​

Create a catalog, schema, and table with customer profiles. The has_consent column is used later for conditional email masking. Customers who have given consent (TRUE) have their full email shown.

Run the following commands in a notebook attached to compute on Databricks Runtime 16.4 or above:

SQL
-- Create catalog (if not already exists)
CREATE CATALOG IF NOT EXISTS abac_tutorial;
USE CATALOG abac_tutorial;

-- Create schema
CREATE SCHEMA IF NOT EXISTS customers;
USE SCHEMA customers;
SQL
CREATE OR REPLACE TABLE profiles (
    first_name STRING,
    last_name STRING,
    email STRING,
    phone_number STRING,
    home_address STRING,
    ssn_number STRING,
    has_consent BOOLEAN
);
SQL
INSERT INTO profiles (first_name, last_name, email, phone_number, home_address, ssn_number, has_consent)
VALUES
('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St, NY', '123-45-6789', TRUE),
('Jane', 'Smith', 'jane.smith@example.com', '234-567-8901', '456 Oak St, CA', '234-56-7890', FALSE),
('Alice', 'Johnson', 'alice.j@example.com', '345-678-9012', '789 Pine St, TX', '345-67-8901', TRUE),
('Bob', 'Brown', 'bob.brown@example.com', '456-789-0123', '321 Maple St, FL', '456-78-9012', FALSE),
('Charlie', 'Davis', 'charlie.d@example.com', '567-890-1234', '654 Cedar St, IL', '567-89-0123', TRUE),
('Emily', 'White', 'emily.w@example.com', '678-901-2345', '987 Birch St, WA', '678-90-1234', FALSE),
('Frank', 'Miller', 'frank.m@example.com', '789-012-3456', '741 Spruce St, WA', '789-01-2345', TRUE),
('Grace', 'Wilson', 'grace.w@example.com', '890-123-4567', '852 Elm St, NV', '890-12-3456', TRUE),
('Hank', 'Moore', 'hank.moore@example.com', '901-234-5678', '963 Walnut St, CO', '901-23-4567', FALSE),
('Ivy', 'Taylor', 'ivy.taylor@example.com', '012-345-6789', '159 Aspen St, AZ', '012-34-5678', TRUE),
('Liam', 'Connor', 'liam.c@example.com', '111-222-3333', '12 Abbey Street, Dublin, Ireland EU', '111-22-3333', TRUE),
('Sophie', 'Dubois', 'sophie.d@example.com', '222-333-4444', '45 Rue de Rivoli, Paris, France Europe', '222-33-4444', FALSE),
('Hans', 'Müller', 'hans.m@example.com', '333-444-5555', '78 Berliner Str., Berlin, Germany E.U.', '333-44-5555', TRUE),
('Elena', 'Rossi', 'elena.r@example.com', '444-555-6666', '23 Via Roma, Milan, Italy Europe', '444-55-6666', FALSE),
('Johan', 'Andersson', 'johan.a@example.com', '555-666-7777', '56 Drottninggatan, Stockholm, Sweden EU', '555-66-7777', TRUE);

Step 3: Add governed tags to columns​

Tag the ssn_number, home_address, and email columns with the pii governed tag. ABAC policies match columns by tag, not by name.

The has_consent column is tagged with the consent governed tag. This is required for the consent-aware masking policy in Step 11, which passes has_consent to the UDF via USING COLUMNS.

SQL
ALTER TABLE abac_tutorial.customers.profiles
ALTER COLUMN ssn_number
SET TAGS ('pii' = 'ssn');

ALTER TABLE abac_tutorial.customers.profiles
ALTER COLUMN home_address
SET TAGS ('pii' = 'address');

ALTER TABLE abac_tutorial.customers.profiles
ALTER COLUMN email
SET TAGS ('pii' = 'email');

ALTER TABLE abac_tutorial.customers.profiles
ALTER COLUMN has_consent
SET TAGS ('consent' = '');

Step 4: Create a UDF to detect EU addresses​

This UDF is passed the value of every column tagged pii = address and returns:

  • FALSE if the address contains EU, E.U., or Europe — the row is hidden.
  • TRUE otherwise — the row is shown.
SQL
CREATE OR REPLACE FUNCTION is_not_eu_address(address STRING)
RETURNS BOOLEAN
RETURN (
    SELECT CASE
        WHEN LOWER(address) LIKE '%eu%'
          OR LOWER(address) LIKE '%e.u.%'
          OR LOWER(address) LIKE '%europe%'
        THEN FALSE
        ELSE TRUE
    END
);
note

This is a simplified check for demonstration purposes. In production, use a more robust method such as a country code column or a lookup table to determine region.

Step 5: Create a row filter policy​

To create a policy, you must have MANAGE on the object or ownership of the object. To add a UDF to a policy, you must have EXECUTE on the UDF.

SQL
CREATE POLICY hide_eu_customers
ON SCHEMA abac_tutorial.customers
ROW FILTER is_not_eu_address
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'address') AS addr_col
USING COLUMNS (addr_col);

You can also create policies through the Catalog Explorer UI. See Create and manage ABAC policies for details.

Step 6: Test the row filter​

Run the following query to verify that the row filter policy is working.

SQL
SELECT * FROM abac_tutorial.customers.profiles;

Only the 10 non-EU resident rows are returned. The five EU customers (Liam, Sophie, Hans, Elena, and Johan) are hidden.

Step 7: Create a UDF to mask SSNs​

This UDF returns a fully-redacted placeholder for any SSN value passed to it.

SQL
CREATE OR REPLACE FUNCTION redact_ssn(ssn STRING)
RETURNS STRING
RETURN '***-**-****';

Step 8: Create a column mask policy​

Create a policy that targets all columns tagged pii = ssn and applies the redact_ssn function to each.

SQL
CREATE POLICY redact_ssn_policy
ON SCHEMA abac_tutorial.customers
COLUMN MASK redact_ssn
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'ssn') AS ssn_col
ON COLUMN ssn_col;

Step 9: Test the column mask​

Run the following query to verify that both the row filter and column mask are active.

SQL
SELECT * FROM abac_tutorial.customers.profiles;

SSNs now return as ***-**-****. Only non-EU residents are returned because the row filter is also active.

Extend: Conditional email masking​

The following steps extend the tutorial with consent-aware email masking. Customers who opted in (has_consent = TRUE) have their full email shown; others see only the first character and domain.

Step 10: Create a consent-aware email masking UDF​

This UDF takes two arguments:

  • email: the actual email value from the matched column
  • consent: the value of the has_consent column on the same row
SQL
CREATE OR REPLACE FUNCTION mask_email_by_consent(email STRING, consent BOOLEAN)
RETURNS STRING
RETURN CASE
  WHEN consent = TRUE THEN email
  ELSE CONCAT(LEFT(email, 1), '***@', SUBSTRING_INDEX(email, '@', -1))
END;

Step 11: Create the conditional email masking policy​

This policy targets columns tagged pii = email and passes the has_consent column to the UDF.

note

The has_consent column was tagged with the consent governed tag in Step 3. This is required because USING COLUMNS can only reference columns that are matched via MATCH COLUMNS. Even though has_consent is not being masked, it must be tagged so the policy can pass its value to the UDF.

SQL
CREATE POLICY mask_email_by_consent_policy
ON SCHEMA abac_tutorial.customers
COLUMN MASK mask_email_by_consent
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'email') AS email_col,
  has_tag('consent') AS consent_col
ON COLUMN email_col
USING COLUMNS (consent_col);

Step 12: Test conditional email masking​

Run the following query to verify that all three policies are working together.

SQL
SELECT * FROM abac_tutorial.customers.profiles;

Customers with has_consent = TRUE have their full email shown. Customers with has_consent = FALSE see a masked version. SSNs remain fully masked, and only non-EU customers are returned.

first_name

has_consent

email

ssn_number

John

TRUE

john.doe@example.com

***-**-****

Jane

FALSE

j***@example.com

***-**-****

Alice

TRUE

alice.j@example.com

***-**-****

Bob

FALSE

b***@example.com

***-**-****

Charlie

TRUE

charlie.d@example.com

***-**-****

Emily

FALSE

e***@example.com

***-**-****

Frank

TRUE

frank.m@example.com

***-**-****

Grace

TRUE

grace.w@example.com

***-**-****

Hank

FALSE

h***@example.com

***-**-****

Ivy

TRUE

ivy.taylor@example.com

***-**-****

Summary​

This tutorial demonstrated three ABAC patterns:

  • Row filtering: hide rows based on column values matched by governed tags
  • Column masking: mask columns matched by governed tags
  • Conditional masking: mask a column based on another column's value on the same row, by tagging the context column and passing it to the UDF via USING COLUMNS

Clean up​

To remove all objects created in this tutorial, run the following. If you skipped the conditional email masking steps, the DROP POLICY mask_email_by_consent_policy and DROP FUNCTION mask_email_by_consent statements fail, which is expected.

SQL
DROP POLICY hide_eu_customers ON SCHEMA abac_tutorial.customers;
DROP POLICY redact_ssn_policy ON SCHEMA abac_tutorial.customers;
DROP POLICY mask_email_by_consent_policy ON SCHEMA abac_tutorial.customers;
DROP FUNCTION IF EXISTS abac_tutorial.customers.is_not_eu_address;
DROP FUNCTION IF EXISTS abac_tutorial.customers.redact_ssn;
DROP FUNCTION IF EXISTS abac_tutorial.customers.mask_email_by_consent;
DROP TABLE IF EXISTS abac_tutorial.customers.profiles;
DROP SCHEMA IF EXISTS abac_tutorial.customers CASCADE;

To remove the pii and consent governed tags, use the Catalog Explorer UI.

On this page