Databricks DE · Topic 8.2

UC Access Control

GRANT/REVOKE privileges on securable objects to account groups — with the USE CATALOG → USE SCHEMA → SELECT chain and downward inheritance. This deep dive walks the model and the SQL.

GRANT / REVOKEUSE chaingrant to groupsMANAGE / ownership

The one-liner

GRANT <privilege> ON <securable> TO <group>. To read a table you need USE CATALOG + USE SCHEMA + SELECT. Privileges inherit downward (catalog → schema → table). Grant to groups, not users.

🪪 Analogy: an office with keycards — USE CATALOG badges into the building, USE SCHEMA onto the floor, SELECT into the room. You need all three — and you issue keycards to teams, not individuals.

The USE chain — toggle grants to open the door

▸ goal: read prod.sales.orders

USE CATALOG prodnot granted

USE SCHEMA prod.salesnot granted

SELECT ON prod.sales.ordersnot granted

🔒 permission denied

1 · The grant statement & the USE chain

GRANT <privilege> ON <securable> <name> TO <principal>. All three rungs are required to read a table. Grant to groups (synced via SCIM / identity federation).

GRANT USE CATALOG ON CATALOG prod              TO `analysts`;   -- badge: building
GRANT USE SCHEMA  ON SCHEMA  prod.sales        TO `analysts`;   -- badge: floor
GRANT SELECT      ON TABLE   prod.sales.orders TO `analysts`;   -- key: the room

2 · The privilege catalog

Category	Privileges
Read/write data	`SELECT`, `MODIFY`
Traverse	`USE CATALOG`, `USE SCHEMA`, `USE CONNECTION`
Create	`CREATE TABLE`, `CREATE SCHEMA`, `CREATE VOLUME`, `CREATE FUNCTION`, `CREATE MATERIALIZED VIEW`
Functions / files	`EXECUTE`, `READ VOLUME`, `WRITE VOLUME`
Discovery / governance	`BROWSE`, `APPLY TAG`
Admin	`MANAGE`, `ALL PRIVILEGES`

GRANT EXECUTE     ON FUNCTION prod.sales.mask_email TO `analysts`;  -- UC function
GRANT READ VOLUME ON VOLUME   prod.sales.landing    TO `loaders`;   -- files
GRANT MODIFY      ON TABLE    prod.sales.orders     TO `data_eng`;  -- write

3 · Inheritance — click a level

▸ a grant at a level can cover children (grant once, not table-by-table)

CATALOG prod

SCHEMA prod.sales

TABLE prod.sales.orders

-- everyone in analysts reads ALL current & future tables in the schema
GRANT SELECT ON SCHEMA prod.sales TO `analysts`;   -- (+ USE CATALOG/USE SCHEMA)

4 · Ownership, MANAGE & SHOW GRANTS

ALTER TABLE prod.sales.orders OWNER TO `data_eng`;   -- transfer ownership (to a group)
GRANT MANAGE ON SCHEMA prod.sales TO `sales_admins`; -- delegate grant authority

SHOW GRANTS ON TABLE prod.sales.orders;              -- audit who can do what
REVOKE SELECT ON TABLE prod.sales.orders FROM `analysts`;

Who can grant: the object owner, holders of MANAGE, or metastore/account admins. Some actions (DROP/ALTER) require ownership.

Grant scope at a glance

Grant level	Covers	Use when
Table/view	one object	fine-grained, exceptional access
Schema	all current + future objects	a team owns a subject area
Catalog	everything in the catalog	broad env/domain access

Why it matters

Day-to-day governance — most "permission denied" bugs are a missing USE CATALOG/USE SCHEMA, not the table grant.
"Give a team read access to a schema?" → GRANT to a group with the USE chain.

👥 Real-world use case: analytics gets USE CATALOG prod, USE SCHEMA prod.sales, SELECT ON SCHEMA prod.sales — granted to the analysts group, so onboarding/offboarding is just group membership.

Uses, edge cases & limitations

Uses: team read/write access; least-privilege by group; delegating admin via MANAGE/ownership; EXECUTE/READ VOLUME for functions/files.
Edge cases: missing USE CATALOG/USE SCHEMA is the #1 denial cause; granting to users instead of groups → sprawl; ownership required for some drop/alter.
Limitation: GRANT controls whole-object access — row/column control needs row filters & column masks (8.3); attribute-based needs ABAC (8.4).

Common gotchas

❌ Granting SELECT but forgetting USE CATALOG/USE SCHEMA → still denied.
❌ Granting to users instead of groups.
❌ Assuming admin = owner — some actions need ownership or MANAGE.
❌ Expecting GRANT to hide rows/columns — that's row filters / column masks (8.3).
❌ Forgetting EXECUTE/READ VOLUME — tables aren't the only securables.