by Ramana Kumar Gunti

Data Security in Snowflake using RLS

Large companies and professional businesses have to make sure that data is kept secure based on the roles and responsibilities of the users who are trying to access the data. One way to protect data is to enforce “Row Level Security” (RLS) to ensure that people can only access what they are supposed to see.

Snowflake working on fine-tuning the Row Access policies for general availability. In this article, we will show an approach to restrict the Row Level Data to Authorized users by using the Snowflake Functions CURRENT_ROLE , CURRENT_USER

So, Let’s get started!!

Row Level Security (RLS)

Row Level Security is a security mechanism that restricts the records from the table based on the authorization context of the current user and the current role that is logged in.

Requirements

Let us build a requirement to demonstrate RLS features in snowflake.

Requirement – 1: Manager should view the sales information of all the stores irrespective of his/her direct and indirect reportees.

Requirement – 2: Manager should view the sales information of all the stores related to that specific Region

Requirement – 3: Sales executives view their respective store details only.

The below diagram provides the overview of all the 3 requirements.

In This Topic:

• Step - 1: Data Setup
• Step - 2: Users Setup
• Step - 3: Secure View
• Step - 4: Test-Run

1. Data Setup

Create the EMPLOYEE, EMP_STORE, SALES, REPORTING_HIERARCHY, EMP_REPORTING_ROLE Tables and seed them with the initial data.

EMPLOYEE

• The employee table identifies every employee by an employee identifier and lists basic personnel information.
• The employee table is created with the following CREATE TABLE statement and inserts data using the INSERT statement:

EMP_STORE

This table lists the employees associated to store.

The EMP_STORE table is created with the following CREATE TABLE statement and inserts data using the INSERT statement:

SALES

Store table provides sales across each store.

The Sales table is created with the following CREATE TABLE statement and inserts data using the INSERT statement:

REPORTING_HIERARCHY

The table describes the designations in the organization.

The REPORTING_HIERARCHY table is created with the following CREATE TABLE statement and inserts data using the INSERT statement:

EMP_REPORTING_ROLE

The table list the employees mapping role in snowflake with their user name.

The EMP_REPORTING_ROLE table is created with the following CREATE TABLE statement and inserts data using the INSERT statement:

2. Snowflake - Roles/Users Setup

Created the following MANAGER/SALES roles and users in snowflake and assign the appropriate roles for each user account.

• MANAGER/E100 (GENERAL MANAGER)
• MANAGER/E101 (REGIONAL MANAGE)
• SALES/E108 (SALES EXECUTIVE)

3. Secure View

Created sales secure view to provide the sales information of all the stores in every region.

• Sales Hierarchy View

4. Test-Run

Now it is time to test our requirements with the data we have created.

Requirement - 1:

• When General Manager logins using the following credentials
  User: BOB
  Password : ******

• You should be able to view all the sales records since the BOB is a manager can access all the stores sales information irrespective of the region.

Requirement - 2:

• When Regional Manager logins using the following credentials
  User: ALEX
  Password : ******
• You should be able view only the sales records related to ‘CA’ since the ALEX is a regional manager and can access only the sales information related to all the stores in California region.

Requirement - 3:

• When Sales Executive logins with the following credentials.
  User: DAVID
  Password : ******

• You should be able to view only the sales records only one store in ‘TX’ since the DAVID is an executive and can access the sales information related to his store in Texas.

Conclusion

The example described above illustrates the process of using snowflake context functions (CURRENT_ROLE & CURRENT_USER) in terms of data security to filter the data at the row level. Row Level Security can be implemented using the custom logic and provides flexibility to filter the data based on user profile dynamically.

I hope this blog gave a preliminary understanding of the most important aspect of Row Leve Security. Please feel free to provide your comments / suggestions.

Signing-off until I post another blog next time.

Ramana Kumar Gunti is a Manager at KPI Partners and has 15 Years of experience in Big Data echo systems, Modelling and Cloud Analytics. He has extensively worked on Big Data, Cloud data warehouse and cloud analytics projects using Hadoop, Snowflake, python and Airflow. He is very enthusiastic to learn and explore the new tools and technologies and be updated with all the features of the existing products.