Databricks Unity Catalog Tutorial: Addressing Privilege Drift After Data Migration

This tutorial focuses on mitigating privilege drift after migrating data to Databricks Unity Catalog using Databricks SQL. We’ll walk through three scripts demonstrating progressive steps in identifying and correcting privilege discrepancies.

Script 1: Initial Privilege Assessment – Identifying the Drift

The goal of this script is to create two tables, one in the legacy Hive metastore and another in Unity Catalog, and then perform a SELECT statement to highlight the potential privilege differences. We’ll create a simple sales dataset for demonstration.

-- Create a legacy Hive table
CREATE TABLE legacy_sales (
    sales_id INT,
    product_name STRING,
    sales_amount DECIMAL(10, 2)
)
USING PARQUET
;

-- Insert sample data into the legacy table
INSERT INTO legacy_sales (sales_id, product_name, sales_amount)
VALUES
(1, 'Laptop', 1200.00),
(2, 'Mouse', 25.00),
(3, 'Keyboard', 75.00);

-- Create a Unity Catalog table
CREATE TABLE unity_catalog_sales (
    sales_id INT,
    product_name STRING,
    sales_amount DECIMAL(10, 2)
)
IN SCHEMA sales_catalog;

-- Insert sample data into the Unity Catalog table
INSERT INTO unity_catalog_sales (sales_id, product_name, sales_amount)
VALUES
(1, 'Laptop', 1200.00),
(2, 'Mouse', 25.00),
(3, 'Keyboard', 75.00);

-- Perform a SELECT statement to compare privileges (using a user with read access)
SELECT 
FROM legacy_sales
INTERSECT
SELECT 
FROM unity_catalog_sales;

This script creates two identical tables. The `INTERSECT` statement then attempts to return the common rows from both tables. This will demonstrate the initial difference in privileges – the user executing this query might not have access to the legacy Hive table, resulting in an empty result set. This is the symptom of privilege drift.

Key Concept: Unity Catalog introduces a data governance layer that fundamentally changes access control. The `INTERSECT` operation highlights how the legacy metastore and Unity Catalog require different access mechanisms.

Best Practice: Always start with a thorough privilege assessment before migrating data to Unity Catalog to understand the implications for your users.

-- Output: []

Script 2: Granting Permissions to Unity Catalog

This script focuses on granting the necessary permissions to the user to access the Unity Catalog table. We’ll use `GRANT SELECT` to allow the user to read data from the `unity_catalog_sales` table.

-- Grant SELECT privilege to a user on the unity_catalog_sales table
GRANT SELECT ON TABLE unity_catalog_sales TO 'user_access_role';

-- Confirm the grant
SHOW GRANTS FOR USER 'user_access_role';

This script grants the `SELECT` privilege to a user (represented by the `user_access_role`) on the `unity_catalog_sales` table. The `SHOW GRANTS` statement confirms that the privilege has been granted successfully.

Key Concept: Unity Catalog’s granular permissions model allows you to control precisely which users or roles have access to which data assets. `GRANT SELECT` is a fundamental permission to begin with, but more complex scenarios require other permissions (e.g., `ALTER`, `DROP`).

-- Output:  (The output will show that the user_access_role has SELECT privilege on the unity_catalog_sales table)
-- Example output:
-- | grantee             | privilege | on object              |
-- |---------------------|-----------|-----------------------|
-- | user_access_role     | SELECT    | unity_catalog_sales    |

Script 3: Validating Access After Permissions Granted

This script executes the same `SELECT` statement as in Script 1, but now with the user having the `SELECT` privilege granted on the Unity Catalog table. This confirms that access is working correctly after the permissions have been applied.

-- Perform a SELECT statement to compare privileges (using the same user)
SELECT 
FROM legacy_sales
INTERSECT
SELECT 
FROM unity_catalog_sales;

Now, when the `INTERSECT` statement is executed, the user should be able to access both tables because they have been granted the necessary permissions on the Unity Catalog table. This validates the successful application of the permissions and resolves the initial privilege drift issue.

Key Concept: The INTERSECT statement serves as a validation tool after granting permissions. If the `INTERSECT` operation now returns data, it confirms the correct permission model is in place.

-- Output:
-- | sales_id | product_name | sales_amount |
-- |----------|--------------|--------------|
-- | 1        | Laptop       | 1200.00      |
-- | 2        | Mouse        | 25.00        |
-- | 3        | Keyboard     | 75.00        |