dynamic_masking_example.sql

-- Create a masking policy for email addresses
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) 
RETURNS string ->
CASE 
  WHEN CURRENT_ROLE() IN ('ANALYST') THEN val 
  ELSE REGEXP_REPLACE(val, '.+\@', '*****@') 
END;

-- Create a tag for PII data
CREATE TAG pii_email_data;

-- Assign the masking policy to the tag
ALTER TAG pii_email_data SET MASKING POLICY email_mask;

-- Now tag any email column - automatic protection
ALTER TABLE customers MODIFY COLUMN email_address SET TAG pii_email_data = 'sensitive';