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 controls.
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.
Common 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 which 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.
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 which 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-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.
This is an example of a real-life Satori row-based security policy companies apply for GDPR compliance. It verifies if the user is using the US_TEAM role, and, if they are, the policy checks if any customer data is retrieved (this is a tag which may be configured to include any data you would like to limit access to). In a case where the data is not filtered only for US customers, the request is blocked (action: block):
- name: Prevent US Team from Accessing EU Customer Data (GDPR) action: block identity_tags: - identity.datastore.role::us_team data_tags: - customer_data - NOT filtered_by_us
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 which 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.
Interested in implementing row-based security controls using Satori or seeing how we can help you save resources on security, privacy, and compliance and concentrate on analytics? Learn more about us!