Databricks Unity Catalog Tutorial: Preventing Overly Broad Catalog Grants Resulting in Sensitive Data Exposure

This tutorial focuses on preventing overly broad catalog grants within Databricks Unity Catalog, specifically using Databricks SQL. We’ll explore how to limit access to sensitive data by strategically utilizing catalog and schema-level permissions, demonstrating best practices for data governance and security.

Script 1: Initial Setup and Basic Granting

This script establishes a simple scenario and demonstrates a basic overly broad catalog grant. We’ll then inspect the permissions to highlight the potential issue.

-- Create a catalog named 'finance'
CREATE CATALOG IF NOT EXISTS finance;

-- Create a schema within 'finance' named 'sales'
CREATE SCHEMA IF NOT EXISTS finance.sales;

-- Insert some sample data into the 'sales' schema.
INSERT INTO finance.sales.orders (order_id, customer_id, order_date, total_amount)
VALUES
  (1, 101, '2024-01-15', 100.00),
  (2, 102, '2024-01-20', 250.50),
  (3, 101, '2024-01-25', 75.25);

-- Grant ALL privileges on the 'finance' catalog to the 'reporting' database. This is the overly broad grant.
GRANT ALL ON CATALOG finance TO DATABASE reporting;

-- Verify the grant
SHOW GRANTS ON CATALOG finance;

This script first creates a catalog named ‘finance’ and a schema named ‘sales’ within it. Then, it inserts three rows of sample order data into the ‘sales’ schema. Crucially, it then grants all privileges on the entire ‘finance’ catalog to the ‘reporting’ database. Finally, it shows the grants to verify the overly broad permission has been applied.

The `SHOW GRANTS` command confirms that the ‘reporting’ database has been granted full access to the entire ‘finance’ catalog. This means any user or service associated with the ‘reporting’ database can access any table or view within the ‘finance’ catalog – regardless of the intended security boundaries.

This demonstrates the fundamental risk: a single, overly permissive grant can expose sensitive data if not carefully managed.

Deterministic SELECT:

SELECT COUNT() FROM finance.sales.orders;

[1]

Script 2: Restricting Access at the Schema Level

This script demonstrates how to mitigate the overly broad grant by restricting access at the schema level. We’ll grant permissions to the ‘reporting’ database only on the ‘sales’ schema within the ‘finance’ catalog.

-- Revoke the overly broad grant from the reporting database on the finance catalog.
REVOKE ALL ON CATALOG finance FROM DATABASE reporting;

-- Grant permissions on the 'sales' schema to the 'reporting' database.
GRANT ALL ON SCHEMA finance.sales TO DATABASE reporting;

-- Verify the grant
SHOW GRANTS ON SCHEMA finance.sales;

The script first revokes the overly broad grant on the ‘finance’ catalog. Then, it grants all privileges on the ‘sales’ schema within the ‘finance’ catalog to the ‘reporting’ database. Finally, it shows the grants to confirm the revised permissions.

The `SHOW GRANTS` command now displays that the ‘reporting’ database has only been granted access to the ‘sales’ schema within the ‘finance’ catalog. This significantly reduces the risk of accidental or malicious access to other data within the catalog.

Deterministic SELECT:

SELECT COUNT() FROM finance.sales.orders;

[1]

Script 3: Granular Granting and Verification

This script shows a more granular approach, granting specific privileges to the ‘reporting’ database within the ‘sales’ schema. This demonstrates the power of Unity Catalog’s permission model.

-- Revoke overly broad catalog grant
REVOKE ALL ON CATALOG finance FROM DATABASE reporting;

-- Grant SELECT privilege on the 'sales' schema to the 'reporting' database.
GRANT SELECT ON SCHEMA finance.sales TO DATABASE reporting;

-- Grant INSERT privilege on the 'sales' schema to the 'reporting' database.
GRANT INSERT ON SCHEMA finance.sales TO DATABASE reporting;

-- Verify the grant
SHOW GRANTS ON SCHEMA finance.sales;

-- Verify that the reporting database can only perform SELECT and INSERT operations on the 'sales' schema
SELECT  FROM finance.sales.orders WHERE order_id = 1;

INSERT INTO finance.sales.orders (order_id, customer_id, order_date, total_amount)
VALUES (4, 103, '2024-01-30', 120.00);

SELECT  FROM finance.sales.orders WHERE order_id = 4;

This script further refines the permissions. It grants only `SELECT` and `INSERT` privileges on the ‘sales’ schema to the ‘reporting’ database. The `SHOW GRANTS` command confirms the specific permissions granted. The subsequent `SELECT` and `INSERT` statements demonstrate that the ‘reporting’ database can only perform those operations within the ‘sales’ schema, reinforcing the restricted access.

Deterministic SELECT:

SELECT COUNT() FROM finance.sales.orders;

[1]