2
0
During this post we will discuss about the Tag-based masking policy in snowflake. A Tag-based masking policy combines the object tagging and dynamic masking features. Therefore, Tag-based masking helps to apply policies uniformly to corresponding tagged columns. When the data type in the masking policy signature and the data type of the column match, the tagged column is automatically protected by the conditions in the masking policy. Hence main advantage is Tagged columns are protected without directly assigning a masking policy to those columns.
However, Based on my understanding I have prepared below diagram to depict all the privilege’s which are required to implement the Tag based masking.
Privileges required to implement TAG mask on custom Role:
- Usage on Database and Schema.
- Create Masking Schema Privilege’s.
- Create Tag Schema Privileges.
- Imported privileges on database SNOWFLAKE.
- Apply Tag and Apply Masking on Account.
- Required Warehouse privilege’s.
Steps to Apply Tag Masking
- Create tags as per business requirement.
- Create one masking policy for each data type based on the table columns
- Assign the policies to the tag.
- Assign the tag with the policies to the table column directly or to the table.
- Check the Information Schema to verify the tag-based policy is assigned to the columns.
- Query the data to verify the data protects.
Create the TAG Admin Roles:
create role tag_administrator;
grant usage on database DEMO_DB to role tag_administrator;
grant usage on schema DEMO_DB.PUBLIC to role tag_administrator;
grant create masking policy on schema DEMO_DB.PUBLIC to role tag_administrator;
grant create tag on schema DEMO_DB.PUBLIC to role tag_administrator;
grant imported privileges on database snowflake to role tag_administrator;
grant apply tag on account to tag_administrator;
grant apply masking policy on account to role tag_administrator;
grant monitor,operate,usage on warehouse COMPUTE_WH to role tag_administrator;
grant all on all tables in schema DEMO_DB.public to role tag_administrator;
create user TAG_ADMIN password = '***' default_role = tag_administrator ;
grant role tag_administrator to user TAG_ADMIN;
Create Application Role:
create role application_role;
grant usage on database DEMO_DB to role application_role;
grant usage on schema DEMO_DB.PUBLIC to role application_role;
grant monitor,operate,usage on warehouse COMPUTE_WH to role application_role;
grant all on all tables in schema DEMO_DB.public to role application_role;
create user APPL_USER password = '******' default_role = application_role;
grant role application_role to user APPL_USER;
Now Login with TAG_ADMIN user and create the TAG and assign to table:
create or replace tag tag_masking;
create or replace masking policy customer_ssn as (val string) returns string ->
case
when current_role() in ('TAG_ADMINISTRATOR','ACCOUNTADMIN') then val
else '**Data Mask***'
end;
create or replace masking policy customer_balance as (val number) returns number ->
case
when current_role() in ('TAG_ADMINISTRATOR','ACCOUNTADMIN') then val
else -999
end;
alter tag tag_masking set masking policy customer_ssn,masking policy customer_balance;
alter table CUSTOMER_TABLE set tag tag_masking = 'tagging data';
Verify in Information Schema:
select *
from table (DEMO_DB.information_schema.policy_references(
ref_entity_domain => 'TABLE',
ref_entity_name => 'CUSTOMER_TABLE' )
);
Login with Application Role and verify the Data.
Column based masking:
Login with TAG_ADMIN user and create the TAG;
CREATE TAG PII_Data ALLOWED_VALUES 'Access','No Access';
create or replace masking policy string_masking as (val string) returns string ->
case
when current_role() in ('TAG_ADMINISTRATOR','ACCOUNTADMIN') or system$get_tag_on_current_column('PII_Data') = 'Access' then val
else '***MASKED***'
end;
create OR REPLACE masking policy number_masking as (val number) returns number ->
case
when current_role() in ('TAG_ADMINISTRATOR','ACCOUNTADMIN') or system$get_tag_on_current_column('PII_Data') = 'Access' then val
else -999
end;
alter tag PII_Data set masking policy string_masking, masking policy number_masking;
Apply TAG to the PII Columns:
ALTER TABLE customer_table_detail ALTER COLUMN CUST_BAL SET TAG PII_Data='No Access';
ALTER TABLE customer_table_detail ALTER COLUMN INV_AMT SET TAG PII_Data='No Access';
ALTER TABLE customer_table_detail ALTER COLUMN SSN SET TAG PII_Data='No Access';
ALTER TABLE customer_table_detail ALTER COLUMN CRID SET TAG PII_Data='No Access';
ALTER TABLE customer_table_detail ALTER COLUMN CUST_NUM SET TAG PII_Data='Access';
ALTER TABLE customer_table_detail ALTER COLUMN CUST_STAT SET TAG PII_Data='Access';
ALTER TABLE customer_table_detail ALTER COLUMN INV_NO SET TAG PII_Data='Access';
ALTER TABLE customer_table_detail ALTER COLUMN PHONE SET TAG PII_Data='Access';
ALTER TABLE customer_table_detail ALTER COLUMN EMAIL SET TAG PII_Data='Access';
Verify in Information Schema:
select *
from table (DEMO_DB.information_schema.policy_references(
ref_entity_domain => ‘TABLE’,
ref_entity_name => ‘customer_table_detail’ )
);
Login with Application Role and verify the Data.
thanks for sharing this.