2
0
Imagine a scenario where you have numerous columns across various tables that require data masking. Rather than applying a masking policy to each column separately, you can streamline the process by grouping all these sensitive columns under a common tag. This approach is commonly known as Tag-based data masking within the Snowflake platform. A tag is a higher-level object within the schema that can be link to other Snowflake objects. Snowflake maintains the tag as a key-value pair, consisting of a tag name and its associated string value.
The advantages of Tag-based Data Masking include automatic application of a masking policy to any future columns assigned to the same tag, ensuring consistent protection.
In our situation, we’re dealing with two tables: Customer Details and Invoice Details. According to business requirements, certain sensitive columns should be mask base on different role hierarchy levels. The conventional method involved creating a masking policy and individually applying it to each column. However, with the advent of Tags, the process has become significantly more streamline. We can group the PII columns under a single tag and then apply the policy to the tag itself, rather than to each individual column.
Technical Implementation:
Technical Implementation:
We have a Role Hierarchy outlined below, and the implementation of column masking will vary based on the role. For instance, ‘REP’ represents a lower-level role, whereas ‘OPS’ is at the top of the hierarchy.
REP->FLM->OPS
As per the above diagram, CUST_BAL,INV_AMT,SSN are map with PII Tag and PHONE is map to the DEMO_INFO Tag. Similarly in another table INV_AMT is map to PII tag while CNTRY is map to DEMO_INFO tag.
- We will create below TAG with respective values.
CREATE OR REPLACE TAG SALES.PUBLIC.PII_INFO ALLOWED_VALUES 'PII','DEMO_INFO';
- Alter the both tables and apply the Tag to the columns:
ALTER TABLE Customer_Details MODIFY COLUMN CUST_BAL SET TAG PII_INFO = 'PII';
ALTER TABLE Customer_Details MODIFY COLUMN INV_AMT SET TAG PII_INFO = 'PII';
ALTER TABLE Customer_Details MODIFY COLUMN SSN SET TAG PII_INFO = 'PII';
ALTER TABLE Customer_Details MODIFY COLUMN PHONE SET TAG PII_INFO = 'DEMO_INFO';
ALTER TABLE Invoice_Details MODIFY COLUMN INV_AMT SET TAG PII_INFO = 'PII';
ALTER TABLE Invoice_Details MODIFY COLUMN CNTRY SET TAG PII_INFO = 'DEMO_INFO';
- Create the Masking Policy.
CREATE OR REPLACE MASKING POLICY PII_MASK AS (VAL number) RETURNS Number ->
CASE
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('SALES.PUBLIC.PII_INFO') = 'PII' AND CURRENT_ROLE() NOT IN ('OPS') THEN '-9999'
ELSE VAL
END;
This masking policy will masked the Columns which are tagged as PII for REP and FLM.
CREATE OR REPLACE MASKING POLICY PII_DEMO_INFO AS (VAL string) RETURNS string ->
CASE
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('SALES.PUBLIC.PII_INFO') = 'DEMO_INFO' AND CURRENT_ROLE() NOT IN ('OPS','FLM') THEN '-9999'
ELSE VAL
END;
This masking policy will masked the Columns which are tagged as DEMO_INFO for REP.
- Apply Masking Policy to the Tag.
ALTER TAG SALES.PUBLIC.PII_INFO SET MASKING POLICY PII_MASK,MASKING POLICY PII_DEMO_INFO;
That’s it, Configuration changes are complete and will use the respective Roles and observe the behaviour.
Use Role REP;
select * from Customer_Details;
Data type of CUST_BAL and INV_AMT is NUMBER in Snowflake while the SSN is VARCHAR define in table. Notice the PII_MASK Policy which accepts and returns the NUMBER Datatype. So, Masking policy applies to tagged column data only if the data type specified in the policy signature matches the data type of the column.
So there is mismatch of datatype in masking policy and Column SSN and hence it was not mask by the policy.
select * from Invoice_Details;
Use Role FLM;
select * from Customer_Details;
select * from Invoice_Details;
Use role OPS;
select * from Customer_Details;
select * from Invoice_Details;
Advantage of Tagging is, any future columns assigned to that same tag will be automatically protected.
Say we want to mask Customer Status as well. We will issue the below query only and rest will taken care by Tag and masking policy.
ALTER TABLE Customer_Details MODIFY COLUMN CUST_STAT SET TAG PII_INFO = 'DEMO_INFO';
USE ROLE REP;
