12.1 Unity Catalog Privilege Model & Grants

Databricks Unity Catalog provides a unified, centralized governance solution for data and AI assets across the Databricks Data Intelligence Platform. It delivers consistent access control, auditing, data lineage, and asset discovery across multiple Databricks workspaces within an organization. For candidates preparing for the Databricks Certified Data Analyst Associate exam, understanding the Unity Catalog privilege model, object hierarchy, ANSI SQL authorization commands (GRANT and REVOKE), and privilege evaluation rules is essential.

Securable Objects & The 3-Level Namespace

In Unity Catalog, all assets managed by governance policies are designated as securable objects. These objects are arranged in a hierarchical structure using a 3-level namespace: catalog.schema.table_or_view (for example, production_catalog.finance_schema.quarterly_revenue).

---

Principals in Unity Catalog

A principal is an identity that can be granted access privileges to securable objects within Unity Catalog. Unity Catalog distinguishes between three types of account-level principals:

1. Account Users: Individual human identities provisioned at the Databricks account level. Users can log into workspaces and execute queries.
2. Account Groups: Collections of users and service principals managed centrally at the account level. Databricks strongly recommends assigning privileges to groups rather than individual user accounts to ensure scalable administration and easy onboarding/offboarding.
3. Service Principals: Automated non-human identities used for production jobs, CI/CD pipelines, and automated reporting scripts.
4. Metastore Admin: A specialized administrative principal designated at the metastore level with unrestricted capabilities to manage catalog creation, security policies, and ownership assignments.

---

Core Unity Catalog Privileges

Access to securable objects is enforced through granular SQL privileges. A principal cannot interact with an object unless explicit or inherited privileges have been granted.

• USE CATALOG: Grants traversal access into a specified catalog. Without USE CATALOG on a catalog, a principal cannot access any child schema or table inside it, regardless of table-level grants.
• USE SCHEMA: Grants traversal access into a specified schema. Required alongside USE CATALOG to read or write any child objects within that schema.
• SELECT: Grants read access to data stored within tables, views, materialized views, or streaming tables.
• MODIFY: Grants write access to execute Data Manipulation Language (DML) operations—including INSERT, UPDATE, DELETE, MERGE INTO, and TRUNCATE—on Delta Lake tables.
• CREATE TABLE / CREATE VIEW / CREATE VOLUME / CREATE FUNCTION: Grants permissions to create new securable objects inside a target schema.
• CREATE CATALOG / CREATE SCHEMA: Grants permissions to instantiate new top-level or second-level namespaces.
• EXECUTE: Grants authorization to invoke scalar or tabular User-Defined Functions (UDFs).
• READ VOLUME / WRITE VOLUME: Governs unstructured file access within Unity Catalog Volumes.
• ALL PRIVILEGES: Grants all valid privileges applicable to the target securable object to the specified principal (note: this does not confer object ownership).

---

SQL Grant & Revoke Syntax

Unity Catalog utilizes standard ANSI SQL syntax for managing privileges. The core syntax forms are:

-- Granting traversal and read access to an analytics group
GRANT USE CATALOG ON CATALOG production_catalog TO GROUP bi_analysts;
GRANT USE SCHEMA ON SCHEMA production_catalog.finance_schema TO GROUP bi_analysts;
GRANT SELECT ON TABLE production_catalog.finance_schema.quarterly_revenue TO GROUP bi_analysts;

-- Granting full data modification capabilities to a service principal
GRANT MODIFY ON TABLE production_catalog.finance_schema.quarterly_revenue TO `sp-etl-pipeline@company.com`;

-- Revoking write permissions from a contractor group
REVOKE MODIFY ON TABLE production_catalog.finance_schema.quarterly_revenue FROM GROUP temporary_contractors;

---

Privilege Traversal: The Two-Gatekeeper Rule

A critical exam concept in Unity Catalog is the privilege traversal rule (often called the two-gatekeeper rule). To successfully read data from a table such as prod.sales.orders, Unity Catalog requires the querying principal to satisfy three simultaneous conditions:

1. Hold USE CATALOG on the parent catalog (prod).
2. Hold USE SCHEMA on the parent schema (sales).
3. Hold SELECT on the target table (orders) or its parent container.

If an analyst possesses SELECT on prod.sales.orders but lacks USE CATALOG on prod or USE SCHEMA on sales, any query against orders will fail immediately with a Permission Denied error indicating missing parent traversal privileges.

Access Request -> [USE CATALOG Check] -> [USE SCHEMA Check] -> [SELECT Check] -> Data Returned

---

Real-World Exam Scenarios & Troubleshooting

Scenario 1: Resolving Unexpected "Permission Denied" Errors

Problem: A data analyst executes SELECT * FROM marketing_catalog.campaigns.conversions; and receives an authorization failure, despite the data lead confirming that GRANT SELECT ON TABLE marketing_catalog.campaigns.conversions TO GROUP analysts; was executed.
Diagnosis: The analyst's group lacks traversal privileges on the parent hierarchy.
Resolution: Execute GRANT USE CATALOG ON CATALOG marketing_catalog TO GROUP analysts; and GRANT USE SCHEMA ON SCHEMA marketing_catalog.campaigns TO GROUP analysts;.

Scenario 2: Distinguishing DML vs DDL Privileges

Problem: An engineer needs to add a new column to a Delta table via ALTER TABLE sales ADD COLUMN region STRING;. Is MODIFY sufficient?
Diagnosis: No. MODIFY governs DML data changes (INSERT/UPDATE/DELETE). Altering table schema (DDL) requires table ownership or ALTER TABLE privileges.

---

Summary Checklist for Exam Readiness

• Remember that Unity Catalog objects use catalog.schema.object naming.
• Understand that account-level groups are the recommended principal target for privileges.
• Always verify USE CATALOG and USE SCHEMA traversal permissions when debugging read/write failures.
• Memorize the distinction between SELECT (read) and MODIFY (write/DML).