Data Governance in Snowflake – Part 1 

Introduction: 

Snowflake provides industry-leading features that ensure the highest levels of governance for your account and users, as well as all the data you store and access in Snowflake. 

To make sure your data is safe, consistent, and easily accessible, data governance is essential. A wide range of guidelines and procedures that are part of effective data governance may also be related to availability, usability, integrity, security, and compliance. 

In this first article in our series on data governance within Snowflake, we focus on 

• Row Level Security 
• Column Level Security 

Prerequisites:  

• Snowflake trial account. 
• Knowledge in SQL Scripting. 

Overview: 

Row Level features: Row-level security (RLS) feature allows you to restrict data access at the row level based on user roles or permissions. When a query is executed, snowflake dynamically applies access restriction logic, ensuring that users can only view or edit data they authorized to access. 

Column Level Security: 

Enforcing Column Level Security (CLS) to make sure that users can only view what they are supposed to is one method of protecting data. Column-level Security in Snowflake allows the application of a masking policy to a column within a table or view. 

1. Use case for Row Level Security:  

In this scenario, we will be displaying the query result based on the user role or permission. 

Example, If the department is HR, the user with the HR Role can only access the HR data and other department details are not shown. 

Follow below steps to implement Row-Level Security using Row Access Policies in Snowflake. 

1. Create a table or view to apply Row-Level Security 

                create or replace TABLE DEMO.PUBLIC. EMPLOYEES ( 

ID NUMBER (38,0), 

NAME VARCHAR (16777216), 

DOB VARCHAR (16777216), 

PHONE NUMBER (38,0), 

SSN VARCHAR (16777216), 

EMAIL VARCHAR (16777216), 

DEPARTMENT VARCHAR (16777216), 

SALARY NUMBER (38,0), 

EXPERIENCE NUMBER(38,0) 

); 

The EMPLOYEES table consists of some personal details of the employees. 

2. Create a Mapping table. 

A row access policy condition can reference a mapping table to filter the query result set.

3. Create a Row Access Policy  

Row access policy includes conditions and expressions. If the condition met to TRUE, then the respective user can view the query results. 

4. Add the Row Access Policy to a table or view. 

5. Create Custom Roles and their Role Hierarchy. 

6. Grant USAGE, SELECT privilege on table to custom roles. 

7. Query and verify Row-Level Security on table using custom roles. 

        Based on the Roles, the data will be query by using this Row Access Policies.  

        Case 1: If the current role is ACCOUNTADMIN, then the user can view all the department details. 

       Case 2: If the current role is HR_ADMIN, then the user can view only the HR department details. 

Case 3:  If the current role is HR_ADMIN, then the user can view only the HR department details. 

Drop Row access policy.  

We cannot drop the row access policy directly; we should first remove row access policy on the table or view where it is be applied. 

 

 

2. Use case for Column Level Security:  

The above table consists of some personal details of the employees that are not necessarily displayed to all the users who view this table. 

Hence, the columns in the employee details table are subject to the masking regulations listed below. 

1. The SSN details are to be masked and visible only to HR_MANAGER, ACCOUNTADMIN, and SECURITYADMIN. 

2. The email ID to be masked and visible only to HR_MANAGER, ACCOUNTADMIN, and SECURITYADMIN. 

3. The phone number to be Partially mask to other roles except HR_MANAGER, ACCOUNTADMIN, and SECURITYADMIN. 

4. The HR_MANAGER can view the compensation of employees with more than five years of experience. 

Create custom roles: 

Create roles – HR_MANAGER, ASSOCIATES. 

Assign the role to SYSADMIN. 

Grant USAGE to Database, Schema and Select privilege to Tables/ Views. 

Case 1: 

HASH () is used to mask the SSN details of the employee. Since the HASH () returns the numeric value, it must be type casted to string as the input and output data types of a masking policy must match. 

Case 2: 

UDFs can be used in masking policies to mask the data. In this use case, the email id of the employee is masked using mask_email_udf(). 

Case 3: 

In this use case, as the phone number must be partially masked, only the last 4 digits of the phone number is displayed, and the rest is masked. 

Case 4: 

The salary of the employees having experience more than 5 years is displayed to HR_MANAGER role and hence the masking policy is created which uses two inputs (salary, experience). 

On applying the above masking policies in EMPLOYEE table, and querying the table as an ASSOCIATES role, PHONE, SSN, EMAIL, SALARY details aren’t exposed as per the condition. 

Now, when it is viewed as HR_MANAGER role, the salary of employees having experience more than 5 years are displayed.  

Altering masking policies: 

The masking policy can be renamed, and additional rules can be added as well. The changes done to the masking policy will go into effect when the next SQL query that uses the masking policy runs. 

Policy cannot be altered if it is applied on the table, and hence it must be UNSET before altering the masking policy. 

View policy details: 

DESCRIBE MASKING POLICY – describes the details about a masking policy, including the creation date, name, data type, and SQL expression. 

Snowflake provides two Account Usage views to obtain information about masking policies: 

• The MASKING_POLICIES view provides a list of all masking policies in your Snowflake account. 

• The POLICY_REFERENCES view provides a list of all objects in which a masking policy is set. 

Drop masking policy: 

A Masking Policy in Snowflake cannot be dropped successfully if it is currently assigned to a column. 

1.Find the columns on which the policy is applied. 

2. Once the columns on which masking policies are applied is found out, UNSET the masking policy from the column 

3. Drop the masking policy. 

Conclusion:  

Hence, the row and Column level security can be applied by using the above procedures. In Part-2 of the Snowflake Data Governance series, we will look at Object Tagging and Data Classification in detail. 

Cittabase is a select partner with Snowflake. Please feel free to contact us regarding your Snowflake solution needs. Our snowflake solutions encompass a suite of services for your data integration and migration needs. We are committed to providing personalized assistance and support customized to your requirements.