POST_START

Implementing Production Row-Level Security with a Country-Based Unity Catalog Row Filter

The Problem: Securing Data Access by Country

I recently had to implement row-level security for a customer database in Databricks. The requirement was simple but critical: only users in the global_sales account group should be able to access all data, while others should only see data for Germany. This meant I needed to enforce a country-based filter at the table level using Unity Catalog.

Creating the Security Function

I started by defining a function that would act as the row filter. The function needed to check if the user belongs to the global_sales account group. If they did, they should be allowed to see all rows. If not, they should only see rows where the country is Germany.

CREATE OR REPLACE FUNCTION production.security.country_filter(country STRING) 
RETURN IF(is_account_group_member('global_sales'), true, country = 'Germany');

I ran this command in my notebook, and it executed successfully. The function is simple but powerful. It uses the is_account_group_member function to determine the user’s group membership, which is a built-in Databricks function that checks if a user is part of a specific account group.

Applying the Row Filter to the Table

Once the function was created, I needed to apply it to the customers table in the production.sales schema. I used the ALTER TABLE command to set the row filter on the table. This step is crucial because it tells Unity Catalog which function to use for filtering rows.

ALTER TABLE production.sales.customers SET ROW FILTER production.security.country_filter ON (country);

I made sure to specify the column country as the argument for the function. This ensures that the filter is applied correctly based on the country value in each row. The command executed without any errors, and I could see the filter being applied to the table.

Verifying the Filter Works as Expected

To confirm that the row filter was working, I ran a simple SELECT query on the customers table. I expected to see all rows if I was in the global_sales group, and only German rows otherwise.

SELECT * FROM production.sales.customers;

I ran this query and was pleased to see that all rows were returned. I then checked with a colleague who wasn’t in the global_sales group, and they only saw rows where the country was Germany. This confirmed that the row filter was working as intended.

I also noticed that the filter is applied at the Unity Catalog level, which means it’s enforced across all queries and tools that access the table. This provides a consistent and secure way to control data access without modifying application code.

Conclusion: Securing Data with Unity Catalog

By implementing the country-based row filter using a Unity Catalog function, I was able to enforce secure access to the customers table. This approach is both efficient and scalable, allowing different groups to access data based on their roles without compromising security or data integrity.

This experience taught me how powerful Unity Catalog can be when it comes to managing data access in a production environment. It’s a great example of how to combine security with usability in a data platform.