POST_START

Implementing Row-Level Access Control with Row Filters

I recently needed to implement row-level access control for a sales team in Databricks. The goal was to ensure that only members of the ‘global_sales’ account group could see all customer data, while others could only access data for Germany. I decided to use Unity Catalog’s row filters to achieve this.

Creating the Row Filter Function

I started by creating a function that would act as the row filter. This function would check if the user is a member of the ‘global_sales’ group. If they are, they would see all rows. Otherwise, they would only see rows where the country is Germany.

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

I verified that the function was created successfully. The function uses the is_account_group_member function, which is part of Unity Catalog’s security features, to determine the user’s group membership.

Applying the Row Filter to the Table

Next, I applied the row filter to the training.sales.customers table. This means that any query against this table will automatically apply the filter defined in the function.

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

I noticed that the table now has a row filter applied. This means that when users query the table, the filter will be enforced based on their group membership. This is a powerful feature for controlling access without modifying queries.

Querying the Filtered Table

To see how the row filter works in practice, I ran a simple SELECT query against the table.

SELECT * FROM training.sales.customers;

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

I verified that the query returned only the rows that matched the filter. Since I am not a member of the ‘global_sales’ group, I only saw customers from Germany. If I were a member of the group, I would have seen all customers regardless of their country.

This approach allows me to enforce access control at the data level, ensuring that users only see the data they’re authorized to access. It’s a clean and efficient way to manage security in a Databricks environment.