Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Unity Catalog and Governance

Grants, Privileges and Groups

Control access in Unity Catalog: GRANT and REVOKE, the USE CATALOG and USE SCHEMA gate, SELECT and MODIFY, ownership, groups, service principals and inheritance.

Unity Catalog follows one rule: access is denied unless it is granted. Permissions are stated with SQL GRANT and REVOKE on securable objects (catalogs, schemas, tables, volumes, functions, models, and more) to principals (users, groups, service principals).

Databricks cannot run on this site. The SQL is read-only, and the runnable stand-ins implement the permission-check logic in Python so you can test your understanding of inheritance.

Principals

  • Users: people, synced from your identity provider.
  • Groups: sets of users (and other groups). Grant to groups, not to individuals.
  • Service principals: identities for automation (jobs, CI). Jobs should run as a service principal, not as a person who might leave.
  • account users is a built-in group containing everyone.

Granting privileges

SQL
GRANT USE CATALOG ON CATALOG prod TO `analysts`;
GRANT USE SCHEMA  ON SCHEMA  prod.sales TO `analysts`;
GRANT SELECT      ON TABLE   prod.sales.orders TO `analysts`;

GRANT MODIFY ON TABLE prod.sales.orders TO `etl-service`;
GRANT ALL PRIVILEGES ON SCHEMA prod.bronze TO `data-engineers`;

REVOKE SELECT ON TABLE prod.sales.orders FROM `analysts`;
SHOW GRANTS ON TABLE prod.sales.orders;

Important privileges:

Privilege Allows
USE CATALOG / USE SCHEMA Seeing and navigating into the container (a gate)
SELECT Reading a table, view or volume files metadata
MODIFY Insert, update, delete, merge into a table
CREATE TABLE, CREATE SCHEMA, CREATE VOLUME, ... Creating objects inside a container
EXECUTE Calling a function or using a model
READ VOLUME / WRITE VOLUME Reading or writing files in a volume
MANAGE Managing grants and ownership on an object
ALL PRIVILEGES Every privilege on that object type

To query prod.sales.orders a user needs all three: USE CATALOG on prod, USE SCHEMA on prod.sales, and SELECT on the table. Missing the first two is the most common "table not found" surprise.

Inheritance

Privileges granted on a container apply to everything inside it, now and in the future:

SQL
GRANT SELECT ON SCHEMA prod.sales TO `analysts`;   -- every table in prod.sales
GRANT SELECT ON CATALOG prod TO `auditors`;        -- every table in every schema of prod

Granting at schema level is usually the right amount of control: new tables are covered automatically, and you do not maintain hundreds of table grants. There is no "deny" at a lower level; if a group must not see one table, keep it in a different schema and grant at schema level.

Ownership

Every securable object has an owner: the creator, unless changed. Owners can grant privileges on their objects and can drop them. Best practice: make owners groups or service principals.

SQL
ALTER TABLE prod.sales.orders OWNER TO `data-engineers`;

A permission checker

The stand-in below models the rules: gates on catalog and schema, a privilege on the table, inheritance from containers, and default deny.

Python Stand-in: can this principal read the table?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Reading effective access

SHOW GRANTS ON TABLE ... lists grants on that object; use SHOW GRANTS ... with a principal to list what someone has. Catalog Explorer's Permissions tab shows inherited grants too. In a real audit you also look at the system tables (for example system.access.audit), which record who did what.

Python Stand-in: an audit log of grant changes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Good practice

  1. Groups, not people. Add and remove members in your identity provider.
  2. Least privilege. Start with USE gates plus SELECT on gold schemas; give MODIFY only to pipelines.
  3. Service principals for jobs, with their own grants.
  4. Grant at schema level unless a table is truly special.
  5. Separate environments: analysts read prod, only CI deploys to it.
  6. Review quarterly: look for old grants to account users.
  7. For column and row restrictions see Lineage, Row Filters and Column Masks.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which grants does a user need to SELECT from prod.sales.orders?

    The container gates plus the privilege on the table are all required.
  2. 2. You GRANT SELECT ON SCHEMA prod.sales. Which tables does it cover?

    Privileges inherit to current and future child objects.
  3. 3. Who should own production objects and run jobs?

    People leave; groups and service principals are stable.
  4. 4. Can you deny one table inside a schema you granted SELECT on?

    Unity Catalog has no DENY; you shape access with layout, views, filters and masks.

Summary

  • Default is deny; grant privileges to groups and service principals.
  • Reading a table needs USE CATALOG, USE SCHEMA and SELECT.
  • Privileges inherit downwards; grant at schema level where possible.
  • Owners are groups or service principals; audit changes through system tables.

Next: Volumes and External Locations