Implementing Column and Row-Level Security with Views

1. Dynamic Views: The Engine of Granular Security

The primary mechanism for implementing row and column-level security in Unity Catalog is the dynamic view. A dynamic view is a standard SQL view that includes functions to customize the data it returns based on the identity of the user running the query.

The two key functions that make this possible are:

• is_account_group_member('group_name'): This function returns TRUE if the current user is a member of the specified account-level group. This is the recommended function for implementing security policies in Unity Catalog.
• current_user(): This function returns the email address of the current user. We will explore this function in more detail in the next lesson.

By embedding these functions within a view's definition, we can create powerful and flexible security rules.

2. Column-Level Security: Hiding Sensitive Columns

Column-level security is used to restrict access to specific columns containing sensitive information, such as Personally Identifiable Information (PII). The standard pattern is to use a CASE statement within the SELECT clause of your view.

The logic is simple:

• CASE WHEN is_account_group_member('privileged_group') THEN sensitive_column
• ELSE 'REDACTED'
• END AS sensitive_column

This logic checks if the user belongs to a group with permission to see the data. If they do, the view returns the actual column value. If not, it returns a placeholder like 'REDACTED', NULL, or a masked version of the data.

Let's watch a short video that demonstrates how to implement this in a Databricks notebook.

3. Row-Level Security: Filtering Rows by User

Row-level security ensures that users can only see the rows of data that are relevant to them. For example, a sales manager for the US should only see sales records from the US.

The pattern here involves using the is_account_group_member() function within the WHERE clause of the view definition.

For instance, to filter a sales table by region, the WHERE clause might look like this:
WHERE is_account_group_member(sales_region)

This dynamically filters the table so that a user who is a member of the USA group will only see rows where the sales_region column is 'USA'.

4. Data Masking: A More Advanced Form of Column-Level Security

Sometimes, you don't want to hide a column entirely but rather obscure parts of it. This is called data masking. A common example is masking an email address to show only the domain (e.g., jane.doe@example.com becomes example.com).

This is achieved using the same CASE statement pattern as column-level security, but instead of returning a static 'REDACTED' string, you use SQL functions like regexp_extract to transform the data.

The video from The Data Master also covers this technique.

5. Hands-On Practice

Now it's time to apply these concepts. The best way to learn is by doing. In this exercise, you will create a sample table and then build two separate views to enforce column-level and row-level security.

Instructions:

1. Open a new SQL notebook in your Databricks workspace. Make sure it's attached to a running SQL warehouse or a cluster in standard access mode.

2. Setup: Run the following SQL to create a sample table with employee data.

   -- Ensure we are using a catalog where we have creation rights, like the main catalog
   USE CATALOG main;
   CREATE SCHEMA IF NOT EXISTS governance_tutorial;
   USE SCHEMA governance_tutorial;
   
   -- Create the employee table
   CREATE OR REPLACE TABLE employees (
     employee_id INT,
     name STRING,
     department STRING,
     country STRING,
     salary DECIMAL(10, 2)
   );
   
   -- Insert sample data
   INSERT INTO employees VALUES
     (1, 'Alice', 'Engineering', 'USA', 120000.00),
     (2, 'Bob', 'HR', 'USA', 95000.00),
     (3, 'Charlie', 'Engineering', 'UK', 110000.00),
     (4, 'Diana', 'Sales', 'UK', 85000.00),
     (5, 'Eve', 'HR', 'USA', 105000.00);
   

3. Create Groups: Go to the Admin Console in your workspace and create two groups: hr_team and uk_managers. Add your own user account as a member of both groups.

4. Task 1: Implement Column-Level Security
   Create a view named employees_redacted_salary that shows the salary column only to members of the hr_team group. Other users should see a salary of 0.00.

   -- Paste and run this code
   CREATE OR REPLACE VIEW employees_redacted_salary AS
   SELECT
     employee_id,
     name,
     department,
     country,
     CASE
       WHEN is_account_group_member('hr_team') THEN salary
       ELSE 0.00
     END AS salary
   FROM employees;
   

   Now, query your new view: SELECT * FROM employees_redacted_salary;. Since you are in the hr_team group, you should see all the real salaries. If you were to remove yourself from the group and re-run the query, you would see 0.00 for all salaries.

5. Task 2: Implement Row-Level Security
   Create a view named employees_uk_only that shows employee records only for the 'UK'. This access should be restricted to members of the uk_managers group. All other users should see no rows.

   -- Paste and run this code
   CREATE OR REPLACE VIEW employees_uk_only AS
   SELECT *
   FROM employees
   WHERE
     is_account_group_member('uk_managers') AND country = 'UK';
   

   Now, query this view: SELECT * FROM employees_uk_only;. Because you are a member of uk_managers, you should see the two records for employees in the 'UK'.

Conclusion

In this lesson, you have learned how to enforce granular data security policies in Databricks using dynamic views. This is a crucial skill for protecting sensitive data and ensuring compliance.

Key Takeaways

• Dynamic views are the primary tool for implementing column-level and row-level security in Unity Catalog.
• Column-level security is achieved using a CASE statement in the SELECT clause, often combined with is_account_group_member() to show or redact column values.
• Row-level security is implemented by adding a WHERE clause to the view that filters rows based on the user's identity or group membership.
• Data masking is an advanced form of column-level security that partially obscures data rather than completely hiding it, using functions like regexp_extract.

Next Steps

We've focused on using group membership (is_account_group_member) to drive our security rules, which is the most common and scalable approach. In the next lesson, we will expand on this by exploring how to use the current_user() function to create even more personalized and dynamic access controls.