Unity Catalog Permissions: The ACL Model Every Data Engineer Needs to Understand

Unity Catalog's permission model is more expressive than the Hive metastore's — and more complicated. If you're coming from a Hive background where permissions were a simple GRANT SELECT ON TABLE, the UC model requires a mental shift. Here's the complete picture.

The Privilege Hierarchy

Unity Catalog permissions are hierarchical. Privileges granted at a higher level flow down to objects below it, but not automatically — a user needs USAGE on every level of the hierarchy to reach an object. This is the gotcha that catches most people.

-- A user needs ALL THREE of these to query a table:
GRANT USAGE ON CATALOG prod_analytics TO `[email protected]`;
GRANT USAGE ON SCHEMA prod_analytics.sales TO `[email protected]`;
GRANT SELECT ON TABLE prod_analytics.sales.customer_orders TO `[email protected]`;

-- Missing USAGE on the catalog or schema? Table access fails.
-- This is different from Hive, where table-level grants were sufficient.

The Full Privilege Set

At the catalog level:

• USAGE — required to access any object within the catalog
• CREATE SCHEMA — create new schemas
• CREATE TABLE — create tables directly in the catalog (unusual)

At the schema level:

• USAGE — required to access any object within the schema
• CREATE TABLE — create tables in this schema
• CREATE VIEW — create views
• CREATE FUNCTION — create UDFs
• SELECT — read all tables in this schema (schema-wide grant)
• MODIFY — write to all tables in this schema (schema-wide grant)

At the table level:

• SELECT — read the table
• MODIFY — insert, update, delete, write to the table
• ALL PRIVILEGES — full control including drop

Group-Based Access Pattern

-- Create groups in the Account Console or sync from your IdP
-- Then grant to groups, not individual users

-- Data engineers: full write access to engineering schemas
GRANT USAGE ON CATALOG prod_analytics TO `data-engineers`;
GRANT USAGE ON SCHEMA prod_analytics.silver TO `data-engineers`;
GRANT USAGE ON SCHEMA prod_analytics.gold TO `data-engineers`;
GRANT CREATE TABLE, MODIFY, SELECT ON SCHEMA prod_analytics.silver TO `data-engineers`;
GRANT CREATE TABLE, MODIFY, SELECT ON SCHEMA prod_analytics.gold TO `data-engineers`;

-- Analysts: read-only access to gold layer
GRANT USAGE ON CATALOG prod_analytics TO `analysts`;
GRANT USAGE ON SCHEMA prod_analytics.gold TO `analysts`;
GRANT SELECT ON SCHEMA prod_analytics.gold TO `analysts`;

-- Automated pipeline service principal: read bronze, write silver and gold
GRANT USAGE ON CATALOG prod_analytics TO `pipeline-sp`;
GRANT USAGE ON SCHEMA prod_analytics.bronze TO `pipeline-sp`;
GRANT SELECT ON SCHEMA prod_analytics.bronze TO `pipeline-sp`;
GRANT USAGE ON SCHEMA prod_analytics.silver TO `pipeline-sp`;
GRANT MODIFY, SELECT ON SCHEMA prod_analytics.silver TO `pipeline-sp`;

Dynamic Views for Row-Level Security

-- A view that filters rows based on the current user's region
CREATE VIEW prod_analytics.gold.regional_orders_view AS
SELECT *
FROM prod_analytics.gold.all_orders
WHERE region_code = (
SELECT region_code
FROM prod_analytics.security.user_region_map
WHERE username = current_user()
);

-- Grant analysts access to the view, not the underlying table
GRANT SELECT ON VIEW prod_analytics.gold.regional_orders_view TO `analysts`;

The Information Schema

Unity Catalog's information schema lets you audit who has access to what. Important for compliance reviews and for verifying that your grant scripts produced the expected result:

-- See all grants on a specific table
SHOW GRANTS ON TABLE prod_analytics.gold.customer_summary;

-- See what a specific principal can access
SHOW GRANTS TO `[email protected]`;

The strictness of the UC permission model — the requirement to explicitly grant USAGE at every level — is a feature, not a bug. It means accidental permissive access is much harder. The cost is more grant statements. Build a standard grant script for each role pattern and apply it consistently when you create new schemas. As always, I'm here to help.