Object tagging is use to track sensitive data, such as PII (Personally Identifiable Information) or PHI (Protected Health Information) data. It enables data stewards to track sensitive data for compliance, discovery, protection and auditing. Snowflake stores the tag and its string value as a key-value pair. For a table or view and its columns, the total number of unique tag keys that can be set is 20.Because tags can be assign to tables, views, and columns, setting a tag helps to identify objects and columns that contain sensitive information.
We used tags to classify our sensitive data, so we can locate them with ease. Upper management can then easily break down who is accessing the sensitive fields data and can leverage this information for Reporting purpose. Later on team can work on the appropriate access controls on PII data.
The ALLOWED_VALUES tag property enables specifying the possible string values that can be assigned to the tag when the tag is set on an object. The maximum number of possible string values for a single tag is 50.
Say we have CUST_TBL which contains some sensitive data. Instead of direct apply Masking policy on this table, Leadership wants to verify how frequently PII data is access. However, Based on the access pattern and users information ,team will decide for Dynamic Masking rules.
Steps
Steps:
- We will create TAG with Access/No Access values.
- Alter the Table and apply the TAG Values on PII and non PII columns accordingly.
- Develop the ACCESS_HISTORY table query to identify the users who has access the table.
- After Analysis the Access History and mutual discussion, Create the Masking Policy on PII columns.
- Attach the Policy to the CUST_TBL table.
CREATE TAG my_sensitive_data ALLOWED_VALUES 'Access','No Access';
ALTER TABLE cust_tbl ALTER COLUMN CUST_BAL SET TAG my_sensitive_data='No Access';
ALTER TABLE cust_tbl ALTER COLUMN INV_AMT SET TAG my_sensitive_data='No Access';
ALTER TABLE cust_tbl ALTER COLUMN SSN SET TAG my_sensitive_data='No Access';
ALTER TABLE cust_tbl ALTER COLUMN CRID SET TAG my_sensitive_data='No Access';
ALTER TABLE cust_tbl ALTER COLUMN CUST_NUM SET TAG my_sensitive_data='Access';
ALTER TABLE cust_tbl ALTER COLUMN CUST_STAT SET TAG my_sensitive_data='Access';
ALTER TABLE cust_tbl ALTER COLUMN INV_NO SET TAG my_sensitive_data='Access';
ALTER TABLE cust_tbl ALTER COLUMN PHONE SET TAG my_sensitive_data='Access';
ALTER TABLE cust_tbl ALTER COLUMN EMAIL SET TAG my_sensitive_data='Access';
Masking Policy:
alter table cust_tbl modify column CRID set masking policy CUST_MASK_PII;
alter table cust_tbl modify column SSN set masking policy CUST_MASK_PII;
alter table cust_tbl modify column INV_AMT set masking policy CUST_MASK_PII_NUM2;
alter table cust_tbl modify column CUST_BAL set masking policy CUST_MASK_PII_NUM2;
Accessing the Table with Generic User and data would be mask for PII fields:
Accessing the Table with ACCOUNTADMIN Role and no masking there: