2
0
Data Masking in Snowflake Using Tags, Policies, and Automation: In modern data platforms, data masking and access control are critical pillars of security and compliance — especially with sensitive fields like SSNs, email addresses, and financial metrics. In this blog, we explore how to implement a tag-driven masking and row-level security framework in Snowflake. It supports multi-tenant governance, intelligent automation, and centralized control across sensitive data.
-
Tag-based column masking
-
Row-level security via table tags
-
Multiple masking policies
-
Role-based access control
-
Auto-tagging of PII using intelligent pattern matching
Architecture Summary
Key Highlights of the Implementation
Role-Based Column Masking
Sensitive fields like SSN, EMAIL, ACCOUNT_ID, CREDIT_SCORE are protected using tags and policies. Access is dynamically mask or allow based on the user’s role.
Row-Level Security via Table Tags
Tables are tagged as RESTRICTED, and access is filtered using a row access policy — only allowing certain roles to view specific tenant data.
Auto-Tagging PII
A custom stored procedure scans for columns with patterns like ssn, email, phone, and auto-tags them with 'PII', enabling protection instantly when new columns are added.
1. Tags: The Foundation of Policy-Driven Masking
We created 3 core governance tags:
These tags are used across multiple tables and columns to dynamically trigger policies without modifying individual column definitions.
2. Sample Tables and Tagged Columns
Two tables available:
-
CLIENT_PROFILE — Customer identity information
-
CREDIT_EVALUATION — Risk and credit limit assessments
Tagging sensitive columns:
ALTER TABLE CLIENT_PROFILE MODIFY COLUMN SSN SET TAG COLUMN_SENSITIVITY = 'PII';
ALTER TABLE CLIENT_PROFILE MODIFY COLUMN EMAIL SET TAG COLUMN_SENSITIVITY = 'PII';
ALTER TABLE CREDIT_EVALUATION MODIFY COLUMN ACCOUNT_ID SET TAG COLUMN_SENSITIVITY = 'FINANCIAL';
ALTER TABLE CREDIT_EVALUATION MODIFY COLUMN CREDIT_SCORE SET TAG COLUMN_MASK = 'PROTECT';
ALTER TABLE CREDIT_EVALUATION MODIFY COLUMN CREDIT_LIMIT SET TAG COLUMN_MASK = 'PROTECT';
3. Masking Policies
We created two policies — one for PII/Financial sensitivity, and one for strict field-level protection.
a. Tag-Driven Policy for Sensitivity
b. Policy for Strict Fields (e.g., CREDIT_SCORE)
c. Attach Policy to the Tag
ALTER TAG PUBLIC.COLUMN_SENSITIVITY SET MASKING POLICY MASK_TAGGED_COLUMNS;
ALTER TAG PUBLIC.COLUMN_MASK SET MASKING POLICY ACCOUNT_ID_COLS;
3. ROW-Level Access via Table Tag
ALTER TABLE CLIENT_PROFILE SET TAG PUBLIC.TABLE_VISIBILITY = 'RESTRICTED';
ALTER TABLE CREDIT_EVALUATION SET TAG PUBLIC.TABLE_VISIBILITY = 'RESTRICTED';
CREATE OR REPLACE ROW ACCESS POLICY RLS_MULTI_TENANT
AS (TENANT_ID STRING) RETURNS BOOLEAN ->
CASE
WHEN SYSTEM$GET_TAG_ON_CURRENT_TABLE('PUBLIC.TABLE_VISIBILITY') = 'RESTRICTED'
AND CURRENT_ROLE() IN ('SUPER_ADMIN','RISK_ANALYST') THEN TRUE
ELSE FALSE
END;
Attach Policy to the Table
ALTER TABLE CLIENT_PROFILE ADD ROW ACCESS POLICY RLS_MULTI_TENANT ON (TENANT_ID);
ALTER TABLE CREDIT_EVALUATION ADD ROW ACCESS POLICY RLS_MULTI_TENANT ON (TENANT_ID);
4. Intelligent PII Auto-Tagging via Stored Procedure
We developed a smart JavaScript stored procedure that scans columns and automatically tags likely PII based on keywords like ssn, email, dob, mobile, etc.
Key Features:
-
Scans only relevant tables
-
Ignores already tagged columns
-
Applies
PIItag only when column name matches pattern.
When we added a new column:
The column was automatically tag.!
After setting up the masking policies and running the auto-tagging procedure, a new column like phone is automatically tagged and masked — without any manual intervention.
Known issue: Currently Proc is able to set the TAG only on string columns. So if any numeric column gets add then Proc would not throw error but will not tag that column with COLUMN_MASK Tag value.
Tag-based masking in Snowflake offers a declarative, scalable, and auditable way to protect sensitive data. Combined with role-based access control and row-level filtering, it creates a powerful governance model ready for the needs of modern enterprises.
