Databricks Unity Catalog Tutorial: Investigate Unexpected Privilege Inheritance after a Production Grant

This tutorial focuses on investigating unexpected privilege inheritance after granting a production role in Unity Catalog using Databricks SQL. We’ll simulate a scenario where a grant is unintentionally granting access to tables beyond the intended scope. This will help you understand the importance of granular permissions and the implications of production grants.

Script 1: Initial Setup – Creating Tables and Granting Initial Permission

This script sets up the initial database and tables, and then grants a production role to a user with the intention of granting access to a single table. We will immediately observe this grant causing issues as it provides further access.

CREATE DATABASE IF NOT EXISTS test_catalog.test_db;
USE test_catalog.test_db;

CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Insert some sample data
INSERT INTO users (user_id, username) VALUES
(1, 'john_doe'),
(2, 'jane_smith');

INSERT INTO products (product_id, product_name, price) VALUES
(101, 'Laptop', 1200.00),
(102, 'Mouse', 25.00),
(103, 'Keyboard', 75.00);

-- Grant the 'production' role to user_id = 1 on the 'products' table
GRANT PRODUCTION ON TABLE products TO USER 1;

This script creates two tables: `users` and `products`, along with some sample data. Then, it grants the `production` role on the `products` table to `user_id = 1`. This is our starting point, and we’ll observe unexpected access later.

The final `SELECT` statement is used to validate the permissions granted.

SELECT 
FROM catalog.db.users;


Empty Result


Script 2: Investigating Unexpected Privilege Inheritance

This script explores the unexpected consequence of the previous grant. We will run a query on the `users` table to investigate if `user_id = 1` is receiving data from the `products` table.

USE test_catalog.test_db;

SELECT
    u.username,
    p.product_name,
    p.price
FROM users u
JOIN products p
ON u.user_id = p.product_id;

This script joins the `users` and `products` tables based on the `user_id` and `product_id` columns. This query attempts to retrieve user names and corresponding product names and prices. Because of the previous production grant, the query should return data from the `products` table for `user_id = 1` (john_doe).


| username | product_name | price |
| ----------- | ------------ | -------- |
| john_doe | Laptop | 1200.00 |
| john_doe | Mouse | 25.00 |
| john_doe | Keyboard | 75.00 |


Script 3: Verifying the Issue and Resetting Permissions

This script confirms the unexpected privilege inheritance and demonstrates how to correct it. The goal is to verify the findings and then reset the permissions to the original intended scope.

USE test_catalog.test_db;

-- Verify the issue by running the same query as Script 2
SELECT
    u.username,
    p.product_name,
    p.price
FROM users u
JOIN products p
ON u.user_id = p.product_id;

-- Revoke the production role from user_id = 1 on the products table
REVOKE PRODUCTION ON TABLE products FROM USER 1;

-- Verify that the query now returns no data
SELECT
    u.username,
    p.product_name,
    p.price
FROM users u
JOIN products p
ON u.user_id = p.product_id;

This script executes the same query as Script 2 to demonstrate the unexpected privilege inheritance. It then revokes the `production` role from `user_id = 1` on the `products` table. Finally, it runs the query again to confirm that the results are now empty, indicating that the privilege inheritance has been resolved.


Empty Result