2 1
Read Time:2 Minute, 46 Second

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:

Masking Hierarchy

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.

Employee1

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

Grant Privileges

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.

REP1 masking

Now Login to the FLM and query the Employee table, Able to see the Phone number but SSN is still not available.

FLM Masking

Login to the OPS and query the Employee table: Both SSN and Phone are visible.

Ops Masking

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.

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *