0
0
In data-driven enterprises, data security is non-negotiable. Dynamic Masking policies in Snowflake help safeguard sensitive information such as customer emails, payment details, and purchased items.
However, a common challenge arises:
- Hardcoded role names in masking policies make managing access permissions cumbersome.
- Whenever a new role is added or removed, the entire masking policy needs to be modified manually. Since a masking policy cannot be altered while it’s applied to any columns, you must first remove it from all associated columns before dropping it.
This blog presents a dynamic, scalable, and efficient approach to role-base data masking in Snowflake using JavaScript UDFs. We go beyond traditional column-level masking by demonstrating how to mask ARRAY data in Snowflake. Instead of masking the entire array as a single entity, we ensure that each individual item within the array is masked separately, providing granular data protection. This approach maintains the structure of the data while ensuring sensitive details remain hidden from unauthorized roles. This is a key highlight of our implementation, making our masking strategy more flexible and practical for real-world scenarios.
The Challenge: Static Role-Based Masking
The Challenge: Static Role-Based Masking
Traditional masking policies hardcode specific roles into CASE statements.
This means whenever a new role is added or removed, you must manually update all masking policies and reapply them, which is time-consuming and error-prone.
Example of a Static Masking Policy:
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SUPER_ROLE', 'ACCOUNTADMIN', 'DATA_OWNER') THEN val
WHEN CURRENT_ROLE() IN ('DATA_ANALYST', 'SUPPORT_TEAM') THEN REGEXP_REPLACE(val, '(^[^@]{2})[^@]+(@.*$)', '\\1****\\2')
ELSE '***MASKED***'
END;
What’s Wrong:
- Hardcoded roles – Any changes require manual modifications to the policy.
- Not scalable – Managing multiple policies across different datasets is tedious.
- High maintenance – Every update requires dropping and recreating the masking policy.
The Solution: Dynamic Role-Based Masking with JavaScript UDFs
The Solution: Dynamic Role-Based Masking with JavaScript UDFs
Instead of hardcoding roles, we introduce JavaScript UDFs to dynamically evaluate role-based access.
- Two separate UDFs determine if a user has full access or partial access.
- These UDFs dynamically evaluate
CURRENT_ROLE()without modifying the policy. - Policies automatically adjust when new roles are added or removed.
Step 1: Define Role-Based Access Functions
Full Access: Users who see unmasked data:
Now, we can simply update the role list inside these functions without modifying the masking policy!.
Step 2: Create Dynamic Masking Policies
Now, we use these UDFs inside masking policies:
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN no_masking(CURRENT_ROLE()) THEN val
WHEN masked_data(CURRENT_ROLE()) THEN REGEXP_REPLACE(val, '(^[^@]{2})[^@]+(@.*$)', '\\1****\\2')
ELSE '***MASKED***'
END;
CREATE OR REPLACE MASKING POLICY payment_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN no_masking(CURRENT_ROLE()) THEN val
WHEN masked_data(CURRENT_ROLE()) THEN 'XXXX-XXXX-XXXX-' || RIGHT(val, 4)
ELSE '***MASKED***'
END;
Masking Purchased Items (ARRAY Data)
The map function in this JavaScript UDF is iterating over each element in the input ARRAY (src) and replacing every item with the string '***MASKED ITEM***'
Step 3: Apply Policies to Table Columns.
ALTER TABLE customer_orders MODIFY COLUMN customer_email SET MASKING POLICY email_mask;
ALTER TABLE customer_orders MODIFY COLUMN payment_card SET MASKING POLICY payment_mask;
ALTER TABLE customer_orders MODIFY COLUMN items_purchased SET MASKING POLICY items_purchased_mask;
Step 4: Verify the Data.
Login with SUPER_ROLE:
Should have access to every column in CUSTOMER_ORDERS table.
Login with SUPPORT_TEAM:
Should have access to every column in CUSTOMER_ORDERS table.
Imagine a large financial institution where roles frequently change due to organizational restructuring.
🔹 Without this approach: The security team would have to manually modify dozens of masking policies.
🔹 With this approach: Dynamic role management ensures seamless, automated masking across the organization.
Conclusion: By integrating JavaScript UDFs, Snowflake’s masking policies become more flexible, scalable, and easier to maintain.
