Day: May 19, 2021

Snowflake Data Masking

Posted on by Frank Bell

Introduction:

 

Today’s article discusses Snowflake Data Cloud’s implementation of dynamic data masking, which is a column-level security feature used to mask data at query runtime. We provide a step-by-step guide on how to create and apply a data masking policy for email addresses in a stored procedure. The article also highlights the benefits of using dynamic data masking policies to secure and obfuscate PII data for different roles without access while displaying the data to roles that need access to it.

Last week, the United States Centers for Disease Control and Prevention (CDC) issued new policies regarding COVID-19 masks. We will focus on how to implement Snowflake Data Cloud’s “Data Masking”. Let’s get started!

 

What is Data Masking?

 

Data Masking is just like it sounds… the hiding or masking of data. This is a practical method to add extra data masking for column-level security. Data Masking overall is a simple concept. It has caught on in our new age of GDPR, PII. What is Snowflake’s Version of Data Masking? Snowflake’s implementation of this is… Dynamic Data Masking.

Dynamic Data Masking is column-level security that uses masking policies to mask data at your query run time. Snowflake’s version of data masking, has several features including Masking policies that are at the schema level. Data Masking currently works to mask data at either the table or view object. The masking policies are applied at query runtime. The masking policies are applied to every location where the column is displayed. Depending on all the variables of your role, your role hierarchy, your masking policy conditions, and SQL execution content then you will see fully masked data, partially masked data, or just plain text!

Now that you know what Snowflake Data Cloud Dynamic Data Masking is then…. how do you use it? Data Masking within Snowflake is enabled with Data Definition Language (DDL). Here is the basic syntax constructs you use for the masking policy object. It is your typical object CREATE, ALTER, DROP, SHOW, DESCRIBE. This is a common feature for most Snowflake objects, and one of the reasons why I prefer Snowflake. Most of the time, it’s reliable, easy to use, and consistent.

So, let’s have some fun and create a data masking policy for email addresses in a simple example. There are 3 main parts for creating and applying a dynamic data mask on Snowflake to a column. Here we go:

 

PART 1 – Enable and Grant Masking Policy

 

To enable masking policy on Snowflake, follow these steps:

  1. Grant create masking policy on schema to a role. For example: GRANT CREATE MASKING POLICY ON SCHEMA DEMO_MASKING_DB.DEMO TO ROLE "DATA_MASKING_ADMIN_ROLE";
  2. Use the account admin role to grant apply masking policy on account to the role. For example: GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE "DATA_MASKING_ADMIN_ROLE";

Replace “DEMO_MASKING_DB.DEMO” with the actual schema name and “DATA_MASKING_ADMIN_ROLE” with the actual role name.

Remember to grant the necessary privileges to the roles that will use the masking policy.

 

PART 2 – Create a Masking Policy

To create a masking policy in Snowflake, follow these steps:

  1. Use a role that has the necessary privileges to create a masking policy.
  2. Use the schema where the table or view that needs the masking policy is located.
  3. Use the CREATE MASKING POLICY statement to create the policy. For example:
