Data security and governance are crucial for today’s digital businesses. Data security has become even more critical with data moved out of the enterprise’s IT infrastructure into cloud storage. Predominant cloud data warehouses like Snowflake provide industry-leading features that ensure the highest levels of security.

Snowflake provides security features like account/user authentication, object security, data security, and data encryption making it easier for organizations to secure their data. Snowflake is also facilitating data governance with column-level security and the recently released row-level security. But is row-level security enough to cater to organizational needs? Most often not. This blog will detail how you can overcome this limitation by creating a custom robust row-level security solution.

Limitations in row-level security in Snowflake 

• It only supports AND clauses in the subquery of the access policy. As a result of this, it does not support multiple single values. For example, a user cannot see data from multiple regions. 
• Optimization of queries is not applicable on table/views with a row access policy since Snowflake must identify the subset of rows the query is permitted to access.  
• Once the access policy is defined and assigned to a table/view, altering it isn’t easy. The policy must be dropped from all tables that it assigned before changing it. 

Business scenario 

Consider sales data of an organization split across multiple tables, fact table containing sales data – ORDERS and master data tables – CUSTOMER, NATION, REGION. Below are the specific security use cases required to secure this data:

1. ORDERS table contains sensitive information such as phone numbers, zip code, credit card number of the end customer. This data has to be accessible only to the delivery department.  
2. The regional managers should have access only to their region. One regional manager can manage more than one region. Global managers will have access to all regions 
3. There are segment managers who manage a specific segment of customers, and they should be provided access only for the segments they manage. Global managers will have access to all segments. 
   

To address scenario 1, where a few columns have to be restricted, you can use Dynamic Data Masking or External Tokenization features available in Snowflake. Please refer to snowflake documentation for further details on these features. For scenarios 2 and 3, where you have to limit access to a subset of data, and out-of-the-box solutions in Snowflake may not work.

Row Level Security  

You can create secure views using the CURRENT_ROLE() or CURRENT_USER() context functions to dynamically filter rows for the user querying the view. But it’s not straightforward in a production scenario as you might have more than one field based on which you would like to set access restrictions. To make this possible, listed below are the prerequisites

Prerequisites 

1. Views – Create views combining data from different tables based on end-user needs. 

2. Functional Role – Roles, which has object-level security set up for the schemas and views which the intended users will have access for 

3. Control Table – Custom table used to maintain which fields are authorization relevant and what value each role should have access for 

Views

For our scenario, create a view combining sales data with master data 

Functional Role

REGIONAL_MANAGER, SEGMENT_MANAGER, and GLOBAL_MANAGER roles are set will read-only access to the view V_ORDERS. 

Control Table

A table that maintains the fields that are authorization relevant. Another table holds the USER-wise values based on which access is to be provided. 

Sample data records in the table

Setting up Row Level Security 

Now that all prerequisites are set, our goal is to create a secure view that will read conditions from this table and build where clause based on these conditions and context function CURRENT_USER. It would not be easy to rebuild the view whenever a new user has to be added, or authorization has to be set for a new view. Hence, it is ideal to have a procedure that reads the control table, creates the where condition dynamically, and creates a secure view. 

Thus, you can overcome the limitation of the row-level security in Snowflake. However, the key to the success of row-level security is to maintain entries in the control tables effectively. This might be an issue if you have to do mass user provisioning. In such cases, you would require automation of user provisioning. The following blog will focus on how user access can be automated. 

To learn more about Snowflake, check out these blogs. Reach out to us here today if you are interested in evaluating if Snowflake is suitable for you. 

Learn more about Visual BI’s Snowflake offerings here.