2
1
Column masking (sometimes referred to as data masking) lets you assign the MASKED attribute to columns so that unprivileged users cannot view the data. Users with the UNMASK privilege can see the data that is mask to other users and can also control how that data can be interact with.
Snowflake’s dynamic data masking played a major role in protecting sensitive information by masking the data and change the actual values of data in encrypted form and thus restrict the unauthorized user in order to get access actual sensitive information i.e Personally identifiable information (PII) about the customer
Snowflake evaluates the masking policy as a SQL expression. Therefore, rules are evaluated in the order they are written if specified as WHEN-THEN clauses in a CASE expression in the masking policy body.
Consider the below multilevel hierarchy to implement the Column Masking:
REPS : collection Representative who talks to the Customer to recover the amount
FLM : First Line Manager where REPS reports to FLM
OPS: Operational Manager : FLM reports to OPS
REPS–> FLM —> OPS
Where REPS ,First level not authorized to view Customer Phone number and SSN ,
While the FLM ,above in the Hierarchy is authorize to access only Phone number as he/she can make a call to customer in case of issue but not SSN number which is confidential.
OPS the top most in hierarchy only authorize to access both Phone as well SSN number.
CREATE ROLE REP1
CREATE ROLE FLM
CREATE ROLE OPS
create or replace user REP1 password = 'REP1' default_role = REP1 must_change_password = false;
create or replace user FLM password = 'FLM' default_role = FLM must_change_password = false;
create or replace user OPS password = 'OPS' default_role = OPS must_change_password = false;
• Grant the required privileges on DATABASE , WAREHOUSE and SCHEMA to the Respective Roles.
• Grant the SELECT privileges on EMPLOYEE1 table to all the Roles.
Also GRANT CREATE MASKING POLICY ON SCHEMA DEMO_DB.PUBLIC TO ROLE REP1
Masking Policy:
Login to the REP1 user and create the Masking policies.
Masking Policy
Now login to the ACCOUNTADMIN and Assign these Masking policies to the SSN and Phone number columns in below way:
ALTER TABLE EMPLOYEE MODIFY COLUMN PHONE SET MASKING POLICY EMPLOYEE_PII_PHONE;
ALTER TABLE EMPLOYEE MODIFY COLUMN SSN SET MASKING POLICY EMPLOYEE_PII_SSN;
Login to the REP1 user and verify the value : SSN and PHONE are displayed 0 value.
Now Login to the FLM and query the Employee table, Able to see the Phone number but SSN is still not available.
Login to the OPS and query the Employee table: Both SSN and Phone are visible.
To unset the masking policy:
ALTER TABLE EMPLOYEE1 MODIFY COLUMN PHONE UNSET MASKING POLICY
ALTER TABLE EMPLOYEE1 MODIFY COLUMN SSN UNSET MASKING POLICY
drop MASKING POLICY EMPLOYEE_PII_PHONE
Currently working on ROW ACCESS Policy which is newly introduced by Snowflake and will share a real use case very soon.
