Mask PII columns by type with tag introspection

This tutorial shows how to use the value of a governed column tag to choose a masking rule for personally identifiable information (PII). A single attribute-based access control (ABAC) column mask policy covers every column tagged as an email address or a phone number, and one SQL user-defined function (UDF) applies the appropriate mask to each column. The policy uses the get_column_tag_value() tag introspection function to pass each column's tag value to the UDF.

For the syntax and behavior of tag introspection functions, see Tag introspection functions.

Prerequisites

• A SQL warehouse, serverless compute, or Databricks Runtime 18 LTS and above to create the policy. To query the protected table, use any compute that meets the ABAC compute requirements.
• The CREATE privilege for governed tags at the account level. Account admins and workspace admins have this privilege by default.
• The ASSIGN privilege on the governed tag you create. Account admins have this privilege by default. See Manage permissions on governed tags.
• The APPLY TAG privilege on the contacts table to set column tags in Step 3.
• MANAGE permission on the target catalog or schema.
• EXECUTE on the UDF.

Scenario

Your organization classifies PII columns with the governed tag pii_type, whose value identifies the type of data, such as email or phone. Each type needs a different mask: hide the local part of an email address, but keep the last four digits of a phone number.

With tag introspection, one policy protects all columns tagged with pii_type, while applying a different mask for each PII type.

The policy matches all columns with the pii_type tag and passes each column's tag value to the UDF. The UDF uses that value to select the appropriate mask.

You can reuse one policy across PII types rather than create a separate policy for each.

Whether you need tag introspection depends on whether the tag value determines how the UDF masks the data:

When	Pattern
The UDF applies different masks based on the tag value	has_tag() to match columns and get_column_tag_value() to pass the value to the UDF
The UDF applies the same mask to all matched columns	A has_tag() or has_tag_value() condition, without tag introspection

When	Pattern
The UDF applies different masks based on the tag value	has_tag() to match columns and get_column_tag_value() to pass the value to the UDF
The UDF applies the same mask to all matched columns	A has_tag() or has_tag_value() condition, without tag introspection

Step 1: Create a governed tag

To create a governed tag with allowed values for the two PII types, run:

SQL

CREATE GOVERNED TAG pii_type
  DESCRIPTION 'PII type used by the ABAC tag introspection tutorial'
  VALUES ('email', 'phone');

Governed tag keys are unique within the account. If a governed tag named pii_type already exists, the statement returns an ALREADY_EXISTS error. Choose a different key and use it throughout the tutorial. For the full syntax, see CREATE GOVERNED 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: Build sample data

Create a catalog, a schema, and a contacts table with an email column and a phone column:

SQL

CREATE CATALOG IF NOT EXISTS abac_tutorial;
USE CATALOG abac_tutorial;

CREATE SCHEMA IF NOT EXISTS introspection_demo;
USE SCHEMA introspection_demo;

CREATE OR REPLACE TABLE contacts (
  contact_id INT,
  email STRING,
  phone STRING
);

INSERT INTO contacts VALUES
  (1, 'alex@example.com', '123-456-7890'),
  (2, 'sam@example.com',  '234-567-8901');

Step 3: Apply the governed tag

Tag the two columns with different values of the same tag key:

SQL

ALTER TABLE contacts
  ALTER COLUMN email SET TAGS ('pii_type' = 'email');
ALTER TABLE contacts
  ALTER COLUMN phone SET TAGS ('pii_type' = 'phone');

Both columns now share the pii_type tag key. The tag value tells the UDF which mask to apply.

Step 4: Create the masking UDF

Create one SQL UDF that takes the column value and its PII type:

SQL

CREATE OR REPLACE FUNCTION abac_tutorial.introspection_demo.mask_pii_by_type(
  col_value STRING,
  pii_type STRING
)
RETURNS STRING
RETURN CASE
  WHEN col_value IS NULL THEN NULL
  WHEN pii_type = 'email' THEN regexp_replace(col_value, '(^[^@]+)', '***')
  WHEN pii_type = 'phone' THEN concat('***-***-', right(col_value, 4))
  ELSE '***'
END;

The ELSE branch fully masks the value if the UDF receives a PII type it doesn't recognize, or no type at all.

Step 5: Create the column mask policy

Create one policy that applies to every column in the schema with the pii_type tag:

SQL

CREATE POLICY mask_pii_columns
ON SCHEMA abac_tutorial.introspection_demo
COLUMN MASK abac_tutorial.introspection_demo.mask_pii_by_type
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag('pii_type') AS pii_col
ON COLUMN pii_col
USING COLUMNS (get_column_tag_value(pii_col, 'pii_type'));

In this policy:

• has_tag('pii_type') matches every column that has the tag, whatever its value.
• ON COLUMN pii_col passes the matched column's data as the UDF's first argument.
• USING COLUMNS (get_column_tag_value(pii_col, 'pii_type')) passes that column's tag value, email or phone, as the second argument.

Step 6: Verify the results

Because the policy applies to account users, it also applies to you. To check the masked values, run:

SQL

SELECT * FROM abac_tutorial.introspection_demo.contacts;

The query returns:

Output

contact_id  email            phone
1           ***@example.com  ***-***-7890
2           ***@example.com  ***-***-8901

The email and phone columns are masked differently, but the same policy protects both.

Why this pattern works

The policy matches columns based on the tag key, regardless of whether the value is email or phone. The UDF receives the tag value and applies the corresponding mask.

To support another PII type, such as ssn:

1. Add ssn to the allowed values of the governed tag.
2. Add an ssn branch to the UDF.
3. Tag the new columns.

The policy doesn't change.

This pattern consolidates policies that differ only in how they mask data. If different PII types require different TO or EXCEPT principals, you still need separate policies. See Implement multi-domain column masking with sensitivity tiers for an example.

For table-tag introspection with get_tag_value(), see Tag introspection functions.

Clean up

To remove the objects created in this tutorial, run:

SQL

DROP POLICY mask_pii_columns ON SCHEMA abac_tutorial.introspection_demo;
DROP FUNCTION IF EXISTS abac_tutorial.introspection_demo.mask_pii_by_type;
DROP TABLE IF EXISTS abac_tutorial.introspection_demo.contacts;
DROP SCHEMA IF EXISTS abac_tutorial.introspection_demo CASCADE;

Other ABAC tutorials also use the abac_tutorial catalog, so this cleanup leaves it in place. To remove it, run DROP CATALOG IF EXISTS abac_tutorial CASCADE.

To remove the governed tag, drop the policy first, and then run:

SQL

DROP GOVERNED TAG pii_type;