POST_START

Hardening Row-Level Security with a Controlled Administrator Bypass

I recently had the task of hardening row-level security for a customer table in our data lake. The goal was to enforce a region-based filter, but also allow for a controlled bypass for administrators. I needed to implement a function that would restrict access to data based on the user’s group membership and the region they’re querying. I decided to use a function in Unity Catalog to achieve this.

I started by defining a function that would act as a row filter. This function would check if the user is part of the data governance admins group, or if they are a regional analyst and are querying data for the ‘EU’ region. I used the is_account_group_member function to determine the user’s group membership.

CREATE OR REPLACE FUNCTION production.security.customer_region_filter(region STRING) 
RETURN is_account_group_member('data_governance_admins') OR 
       (is_account_group_member('regional_analysts') AND region = 'EU');

I verified that the function was created successfully. The function now acts as a gatekeeper for data access, ensuring that only authorized users can see the data they’re supposed to.

Next, I applied this function as a row filter to the customer_master table in the production schema. This would enforce the security policy at the row level, ensuring that users can only access rows that match their permissions.

ALTER TABLE production.customers.customer_master SET ROW FILTER production.security.customer_region_filter ON (region);

I noticed that the table now had a row filter applied, and I was confident that any queries against this table would now be subject to the security rules defined in the function. This means that regional analysts can only access data for the ‘EU’ region, while data governance admins can bypass the region filter entirely.

To test the implementation, I ran a query to retrieve some customer data and verify the behavior. I expected to see only the rows that matched the filter conditions.

SELECT customer_id, region, email FROM production.customers.customer_master;

I verified that the query returned the expected results. The ‘US’ region rows were not included, which confirmed that the filter was working as intended. The ‘EU’ region rows were included, which aligns with the logic of the function. I also noticed that the status column was included in the output, which was part of the original table structure.

Through this exercise, I learned how to use functions in Unity Catalog to enforce row-level security. I also saw how to allow for a controlled bypass by including the data governance admins group in the filter logic. This approach provides a balance between security and flexibility, ensuring that only the right people can access the right data.