CREATE OR REPLACE MASKING POLICY MASK_FOR_EMAIL AS (VAL STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ROLE') THEN VAL
ELSE '*********'
END;

Replace MASK_FOR_EMAIL with the name of your masking policy. In this example, the policy masks the email column with asterisks for all roles except for the HR_ROLE.

Remember to grant the necessary privileges to the roles that will use the masking policy.

 

PART 3 – Apply the Masking Policy to a Column in a View or Table

 

To apply the masking policy to a column in a view or table in Snowflake:

  1. Use a role that has the necessary privileges to modify the table or view.
  2. Use the schema where the table or view that needs the masking policy is located.
  3. Use the ALTER TABLE or ALTER VIEW statement to modify the column and apply the masking policy. For example:
ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN EMAIL SET MASKING POLICY MASK_FOR_EMAIL;

Replace EMPLOYEE with the name of your table and EMAIL with the name of the column that needs the masking policy. Replace MASK_FOR_EMAIL with the name of your masking policy.

Remember to grant the necessary privileges to the roles that will use the masking policy.

(just creating a masking policy is not enough. Kind of like wearing a covid mask under your mouth and nose.  Even though you have a mask, it’s not applied really so it’s not working)

 

 

 

We will show you how to do all of this in detail below.

 

Dynamic Data Masking Example

Let’s say we want to create a data mask for email addresses in our row using a stored procedure.

If you have not been using our Snowflake Solutions Demo Database Training Example then let’s create a database, schema, and table to use.


/* SETUP DEMO DATABASE AND TABLE FOR DATA MASKING DEMO and PROOF OF CONCEPT */
USE ROLE SYSADMIN;  /*use this role or equivalent */
CREATE OR REPLACE DATABASE DEMO_MASKING_DB;
CREATE SCHEMA DEMO;
CREATE OR REPLACE TABLE EMPLOYEE(ID INT, FULLNAME VARCHAR,HOME_ADDRESS VARCHAR,EMAIL VARCHAR);
INSERT INTO EMPLOYEE VALUES(1,'Frank Bell','1000 Snowflake Lane North Pole, Alaska', 'fbell@snowflake.com');
INSERT INTO EMPLOYEE VALUES(2,'Frank S','1000 Snowflake Lane North Pole, Alaska', 'franks@snowflake.com');
INSERT INTO EMPLOYEE VALUES(3,'Craig Stevens','1000 Snowflake Lane North Pole, Alaska', 'craig@snowflake.com');
CREATE WAREHOUSE IF NOT EXISTS MASK_WH WITH WAREHOUSE_SIZE = XSMALL, INITIALLY_SUSPENDED = TRUE, auto_suspend = 60;

/* PART 0 – create and grant roles for DATA MASKING DEMO – REPLACE FREDDY WITH YOUR USERNAME– there is more to do when you use custom roles with no privileges */USE ROLE SECURITYADMIN;CREATE ROLE IF NOT EXISTS EMPLOYEE_ROLE;CREATE ROLE IF NOT EXISTS MANAGER_ROLE;CREATE ROLE IF NOT EXISTS HR_ROLE;CREATE ROLE IF NOT EXISTS DATA_MASKING_ADMIN_ROLE;GRANT USAGE ON DATABASE DEMO_MASKING_DB TO ROLE EMPLOYEE_ROLE;GRANT USAGE ON SCHEMA DEMO_MASKING_DB.DEMO TO ROLE EMPLOYEE_ROLE;GRANT SELECT ON TABLE DEMO_MASKING_DB.DEMO.EMPLOYEE TO ROLE EMPLOYEE_ROLE;GRANT USAGE ON DATABASE DEMO_MASKING_DB TO ROLE HR_ROLE;GRANT USAGE ON SCHEMA DEMO_MASKING_DB.DEMO TO ROLE HR_ROLE;GRANT SELECT ON TABLE DEMO_MASKING_DB.DEMO.EMPLOYEE TO ROLE HR_ROLE;GRANT USAGE,MODIFY ON DATABASE DEMO_MASKING_DB TO ROLE “DATA_MASKING_ADMIN_ROLE”;GRANT USAGE,MODIFY ON SCHEMA DEMO_MASKING_DB.DEMO TO ROLE “DATA_MASKING_ADMIN_ROLE”;GRANT USAGE ON WAREHOUSE MASK_WH TO ROLE EMPLOYEE_ROLE;GRANT USAGE ON WAREHOUSE MASK_WH TO ROLE HR_ROLE;GRANT ROLE EMPLOYEE_ROLE TO USER FREDDY;GRANT ROLE MANAGER_ROLE TO USER FREDDY;GRANT ROLE HR_ROLE TO USER FREDDY;GRANT ROLE DATA_MASKING_ADMIN_ROLE TO USER FREDDY;

/* PART 1 – enable masking policy ON ACCOUNT AND GRANT ACCESS TO ROLE */GRANT CREATE MASKING POLICY ON SCHEMA DEMO_MASKING_DB.DEMO TO ROLE “DATA_MASKING_ADMIN_ROLE”;USE ROLE ACCOUNTADMIN;GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE “DATA_MASKING_ADMIN_ROLE”;

/* PART 2 – CREATE MASKING POLICY /USE ROLE DATA_MASKING_ADMIN_ROLE; USE SCHEMA DEMO_MASKING_DB.DEMO;CREATE OR REPLACE MASKING POLICY MASK_FOR_EMAIL AS (VAL STRING) RETURNS STRING ->CASEWHEN CURRENT_ROLE() IN (‘HR_ROLE’) THEN VALELSE ‘********’END;

/* PART 3 - APPLY MASKING POLICY TO EMAIL COLUMN IN EMP:LOYEE TABLE */ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN EMAIL SET MASKING POLICY MASK_FOR_EMAIL;

**AWESOME – NOW YOU NOW HAVE CREATED AND APPLIED YOUR DATA MASK! Let’s Test it out.


/* TEST YOUR DATA MASK !!! --> TEST by QUERYING TABLE WITH DIFFERENT ROLES AND SEE RESULTS */
/* Notice the EMAIL is MASKED with ******* */
USE ROLE EMPLOYEE_ROLE;
SELECT * FROM DEMO_MASKING_DB.DEMO.EMPLOYEE;
/* Notice the EMAIL is NOT MASKED */
USE ROLE HR_ROLE;
SELECT * FROM DEMO_MASKING_DB.DEMO.EMPLOYEE;

ADDITIONAL DETAILS:

  • **MASKS are really custom data definition language (DDL) objects in Snowflake. *YOU can always get their DDL by using the Snowflake standard GET_DDL function or using DESCRIBE./ EXAMPLES for reviewing the MASKING POLICY // when using SECURITYADMIN or other roles without USAGE you must use the full DATABASE.SCHEMA.POLICY PATH */

USE ROLE SECURITYADMIN;DESCRIBE MASKING POLICY DEMO_MASKING_DB.DEMO.MASK_FOR_EMAIL;

USE ROLE ACCOUNTADMIN; /* when using SELECT that means the ROLE MUST HAVE USAGE enabled which the SECURITYADMIN role does not have by default */

SELECT GET_DDL(‘POLICY’,’DEMO_MASKING_DB.DEMO.MASK_FOR_EMAIL’);

 

Conclusion:

 

Dynamic Data Masking Policies are a great way to secure and obfuscate your PII data to different roles without access where necessary while at the same time displaying the PII data to the roles that need access to it. We hope this tutorial has helped you understand Dynamic Data Masking on Snowflake. For further information on Snowflake, check out our blog for more tips and tricks.