Author: Vipul Sharma
Introduction
This blog will cover how Snowflake's tag-based masking can be leveraged to segregate and mask the PII data in the warehouse to keep it secure and visible only to privileged users.
Tag-Based Masking
It is Snowflake's newly released feature which came into public preview mode recently after the Snowflake summit. Tag-based masking combines the Object Tagging and the Dynamic Data Masking feature, allowing the policy to be set on a tag using ALTER TAG command.
Object Tagging enables data stewards to track sensitive data for use cases such as compliance, discovery, protection, and resource utilization through a centralized or decentralized data governance management strategy.
Tag is a schema-level object which can be assigned to others. As a key-value pair, Snowflake stores the tag and its string value. The tag value must be unique to your schema and is always a string.
When you apply a tag to an object using the CREATE TAG statement, you must supply the tag string value. If the tag already exists, it can be applied to an object when created using the CREATE object> statement. Alternatively, you can use an ALTER object> command to assign the tag to an already-existing object.
Tables, views, and columns can all be given tags. Setting a tag and then searching it makes it possible to find numerous database objects and columns that contain sensitive data.
In our case, we had the LOGIN_DETAILS table, which contains some PII data columns such as NAME, MOBILE_NO, and PWD.
A new role, object_tagging, has been created & the required privileges to the database objects are being granted using the below scripts.
Object tagging is used here to segregate the PII data. A tag named PII_TYPE is created and added to the sensitive columns.
Script:
create or replace role object_tagging;
grant create tag on schema APP_SCHEMA to role object_tagging;
grant apply tag on account to role object_tagging;
grant all on warehouse compute_wh to role object_tagging;
grant all on database APP_DB to role object_tagging;
grant all on schema APP_DB.APP_SCHEMA to role object_tagging;
grant select on table "APP_DB"."APP_SCHEMA"."LOGIN_DETAILS" to role object_tagging;
grant role object_tagging to user CROPANALYTICS;
use role object_tagging;
create or replace tag pii_type;
alter table LOGIN_DETAILS modify column pwd set tag pii_type='very confidential';
alter table LOGIN_DETAILS modify column mobile_no set tag pii_type='very confidential';
alter table LOGIN_DETAILS modify column name set tag pii_type='confidential';
Assigned tags can be checked using the below command.
select * from SNOWFLAKE.ACCOUNT_USAGE.tag_references;
select system$get_tag (‘pil_type’, ‘LOGIN_DETAILS.name’, ‘column’), system$get_tag(‘pil_type’, ‘LOGIN_DETAILS.name’, ‘column’);
Now, to selectively conceal plain-text data in table and view columns at query time, Dynamic Data Masking can be used, a Column-level Security feature.
Because of being schema-level objects in Snowflake, masking policies cannot be applied to a column unless a database and schema are present in Snowflake.
A new role, masking_admin, has been created and the required privileges to the database objects are granted using the scripts below.
Script:
create or replace role masking_admin;
grant create masking policy on schema APP_DB.APP_SCHEMA to role masking_admin;
grant apply masking policy on account to role masking_admin;
grant role masking_admin to user cropanalytics;
use role accountadmin;
grant all on warehouse compute_wh to role masking_admin;
grant all on database APP_DB to role masking_admin;
grant all on schema APP_DB.APP_SCHEMA to role masking_admin;
grant select on table "APP_DB"."APP_SCHEMA"."LOGIN_DETAILS"
to role masking_admin;
Then two masking policies maskpii_str & maskpii_num are created to mask string & number values respectively.
Script:
use role masking_admin;
create or replace masking policy maskpii_str as (val string) returns string ->
case
when current_role() in ('ACCOUNTADMIN') then val
else '***MASKED***'
end;
create or replace masking policy maskpii_num as (val number) returns number ->
case
when current_role() in ('ACCOUNTADMIN') then val
else -1
end;
Earlier, we had the option to use Dynamic Data Masking with only Tables and views, but with the introduction of Tag-based masking, a Masking policy can now be assigned directly to a tag that contains segregated sensitive columns.
Script:
use role object_tagging;
show tags;
alter tag APP_DB.APP_SCHEMA.pii_type set
masking policy maskpii_str,
masking policy maskpii_num;
And this is how the masked columns would look after implementing Tag-based Masking when accessed using roles other than ACCOUNTADMIN.
Summary
Snowflake's Tag-based masking feature can be very efficient when you have new PII data columns getting introduced at regular intervals, which eventually will be required to be masked as well. Then the admin will just have to add the tag to the newly introduced sensitive columns, and the Masking policy will be applied to them Dynamically.
References
Object Tagging — Snowflake Documentation
Tag-based Masking Policies — Snowflake DocumentationUnderstanding Dynamic Data Masking — Snowflake Documentation