Snowflake Row-Level Security

Row-level security, or row-based security, is a data access control concept in which access to data in a table is limited according to certain restrictions, and various users, groups or roles may have different permissions on certain rows, based on identities within the rows. Given the right conditions, row-based security can be an important form of data protection control.

For more information about how Satori helps simplify row-level security for Snowflake and other data stores, read our post specific to that.

Take for example a situation in which tables contain sales data for multiple regions or departments, and the organization wants to limit specific teams’ exposure to data about sales in other regions. Such a situation can be simplified by the following diagram which shows an access attempt from a user who “belongs” to region 2. Row-level access control should prevent this user from retrieving data from other regions while preserving her ability to query data about her own region.

Today, we will learn how to implement Snowflake row-level access control, by using its built-in capabilities, as well as by using Satori along with Snowflake.

Add Your Heading Text HereCommon Challenges with Row-Based Security

Row-based security includes some inherent challenges, and there is no silver bullet that solves everything. Rather there are a series of decisions that need to be made according to the reasons for enforcing row-level security and the risk calculations:

To understand the example below, let’s create a sample table called sales_summary, holding fictitious sales data for different customers across different regions:

CREATE TABLE sales_summary (sale_id integer, tcv integer, acv integer, customer_name text, region_id integer);
INSERT INTO sales_summary (sale_id, tcv, acv, customer_name, region_id)
VALUES
(1, 333, 111, 'User 1', 1),
(2, 333, 111, 'User 2', 2),
(3, 333, 111, 'User 3', 1),
(4, 333, 111, 'User 4', 3),
(5, 333, 111, 'User 5', 3),
(6, 333, 111, 'User 6', 2);

Explicit Row-Level Security

Explicit row-level security means that the users have to add specific filtering based on the row-level security criteria when they query the data. A region 2 user, for example, would have to add filtering for their specific region, as shown below:

SELECT sale_id, customer_name, acv, tcv FROM sales_summary

The above specification would fail, though, giving an error that the user must filter the region_id column. Filtering by a region the user does not have access to will also fail and give an error:

SELECT sale_id, customer_name, acv, tcv FROM sales_summary WHERE region_id = 1

Only a query that filters the table according to the user’s allowed regions should work, as shown in the below example:

SELECT sale_id, customer_name, acv, tcv FROM sales_summary WHERE region_id = 2

This process may get a bit more complicated, as users may have access to several regions. In this case, users should be able to filter by any region they have access to.

Learn more:

Implicit Row-Level Security

Implementing an implicit row-level security system limits the results a user receives from the database according to certain access control settings. In this case, the user will query the table without placing any filtering over the region_id column, but the filtering will then be added, either by an abstraction layer of a secure view or by query rewriting.

As per our example, we can create a table containing the row-level security definitions per role as well as a secure view, abstracting data access to the sales_summary table. Let’s create an example of implementing such controls:

Row-level security in snowflake

Step 1: Create a Row-Level Security Configuration Table

CREATE TABLE rows_filtering_by_regions (role_name text, region_id integer);
INSERT INTO rows_filtering_by_regions (role_name, region_id) VALUES 
('REGION2', 2);

As you can see in this sample, we are creating a table that will contain the mapping of roles to regions and answer the question, “Which region can each role view?”

Step 2: Create the Abstract Secure Views in Snowflake

CREATE SECURE VIEW v_sales_summary AS
SELECT sale_id, tcv, acv, customer_name, region_id
FROM sales_summary
WHERE region_id = (
  SELECT region_id FROM rows_filtering_by_regions
  WHERE role_name=CURRENT_ROLE()
);

As shown above, this view gets the original columns from the sales_summary table, but it enforces a filter that selects the region which is configured for the querying user’s current role (using the CURRENT_ROLE() function).

Step 3: Granting Permissions

CREATE ROLE region2;
GRANT ROLE region2 TO USER <the_user_we_want_to_assign>;

GRANT SELECT ON v_sales_summary TO ROLE region2;
GRANT SELECT ON rows_filtering_by_regions TO ROLE region2;

In this step, we are creating the “region2” role, assigning it to the “region 2” users, and granting the role with access to the view we created, as well as to the mapping table (rows_filtering_by_regions). Note that we do not grant permission to view the underlying table (sales_summary), as it contains no restrictions over which rows are being queried.

Now, when we use the role “region2”, we only obtain results for region_id 2:

USE ROLE region2;
SELECT * FROM v_sales_summary;

This is the information that is returned:

Snowflake Row-Based Security for Multiple Conditions

If the requirement is to allow access based on multiple roles (in our case each role adds one or more “regions” which we will be able to view), we can do so by using the CURRENT_AVAILABLE_ROLES() function, which (as its name implies) returns a JSON array of all available roles to the current user. Let’s modify our code accordingly:

Step 1: Adding Another Role with Additional Row-Based Access

