Question

A healthcare analytics team stores a patient table in Unity Catalog that includes a sensitive 'ssn' column. Analysts need to run reports against the table, but only members of the 'compliance_team' group should see the full SSN values; everyone else should see the values masked. The data engineer must implement this without duplicating the table or removing the column. What is the most appropriate approach?

Accepted Answer

Create a dynamic view over the table that uses the is_account_group_member() function in a CASE expression to mask the 'ssn' column, then grant analysts SELECT on the view instead of the base table.

Answer

Grant analysts SELECT on the base table and rely on Unity Catalog to automatically mask sensitive columns based on their name.

Answer

Create a separate copy of the table with the 'ssn' column removed and grant analysts SELECT on that copy.

Answer

Revoke SELECT from analysts on the base table and instead grant them MODIFY so they can query but not read the SSN column.