This article is also published as a chapter in our Snowflake Security guide, covering many other topics. Visit our Snowflake Security Guide in its entirety, where you can also download the entire guide as a PDF e-book.
2021-07-27 Added a Snowflake Row Access Policies section.
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.
Common Challenges with Row-Based SecurityRow-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:
Explicit Row-Level SecurityExplicit 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: 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: Only a query that filters the table according to the user’s allowed regions should work, as shown in the below example: 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 SecurityImplementing 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 snowflakeStep 1: Create a Row-Level Security Configuration Table 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 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 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: This is the information that is returned:
Snowflake Row-Based Security for Multiple ConditionsIf 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 AccessStep 2: Replace the Snowflake Secure Views with a Multi-Condition Query 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 SnowflakeSnowflake 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');|
|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() );|
|ALTER TABLE sales_raw ADD ROW ACCESS POLICY regional_access ON (region);|
Row-Level Security Using Satori Over SnowflakeSatori 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 that 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):
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.