As adoption of the Snowflake Data Cloud has grown, we’ve seen some of the world’s most demanding organizations using Snowflake to mobilize their data by eliminating data silos and breaking down barriers—all while maintaining high standards of compliance and governance.
To accelerate their data governance initiatives, enterprises need a set of access controls that can be consistently applied to different workloads across clouds and multiple geographic regions. Specifically, data governance teams want to ensure that users can view only column-level and row-level data that they are authorized to access.
Earlier this year, we announced the general availability of Dynamic Data Masking, which enhances column-level security in Snowflake’s Data Cloud . Today, we are proud to follow up by announcing the general availability of the Row Access Policies feature, which enhances row-level security in the Data Cloud. During the preview period, multiple Fortune 500 customers in the financial services, technology, retail, and healthcare sectors used Row Access Policies to make data easily accessible to thousands of users in a secure way, removing barriers to data access and driving new data insights.
«Granular access control of rows of sensitive data makes it easier for Capital One to comply with strict regulatory requirements. However, attempting this in most data platforms leads to proliferating silos and increasing overhead to maintain multiple views of the same data. Snowflake’s new Row Access Policies feature addresses our governance needs with flexible policies that can be applied to multiple tables. This unique approach allows us to more easily scale governance for a large, regulated environment without sacrificing freedom and innovation.»
–Salim Syed, Senior Director of Data Engineering at Capital One
Row Access Policies enable enterprises to secure data using fine-grained, content-based access control. They simplify data governance and improve the security posture by eliminating the need for data silos for different groups of users.
Snowflake’s approach can govern data at scale. Row Access Policies were designed from the ground up to be easy to manage in organizations large and small:
- A single policy can be applied to multiple tables, which greatly simplifies policy administration, change management, and compliance reporting.
- For organizations with many users and complex authorization logic, Snowflake supports a data-driven approach where authorizations are stored in mapping tables.
- Snowflake supports a hybrid policy administration model: Policy administrators can define and manage policies centrally, while data stewards throughout the organization apply these policies to data.
- Row Access Policies can be replicated securely through Snowgrid and applied across multiple accounts and regions. This enables enterprises to easily achieve a consistent security posture worldwide.
Further, row access policies are integrated seamlessly across Snowflake’s Data Cloud. With Snowflake’s unique replication feature, you can consistently apply policies to all of your Snowflake accounts, ensuring governance across regions and even across different clouds. You can also implement row access policies for diverse use cases and workloads within Snowflake, whether that is for restricting row access to data shared with consumer accounts, data stored in external tables, semi-structured JSON data, or data pipelines via streams.
How Row Access Policies Work
Let’s take a look at a simple example to understand how Row Access Policies work. To ensure that regional sales data is restricted to users local to the geographic region, suppose that you want to restrict access to regional sales data to users in the region. As shown in the figure below, you have a client information table (client_info) containing restricted information from multiple geographies: their names, phone numbers, and region. You are required to restrict access to client information in a region to salespeople in the same region. Sales managers can access client information across multiple geographies.
You need to perform only three simple steps to use Row Access Policies to accomplish row-level security:
- Define a policy and optionally define a mapping table.
- Apply the policy to one or more tables.
- Query the data.
You create a row access policy as shown in the example below, which would allow access to all rows if the table is queried using the SALES_MANAGER role. Unauthorized rows are filtered for SALES_NA and SALES_EU roles.
You then apply the sales_regionalization policy to the client_info table.
All subsequent data access on the client_info table is filtered according to the authorization in the row access policy.
A unique advantage to Snowflake’s policy-based approach is the ability to apply the same policy to protect multiple tables. As an example, if you have a partner information table (partner_info) containing restricted information and you want to restrict access to partner information in a region to salespeople in the same region, you can apply the same sales_regionalization policy you created earlier to partner_info, as shown below.
As a result, all data access on the partner_info table is filtered according to the policy.
Note that these are simple examples to illustrate how Row Access Policies work. A row access policy expression can be as simple as allowing one particular role to view rows, or it can be as complex as required to restrict access to rows in the query result. In fact, many enterprises may already have a mapping of users or roles and their respective entitlements similar to the mapping table described next.
Example: Mapping Table
In the previous example, we demonstrated a standalone row access policy, where the policy logic was embedded in the policy definition. Snowflake also makes it easy to manage more-complex policy conditions through several techniques: Complex policies can be decomposed using «helper» UDFs, and they can consult mapping tables. The example below demonstrates the mapping table approach.
A mapping table maps some context, such as a role, user name, or Snowflake account name, to a specification of the content that may be accessed. In the example below, the mapping table maps sales roles to the region codes that each sales role can access.
Mapping Table: Region_Entitlement
In this case, you can create a row access policy as shown in the following example such that unauthorized rows are filtered based on the authorization defined in the mapping table.
You can start using Row Access Policies today to evaluate the benefits of policy-based row-level security for your use cases. You can learn more about Row Access Policies in the Snowflake documentation and in this best practices session from Snowflake Summit. Please share your feedback or request enhancements through the Snowflake Community portal.