Implement row access policies to transform data access and storage

Simplifying security step by step with Snowflake row access policies

Presented by Jack Beck and Logan Guerry

Your company is storing more and more potentially sensitive data in Snowflake. Controlling access to that data is obviously vital, but building a solution sounds complicated. You have to identify who should have access to what data to ensure employees easily get the data they need while making the data they don’t need invisible to them.

Fortunately, row-level security (RLS) simplifies the challenge. RLS is a feature implemented in databases that allows for fine-grained, content-based access control defined by customizable user roles. It lets your company’s security administrators control rows in database objects through user attributes/roles/groups or query execution context.

That means less need for redundant data stores – for example, you just need one database instead of siloed data stores for various geographies, teams, or tiers of restricted data access permissions. You can also minimize or even eliminate the need to filter query output or switch query context, and ultimately deliver a simplified user experience.

Seamless scaling with Snowflake

This summer, controlling data access through RLS got even easier and more effective with Snowflake’s new row access policies. Released for Snowflake Enterprise Accounts in July, row access policies are designed to work in conjunction with Dynamic Data Masking, which operates at the column level. They expand on Dynamic Data Masks by enabling companies to secure data at row-level granularity, ensuring users only see content appropriate to their role. 

The result? No need to silo data based on user group and applicability to diverse use cases. You can restrict access within data shares, external tables, pipelines, and on all data types, including semi-structured.

One of the best features of Snowflake’s row access policies is their scalability. Theoretically, one row access policy can be applied to an infinite number of tables, simplifying policy administration and change management. Scaling is seamless and migration is easy.

Their scalability and other features make row access policies ideal for organizations with complex authorization logic or large user bases. Policy administrators can define and manage policies centrally, while lower-level data users apply the policies to data throughout the organization. For international users, policies can be applied across multiple accounts and regions, allowing for consistent security worldwide.

Getting it done, step by step

Creating and implementing row access policies is relatively straightforward, but you should keep some key roles and best practices in mind – especially maintaining separate system administrator and security administrator roles.

• The process begins by working with your security administrator to set up the roles that you want to dynamically control access between.
• Next, your system administrator will need to set up the functionality that grants usage of various roles to relevant tables.
• Creating your security schema comes next, and then a mapping table that stores unique combinations of a given role name and a corresponding key to filter row access.
• At this point, your security administrator steps in again, creating a role whose only job is handling queries to security tables and to keep other roles from accessing them.
• Then back to your system administrator to run the DDL and DCL statement for creating row access policies and applying them to the requisite tables. The row access policy itself is simply a centrally defined procedure that returns a Boolean value (TRUE or FALSE) based on whether a user is allowed to view a specific row.

Using these basic steps, Snowflake’s row access policies can transform the way your organization stores and accesses data. You can break down silos, eliminate multiple databases and redundant stories, and give your employees easy access to the data they need. All while enhancing security and seamlessly transitioning to a flexible data platform that is virtually infinitely scalable.

For a recorded demo of Snowflake’s row access policies provided by DAS42 at a recent meeting of the Denver Snowflake User’s Group, please click here. For more information about the User’s Group, please reach out to DAS42 or check out the Snowflake User Group.