Securing your Data in Snowflake with Row Level Access and Multiple Conditions

Snowflake is one of the most popular Software-as-a-Service (SaaS) solutions to store and process the data of your whole organization. It’s a pioneer in data management, which is why Snowflake places a high value on security. Imagine  you are pulling data from different databases and sources in your organization, and you are centralizing all this data in Snowflake. Instead of having several users connecting to its own data source, you have several users connecting to one single point: Snowflake. How will you limit some users accessing the data from other users? You can think about privileges—that’s right, but not enough. Privileges can limit the access at object level (let’s say a table). However, it doesn’t help when you need to limit the access at row level. Here is where “Row Level Access” Policies in Snowflake come to help. With a Row Level Access Policy, you will be able to decide which set of rows can be seen and managed by which user(s). 

How does Row Level Security work?

Generally speaking, it’s best practice to have a “Policy Admin” or “Security Admin” that handles all the security features for your Snowflake account, including all your Row Level Access Policies. For example, in the following image, you have a Policy Admin who creates a Row Level Access Policy and then applies those policies to a Table or a View. Whenever a user wants to execute a SELECT against one of those tables or views, the user will be able to see only that specific set of rows that they are authorized to see. These policies will secure your data, allowing it to be queried by anyone with access but only returning specific data that the user is allowed to see.

There are three roles that we will use in this article:

• SECURITYADMIN: This is a default role in Snowflake. It will be used to grant the required access (GRANTs) to our Security Role, the one that is supposed to manage all the security stuff in our Snowflake account. You would think, why instead of creating a new role to manage security, we use the SECURITYADMIN directly? There is a simple reason: SECURITYADMIN role is more powerful, and we recommend having a role that can manage only those specific tasks that are allowed to manage, aka “Principle of Least Privilege.” Snowflake documentation says the following description about this role: 

(aka Security Administrator)

Role that can manage any object grant globally, as well as create, monitor, and manage users and roles. More specifically, this role:

Is granted the MANAGE GRANTS security privilege to be able to modify any grant, including revoking it.

Inherits the privileges of the USERADMIN role via the system role hierarchy (e.g. USERADMIN role is granted to SECURITYADMIN). 

• Security Role: I will create the SECURITY_ROLE; this role will be used to create Row Level Access Policies and apply them to tables and views. 
• Analyst: It’s the role used by end users that use the table or view that we want to secure with Row Level Access Policies.

Configuring a Row Level Access Policy:

Creating the table for Business Data

I will now create a table called “SALES” where I will apply the Row Level Security. It will store the total amount of sales for countries. The idea is that some Sales Managers  (end users) will be able to see only some specific set of rows from this table.

USE ROLE ANALYST;
create table BUSINESS.sales (
  country     varchar,
  total_sales      decimal(20, 2)  
);

Designing the Access Conditions 

These are the multiple conditions that we will implement with our Row Level Access Policy:

• The Sales Manager: We will allow only some specific end users (Sales Managers) to query data from the Sales table. The rest of the users may not see any data. 
• Country: The allowed end user (Sales Managers) will only see rows from specific countries where they have authorization.
• Equal: The allowed end users (Sales Managers) will only see rows where the total amount of sales is equal to this value.  
• Greater than: The allowed end users (Sales Managers) will only see rows where the total amount of sales is greater than this value. 
• Less than: The allowed end users (Sales Managers) will only see rows where the total amount of sales is less than this value. 

At the end, we will have the following table with the access conditions we want to implement with our Row Level Access Policy:

Creating the table with Access Conditions 

This table will be created in the SECURITY schema because it will store all the access conditions to be implemented in the SALES table.

USE ROLE SECURITY_ROLE;
CREATE TABLE SECURITY.SALESACCESS (
  manager varchar,
  country        varchar,
  equal_sales         decimal(20, 2),
  minimum         decimal(20, 2),
  maximum         decimal(20, 2)
);

We will insert the rows with values that represent our table with access conditions (see the table image in the previous step):

insert into security.salesaccess values ('JAMES','USA',1000,150,200);
insert into security.salesaccess values ('JAMES','GTM',2000,250,300);
insert into security.salesaccess values ('JAMES','CAN',3000,350,400);
insert into security.salesaccess values ('JAMES','CRI',4000,450,500);
insert into security.salesaccess values ('JOHN','USA',5000,550,600);
insert into security.salesaccess values ('JOHN','GTM',6000,650,700);
insert into security.salesaccess values ('JOHN','CAN',7000,750,800);
insert into security.salesaccess values ('JOHN','CRI',8000,850,900);