CREATE role region1;
GRANT role region1 TO USER <the_user_we_want_to_assign>;
INSERT INTO rows_filtering_by_regions (role_name, region_id) VALUES 
('REGION1', 1);

Step 2: Replace the Snowflake Secure Views with a Multi-Condition Query

DROP VIEW v_sales_summary;
CREATE SECURE VIEW v_sales_summary AS
SELECT sale_id, tcv, acv, customer_name, region_id
FROM sales_summary
WHERE region_id IN (
  SELECT region_id FROM rows_filtering_by_regions
  WHERE role_name IN (SELECT value FROM TABLE(flatten(input => parse_json(CURRENT_AVAILABLE_ROLES()))))
);

As you can see, the “magic” is that, instead of checking for a match for our current role, we look for any matches for any roles the user is granted. In addition, this code allows multiple roles to be tested against each other.

Snowflake Row-Level Security Caveats and Limitations

This example assumes one “region” or “filter” per role, but this is is not always the case. Sometimes, a certain role may have access to several different “regions” (or other filters). Similarly, sometimes the filtering is done over several different columns or may introduce other complexities this method does not account for.
In addition, sometimes we want to implement more than one row-based security condition, or we want to utilize row-based security in addition to Snowflake column-based security.
When using this method, it is important to note that the users have select permission over the mapping table, meaning that they have visibility into the access mapping.
Managing many tables with a large number of roles may be challenging in terms of both architecture and maintenance.
In environments with multiple data stores, this process may be challenging to manage at a large scale.

Row Access Policies in Snowflake

Snowflake introduced a new implementation of row-level security, which enables a more simple and reusable way of handling row-level security. Row access policies are created once, and can be then applied on one or more tables or views. In this case, the policy defines the filtering to apply, so that users are getting rows from the table based on specific conditions.

For example, let’s assume that we want to allow access to the raw sales data based on the regional entitlement of the roles. Let’s create a mock raw sales data on which we will apply the policy, and a mapping table, mapping the entitlement of the different regions to the roles.

First, let’s create the mock data:

CREATE TABLE sales_raw (sales_info string, region string);
INSERT INTO sales_raw VALUES (‘test’, ‘eu’), (‘test2’, ‘us’);

CREATE TABLE sales_entitlements (role_entitled string, region string);
INSERT INTO sales_entitlements VALUES (‘SALES_EU’, ‘eu’), (‘SALES_US’, ‘us’);

Now, let’s create the row access policy, which defines that if the requesting role is SALES_ADMIN, they will see all sales, regardless of region. However, other roles will be looked up in the mapping table, to check if the current role can view data from the specific region:

CREATE ROW ACCESS POLICY regional_access AS
(region_filter VARCHAR) RETURNS BOOLEAN ->
CURRENT_ROLE() = ‘SALES_ADMIN’
OR EXISTS (
SELECT 1 FROM sales_entitlements
WHERE region = region_filter
AND role_entitled = CURRENT_ROLE()
);

Now what we do is apply the regional_access policy on the region column of sales_raw. This flexibility of applying the policy on a specific column (or columns) can help when in another table that column may be called item_region or customer_region instead of the region. Here is the assignment command:

ALTER TABLE sales_raw ADD ROW ACCESS POLICY regional_access ON (region);

Snowflake Row Access Policies can also be applied on external tables, which can make secure data access faster, as it may reduce or eliminate ETLs needed to be done on such data. In addition, you can set both Row Access Policies and Dynamic Masking on the same table. Another thing to note is that Row Access Policies (unlike secure views) also work when deleting or updating data.

Row-Level Security Using Satori Over Snowflake

Satori can be used to set up granular policy controls in order to simplify data access. This process can be performed on top of a single Snowflake data warehouse or on top of your entire data stack. Either way, the basic concept is that the data processed by Satori is classified with Satori tags (such as PII, PCI, and more), as well as with custom tags.

Setting up row-based security in Satori decouples security controls from the data infrastructure and enables policies not to require in-depth knowledge about the data itself. Such control can be set over all relevant tables and warehouses using one policy.

Learn more:

Simple Fine-Grained Access Control with Satori

Advantages of Satori Row-Based Security

You receive the entirety of Satori benefits, such as Universal Audit to quickly handle security and compliance issues in your data stores, out-of-the-box data classification, data analytics, and advanced security policies.
You can set policies that will be applied to all your data stores. Doing so can save a lot of time and risk in setting up the access control and maintaining it.
Satori has the identity context from your identity provider. This means that you can set up row-based security based on data derived from Okta or other IDPs without having to incorporate this information into your data warehouse.
Setting up row-based security via Satori eliminates the need for secure views, which have an optimization penalty for queries.
This system also enables you to set more sophisticated data security controls. For example, you may limit access using row-level access control but only do so for records containing PII, or specific types of PII.
This security system increases your technology independence, as it allows you to safely switch between data technologies without compromising your compliance, security, or privacy, as all Satori controls are universal and remain intact.