Snowflake’s Row Access Policies provides granular control over data access and security in Data Engineering pipeline. They enable organizations to maintain data privacy, ensure compliance, and enhance overall data governance within their data warehousing environment.
Snowflake Data Cloud, row-level security (RLS) is achieved by utilizing row access policies that determine the rows in query results. These policies can vary in complexity, ranging from simple tasks such as granting specific roles access to particular rows to more complex processes that involve mapping tables to determine row access in query results.
The row-access policy is a schema-level object, which, when applied, will affect the following statements:
Select statements
Update, delete, and merge on selected rows
Similar to the Dynamic Data Masking policy, row-access policies in Snowflake provide flexibility by including conditions and functions in the policy expression. This enables data transformation at query runtime based on specific conditions.
This policy-driven approach promotes the segregation of duties (like Dynamic Data Masking), enabling governance teams to define policies restricting sensitive data exposure. The object owner, typically the role with OWNERSHIP privilege on the object (e.g., table or view), retains full access to the underlying data. Moreover, a single policy can be applied simultaneously to multiple tables and views.
In this blog, we will look at how to configure row-access policies and apply them to tables. We will also address the limitations and important considerations when configuring these policies across multiple tables and views.
The Process of Creating Row-Access Policy
The diagram below represents RBAC, which can be integrated with row-level security features using a mapping table. The data in the sales table is restricted by the department based on the role using row-level security features.
Steps to Configure Row-level Security
Step 1: Let’s create a sales table with a ‘Division’ column, which we will use to implement the policy. We will insert dummy data, as shown below.
-- create table sales
create or replace table sales (
order_number varchar,
division varchar,
quantity number,
price float,
order_date timestamp,
status varchar
);
--insert values
insert into sales
values('123','kitchen',20,1050.50,'2023-06-13','PENDING'),
('234','office',50,5050.40,'2023-03-14','COMPLETED'),
('345','office',30,2050.10,'2023-04-10','COMPLETED'),
('005','kitchen',50,1350.50,'2023-06-10','PENDING'),
('010','office',40,67850.50,'2023-06-01','COMPLETED')
;
--verify the table data
select * from sales;
Step 2: Create a ‘Role Mapping’ table with two columns: ‘Division’ and ‘Role’. We will use these two fields to apply the row-access policy on the sales table.
--create role mappings table
create or replace table role_mappings(
division varchar,
role_name varchar);
--insert the data ( department and roles)
insert overwrite into role_mappings values
('office' , 'DEMO_OFFICE_SALES'),
('kitchen' ,'DEMO_KITCHEN_SALES'),
('office','WS_DGANIGER'),
('kitchen', 'WS_DGANIGER');
--verify the data
select * from role_mappings;
Step 3: If no existing roles exist, create new roles and grant them to the user(s). For this tutorial, we will create a ‘sales office’ role and a ‘sales kitchen’ role and attach one user to each.
-- create the roles based on the sales departments
create role demo_office_sales;
create role demo_kitchen_sales;
--grant the roles to the users of the department
grant role demo_office_sales to user dganiger;
grant role demo_kitchen_sales to user dganiger;
Step 4: Create a row-access policy by using the argument division_code and boolean return type. The policy body will verify whether a specific role has access to a particular division by using the role-mapping table.
--create row access policy
create or replace row access policy division_user_mapping_policy as
(division_code varchar)
returns boolean ->
exists(select 1 from role_mappings
where role_name = current_role()
and division = division_code);
Now, alter the sales table to add a row-access policy on the “Division” column.
--add the row access policy to Sales table
alter table sales add row access policy division_user_mapping_policy on (division);
Step 5: Next, we’ll need to provide appropriate grants to both roles, giving access to the sales table data. This step involves providing grants to the warehouse, database, schema, and table.
-- grant access to office sales role
grant usage, operate on warehouse default_user_wh to role demo_office_sales;
grant usage on database user_dganiger to role demo_office_sales;
grant usage on schema analytics to role demo_office_sales;
grant select on table sales to role demo_office_sales;
-- grant access to kitchen sales role
grant usage, operate on warehouse default_user_wh to role demo_kitchen_sales;
grant usage on database user_dganiger to role demo_kitchen_sales;
grant usage on schema analytics to role demo_kitchen_sales;
grant select on table sales to role demo_kitchen_sales;
Step 6: Test the row-access policy for each role to verify whether the data view securely provides access to the selected divisions.
--switch the role to office sales
use role demo_office_sales;
--validate office sales data only that is being generated
select * from sales;
--switch the role to kitchen sales
use role demo_kitchen_sales;
--validate kitchen sales data only that is being generated
select * from sales;
Step 7: This step is optional, but If the policy admin needs to view all data, then the role-mapping table must be mapped for the admin.
--switch the role to admin
use role ws_dganiger;
--all data is visible to admin role
select * from sales;
Limitations of Row-Level Security
While row-level security in Snowflake provides powerful access control capabilities, there are certain limitations to be aware of.
Performance Impact: Implementing row-level security can introduce overhead and impact query performance. The predicates defined in security policies must be evaluated for each query execution, potentially slowing down data retrieval.
Complexity: Defining and managing security policies can become complex, especially in scenarios involving multiple tables, complex predicates, or frequent changes to access control rules. This requires careful planning and maintenance to ensure policies are accurate and up to date.
Limited Control over Metadata Access: Row-level security in Snowflake focuses on controlling access to data rows, but it does not provide granular control over metadata access. Users granted access to a table with RLS can see the table’s metadata, such as column names and data types.
Policy Execution Order: When multiple security policies are defined for a table, the order in which they are executed can have an impact on the results. It’s essential to understand the policy execution order and ensure that conflicting policies do not lead to unexpected data access issues.
Conclusion
Despite these limitations, Snowflake’s RLS feature remains valuable for enforcing row-level access control and enhancing data security in many use cases. It is important to consider these limitations carefully and design security policies that align with your requirements.
If you need additional help or are curious about how to leverage row-level security better, reach out to our team of Snowflake experts today for help, guidance, and best practices!
