POST_START

Designing Department-Based Row-Level Security for Enterprise Tables

Setting Up the Security Function

I started by thinking about how to secure our enterprise tables based on department access. I needed a way to filter rows so that users could only see data relevant to their department. The solution was to create a security function that checks if a user belongs to a specific department group.

I ran the following SQL to create the security function:

CREATE FUNCTION production.security.department_filter(department STRING) 
RETURN is_account_group_member(department);

I saw a representative result like this:

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

This function uses the built-in is_account_group_member to verify if the current user is part of the specified department group. It’s a simple but powerful mechanism for controlling access at the row level.

Applying Row-Level Security to the Table

With the function in place, I moved on to applying it to the table where I wanted to enforce department-based access. The table in question was production.hr.employee_data, which holds sensitive employee information.

I executed the following command to set the row filter on the table:

ALTER TABLE production.hr.employee_data 
SET ROW FILTER production.security.department_filter ON (department);

I saw a representative result like this:

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

This step binds the security function to the table, ensuring that whenever a user queries the table, the function is applied. Only rows where the user belongs to the corresponding department group will be returned. This is a clean and declarative way to enforce security without modifying application logic.

Verifying the Security in Action

Finally, I wanted to verify that the row-level security was working as expected. I ran a simple SELECT statement to fetch all data from the secured table:

SELECT * FROM production.hr.employee_data;

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 confirmed that the security filter was in effect. The data returned was limited to the rows that the current user had access to, based on their department group. It was a satisfying moment to see the system enforce access control automatically.

By following these steps, I was able to implement a robust and scalable department-based row-level security solution using Unity Catalog. This approach ensures that sensitive data is protected without compromising the usability of the table for authorized users.