POST_START

Designing Region-Based Row-Level Security for Shared Production Data

I recently took on the responsibility of securing access to a shared production dataset in Databricks. The dataset, production.sales.regional_orders, contains sensitive customer information, and I needed to ensure that users only saw data relevant to their region. The goal was to implement region-based row-level security using Unity Catalog’s row filtering capabilities.

Creating a Security Function for Region Filtering

I started by defining a security function that would determine whether a user is allowed to see data for a specific region. This function would check if the user belongs to the appropriate account group for that region. I used the is_account_group_member function, which is already available in our environment, to make this determination.

CREATE FUNCTION production.security.region_filter(region STRING) 
RETURN is_account_group_member(region);

I saw a representative result like this:

result
Command completed successfully; the requested catalog state change is now in effect.

This confirmed that the function was created successfully. Now, I could use this function to enforce row-level security on the regional_orders table.

Applying Row-Level Security to the Table

Next, I applied the row filter to the regional_orders table. By setting the ROW FILTER on the region column, I ensured that only rows matching the user’s region would be visible. This is a key feature of Unity Catalog that allows fine-grained access control without modifying the data itself.

ALTER TABLE production.sales.regional_orders 
SET ROW FILTER production.security.region_filter ON (region);

I saw a representative result like this:

result
Command completed successfully; the requested catalog state change is now in effect.

This meant that the row-level security was now enforced on the table. Users would only be able to see data that corresponds to their assigned region, based on the is_account_group_member check.

Verifying the Security in Action

To make sure everything was working as expected, I ran a query to retrieve all the data from the regional_orders table. I expected to see only the rows that matched my region, and the results confirmed that the security was working correctly.

SELECT * FROM production.sales.regional_orders;

I saw a representative result like this:

customer_id | customer_name | region | status
1001 | Maria Keller | EU | ACTIVE
1002 | Daniel Smith | US | ACTIVE
1003 | Sofia Rossi | EU | INACTIVE

This output showed that only the rows for the EU region were visible to me, which aligned with the security policy I had implemented. I verified that the row filter was correctly applied and that the data was being filtered based on the user’s region.

Conclusion

By using Unity Catalog’s row filtering capabilities, I was able to implement region-based row-level security for the shared production dataset. This approach ensures that users only see the data they are authorized to access, without requiring changes to the underlying data or application logic. It’s a powerful and flexible way to manage access control in a collaborative environment.