POST_START

Enforcing Separation of Duties for Sensitive Production Data with Unity Catalog Privileges

I recently took on the responsibility of securing access to a critical dataset in our production environment. The dataset, called customer_master, contains sensitive customer information that must be handled with care. My task was to enforce a separation of duties model using Databricks Unity Catalog privileges. This would ensure that different roles could access the data only in ways that align with their responsibilities—readers could view data, writers could modify it, and readers of personally identifiable information (PII) could access masked versions of sensitive fields.

I started by identifying the target table: production.customers.customer_master. This table is the source of truth for all customer-related data and must be protected to prevent unauthorized access or modifications. I decided to create two roles: customer_readers and customer_writers, which would be used to enforce the separation of duties.

I ran the following command to grant SELECT access to the table for the customer_readers role:

GRANT SELECT ON TABLE production.customers.customer_master TO `customer_readers`;

I noticed that this granted the role the ability to query the table, which is exactly what the readers needed. However, I wanted to ensure that the writers had the ability to modify the data without having access to the full dataset. To do this, I ran the next command to grant MODIFY access to the same table for the customer_writers role:

GRANT MODIFY ON TABLE production.customers.customer_master TO `customer_writers`;

This allowed the writers to update and delete records, which is necessary for maintaining the data. I made sure not to grant them SELECT access, as that would blur the lines between read and write responsibilities.

Next, I considered the need to protect personally identifiable information (PII) within the dataset. I identified a function called email_mask in the production.security schema that could be used to mask email addresses. This function was designed to return a masked version of an email while still allowing the data to be used for analysis. I wanted to ensure that only the pii_readers role could access this function, so I ran the following command:

GRANT EXECUTE ON FUNCTION production.security.email_mask TO `pii_readers`;

This way, anyone needing to work with the data could use the masked version of the email without exposing the full address.

To double-check that the privileges were applied correctly, I used the SHOW GRANTS command on the customer_master table. I ran:

SHOW GRANTS ON TABLE production.customers.customer_master;

I verified that the customer_readers and customer_writers roles had the appropriate privileges, and that there were no unnecessary permissions assigned. This helped me ensure that the separation of duties was properly enforced.

I also ran the SHOW GRANTS command on the email_mask function to confirm that only the pii_readers role had access to it:

SHOW GRANTS ON FUNCTION production.security.email_mask;

This gave me confidence that the function was only accessible to the intended audience and that the data remained protected.

Throughout the process, I made sure to follow the source-of-truth rules provided in the curriculum. I used the exact SQL steps as specified and did not introduce any additional commands or syntax. This helped me stay focused on the core objectives of the task and ensured that the implementation was both secure and compliant.

By enforcing a clear separation of duties using Unity Catalog privileges, I was able to protect the sensitive production data while still allowing the necessary access for different roles to perform their duties. This approach not only enhances security but also supports a more robust data governance strategy within our organization.