POST_START

Combining Row Filtering and Column Masking on the Same Customer Table

I recently had the task of securing sensitive customer data in our production environment. The goal was to ensure that only authorized users could access specific rows and columns in the customer table. I decided to implement both row filtering and column masking on the same table to achieve this. Today, I’ll walk through the steps I took to set this up in Databricks Unity Catalog.

Setting Up Row Filtering

I started by applying row filtering to the customer table so that only users with the appropriate permissions could see data for specific regions. I used the ALTER TABLE command to set the row filter on the region column. This would ensure that any query against the table would automatically apply the filter based on the user’s access.

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

I noticed that the command executed without any errors, which was a good sign. This means the row filter was successfully applied to the table. The next step was to ensure that the column masking was also in place for sensitive data like email addresses.

Applying Column Masking

To protect the email addresses in the table, I decided to apply column masking. I used the ALTER TABLE command again, this time to alter the email column and set a mask using the production.security.email_mask mask. This would ensure that even if someone had access to the table, they would only see masked email addresses.

ALTER TABLE production.customers.customer_master ALTER COLUMN email SET MASK production.security.email_mask;

After running the command, I confirmed that the email column was now masked. This means that any query against the table would return the masked email addresses, adding an extra layer of security to our data.

Verifying the Combined Effect

To ensure that both the row filtering and column masking were working as intended, I ran a simple SELECT query to retrieve some data from the customer table. I wanted to see if the row filter was limiting the rows returned and if the email column was masked.

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

<

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

I verified the results and saw that the query returned only the rows that matched the row filter, which was based on the region. Additionally, the email addresses were masked, confirming that both row filtering and column masking were applied correctly. This gave me confidence that the data was now more secure and that only authorized users could access sensitive information.

I learned that combining row filtering and column masking is a powerful way to control access to sensitive data. It ensures that both the data and the users are properly protected, which is essential in a production environment.