Row Level Access Policy Creation:

Here is where we create our RLA Policy— it’s been created in the SECURITY schema because this object (row level access policy) is part of the security objects. We are implementing all our access conditions; in total we are implementing six access conditions. (1) The Sales Manager (end user), (2) Country, (3) Sales equal to, (4) Sales greater than, (5) Sales less than and one more:

• (6) Admin Access: We are adding another condition that verifies if the user DGOMEZ is executing DML operations against the table, then that user is allowed to see all the data from the SALES table (like an admin). 

USE ROLE SECURITY_ROLE; CREATE OR REPLACE ROW ACCESS POLICY security.country_policy AS (r_country varchar,r_total_sales decimal(20, 2)) returns boolean -> exists ( select 1 from security.salesaccess where ( manager = CURRENT_USER() and country = r_country and (r_total_sales=equal_sales or ( r_total_sales>minimum and r_total_sales<maximum))) ) OR CURRENT_USER()='DGOMEZ';

The magic of Row Level Access is done by the value returned. As you can see, the policy says it will return a “boolean” value. If the value is 1 then the end user (Sales Manager) will see the row; otherwise, data will not be returned. And that value returned (1 | 0) is based on a query. Since a query can be very dynamic, you can implement complex conditions for your Row Level Access Policies. 

Applying Level Access Policy to a Table:

First, we have to grant the ownership on the SALES table to the role SECURITY_ROLE because that role has to apply the Policy and only “owners” can execute “ALTER TABLE” which is the statement it needs to be executed to apply the policy:

USE ROLE ANALYST;
GRANT OWNERSHIP ON TABLE BUSINESS.SALES TO SECURITY_ROLE;
GRANT USAGE ON SCHEMA BUSINESS TO SECURITY_ROLE;

Then, with the SECURITY_ROLE role, the policy is applied on the SALES table:

USE ROLE SECURITY_ROLE;
alter table BUSINESS.sales add row access policy security.country_policy on (country, total_sales);

Finally, the ownership on the SALES table must be granted back to the ANALYST role so that the privileges are in their original state.

GRANT OWNERSHIP ON TABLE BUSINESS.SALES TO ANALYST;

Verify the Row Level Access Policy:

You can confirm if the Row Level Access Policy was applied successfully with the following query:

USE ROLE SECURITY_ROLE;
select *
from table(
  information_schema.policy_references(
    policy_name=>'security.country_policy'
  )
);

The Magic (Results):

Now it’s time to check the Row Level Access Policy in Action. I will connect to Snowflake using JAMES end user, and I will query the SALES table. This is what JAMES can see:

Now I will connect to Snowflake using JOHN end user, and I will query the SALES table. This is what JOHN can see:

Finally, I will connect to Snowflake using the Admin user (DGOMEZ) which we defined in the Row Level Access Policy to see all the data in the SALES table. This is what the user DGOMEZ can see:

WARNING: Row Level Access Policy is not only for SELECTs

It’s very important to know that the Row Level Access Policies are not only for SELECT statements. They also have an impact on UPDATEs and DELETEs (DMLs). For example, in the following image, JOHN is trying to delete all the rows from the SALES table. However, you will see in the result that only 8 rows were deleted, and the SALES table has 16 rows as we saw when we queried the table with the admin (DGOMEZ). This means JOHN can delete only the rows that he is authorized to see. This also happens with UPDATE statements.

Executing DELETE with JOHN:

Now I will use the admin user (DGOMEZ) to query the SALES table in order to see which rows are still there:

Only the rows that JAMES can see are stored in the SALES table, which means that the rows that JOHN was able to delete were only those rows that he was authorized to see. Congratulations, you’ve successfully learned how to secure your data in Snowflake with Row Level Access.

Is your organization adapting to remain competitive? In the Architect Room, we design a roadmap for the future of your digital organization, while still capitalizing on current technology investments. Our knowledge spans a variety of offerings across all of the major public cloud providers. Visit Red Pill Analytics or feel free to reach out on Twitter, Facebook, and LinkedIn.