4.1 Hierarchical Privilege Model & Securable Objects

4.1 Hierarchical Privilege Model & Securable Objects

DP-750 Exam Focus: Master the Unity Catalog hierarchical securable object model and the mechanics of downward privilege inheritance. You must understand the prerequisite traversal privileges (USE CATALOG on the catalog and USE SCHEMA on the schema) required before a user can query tables or read volumes, the exact ANSI SQL GRANT and REVOKE syntax, and the operational differences between ALL PRIVILEGES, specific verb privileges, and object ownership.

---

The Unity Catalog Securable Object Hierarchy

In Azure Databricks Unity Catalog, a securable object is any entity on which permissions can be granted to a principal (user, service principal, or account group). Unity Catalog structures all securable objects in a strict, containment-based tree hierarchy. Access control applied at higher tiers of the tree influences accessibility at lower tiers.

                               +---------------------------------------------------+
                               |                     METASTORE                     |
                               |     (Top-level account container for metadata)    |
                               +---------------------------------------------------+
                                                         |
                   +-------------------------------------+-------------------------------------+
                   |                                     |                                     |
                   v                                     v                                     v
  +---------------------------------+  +-----------------------------------+  +---------------------------------+
  |       STORAGE CREDENTIALS       |  |             CATALOGS              |  |       EXTERNAL LOCATIONS        |
  | (Encapsulates Azure Managed Id) |  |   (First level of 3-level name)   |  |   (Gov. ADLS Gen2 URI paths)    |
  +---------------------------------+  +-----------------------------------+  +---------------------------------+
                   |                                     |                                     |
                   | (Used to create)                    v                                     | (Used to register)
                   +----------------------------->    SCHEMAS   <------------------------------+
                                           (Second level / database)
                                                         |
         +---------------------+-------------------------+-------------------------+---------------------+
         |                     |                         |                         |                     |
         v                     v                         v                         v                     v
+-----------------+   +-----------------+       +-----------------+       +-----------------+   +-----------------+
|     TABLES      |   |      VIEWS      |       |     VOLUMES     |       |    FUNCTIONS    |   |     MODELS      |
| (Managed/Ext.)  |   | (Standard/Mask) |       | (Managed/Ext.)  |       |   (UDFs / TVFs) |   | (MLflow Models) |
+-----------------+   +-----------------+       +-----------------+       +-----------------+   +-----------------+

Primary Securable Object Types

1. Metastore: The root metadata container deployed per Azure region. Governs global objects such as Catalogs, Storage Credentials, External Locations, Connections, Delta Sharing Providers, and Recipients.
2. Catalog: The primary organizational boundary (the first identifier in catalog.schema.table). Represents environments (e.g., prod_dw, dev_sandbox) or business domains (finance, marketing).
3. Schema (Database): The logical grouping within a catalog (the second identifier in catalog.schema.table). Represents business processes or medallion layers (bronze, silver, gold).
4. Data Securables (Leaf / Execution level):
   • Tables: Tabular datasets (Managed Delta tables or External tables).
   • Views: Virtual tables defined on top of one or more tables or views.
   • Volumes: Logical governance endpoints for unstructured or semi-structured files in ADLS Gen2.
   • Functions: User-defined scalar and table-valued functions (UDFs/TVFs) registered in the catalog.
   • Models: Registered machine learning models tracked and deployed via Unity Catalog MLflow.
5. Infrastructure Securables:
   • Storage Credentials: Authentication mechanisms wrapping Azure Access Connectors / Managed Identities.
   • External Locations: Governed bindings between Storage Credentials and specific ADLS Gen2 paths (abfss://...).
   • Connections: Governed connection endpoints to external database engines (Lakehouse Federation).

---

Privilege Inheritance Mechanics

Unity Catalog implements downward privilege inheritance. When an operational privilege is granted on a parent securable object, that privilege is automatically and implicitly inherited by all existing and future child objects contained within that parent container.

                                +------------------------------------------+
                                |         GRANT SELECT ON CATALOG          |  <-- Assigned at Catalog level
                                |               prod_catalog               |      to group `analysts`
                                +------------------------------------------+
                                                     |
                                                     | (Inherited automatically)
                                                     v
                                +------------------------------------------+
                                |               All Schemas                |  <-- `analysts` can query tables
                                |    (e.g., prod_catalog.silver, etc.)     |      in all schemas
                                +------------------------------------------+
                                                     |
                                                     | (Inherited automatically)
                                                     v
                                +------------------------------------------+
                                |       All Current & Future Tables        |  <-- Newly created tables in these
                                | (e.g., prod_catalog.silver.orders_fact)  |      schemas are instantly queryable
                                +------------------------------------------+

How Inheritance Operates in Practice

• Catalog-Level Grants: Executing GRANT SELECT ON CATALOG prod_dw TO data_analysts; gives data_analysts read access to every table and view across every schema in prod_dw. When a data engineer adds a new schema or creates a new table in prod_dw weeks later, data_analysts immediately inherits SELECT privileges on that new asset without administrative intervention.
• Schema-Level Grants: Executing GRANT SELECT ON SCHEMA prod_dw.gold TO marketing_team; restricts inherited read access strictly to tables and views within the gold schema of prod_dw. Assets in prod_dw.silver or prod_dw.bronze remain inaccessible.
• Object-Level Grants: Executing GRANT SELECT ON TABLE prod_dw.gold.customer_360 TO external_auditors; grants access strictly to that single table.

Exam Tip: Privilege inheritance in Unity Catalog flows only downward, never upward. Granting SELECT on a table does not grant privileges on its parent schema or catalog. Furthermore, Unity Catalog uses a default-deny model; if no grant exists at the object, schema, catalog, or metastore level, access is denied.

---

The Traversal Prerequisite Principle: USE CATALOG and USE SCHEMA

A critical concept tested heavily on the DP-750 exam is the requirement for traversal privileges. In Unity Catalog, granting an action privilege (such as SELECT on a table or READ VOLUME on a volume) is insufficient by itself for a user to interact with the object. The user must also possess traversal permissions along the full parent hierarchy.

The Mandatory Traversal Chain for Tables and Views

To execute SELECT * FROM prod_dw.silver.orders;, the querying principal must hold:

1. USE CATALOG on prod_dw (the parent catalog).
2. USE SCHEMA on prod_dw.silver (the parent schema).
3. SELECT on prod_dw.silver.orders (or inherit SELECT from silver, prod_dw, or the metastore).

  Required Privilege Check Flow for: SELECT * FROM prod_dw.silver.orders
  
  +------------------------+
  | 1. Check Catalog Level | ---> Does principal have USE CATALOG on `prod_dw`?
  +------------------------+      [NO]  --> FAIL: Permission Denied (Cannot traverse catalog)
              | [YES]
              v
  +------------------------+
  | 2. Check Schema Level  | ---> Does principal have USE SCHEMA on `prod_dw.silver`?
  +------------------------+      [NO]  --> FAIL: Permission Denied (Cannot traverse schema)
              | [YES]
              v
  +------------------------+
  | 3. Check Object Level  | ---> Does principal have SELECT on `prod_dw.silver.orders`?
  +------------------------+      (Or inherited SELECT from schema/catalog?)
              | [YES]             [NO]  --> FAIL: Permission Denied (Cannot read table)
              v
       [SUCCESS: Query Executes]

What Traversal Privileges Enable

-- Correct Traversal Provisioning for an Analyst Group
GRANT USE CATALOG ON CATALOG prod_dw TO `bi_analysts`;
GRANT USE SCHEMA ON SCHEMA prod_dw.gold TO `bi_analysts`;
GRANT SELECT ON TABLE prod_dw.gold.daily_revenue TO `bi_analysts`;

-- Alternative using Inheritance at the Schema level:
GRANT USE CATALOG ON CATALOG prod_dw TO `bi_analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA prod_dw.gold TO `bi_analysts`;

---

Complete Unity Catalog Privilege Matrix

Unity Catalog supports specific ANSI SQL privilege verbs tailored to each securable object type:

---

ANSI SQL Syntax: GRANT, REVOKE, and SHOW GRANTS

Unity Catalog uses standard ANSI SQL statements to manage privileges. All grant statements can be executed in Databricks Notebooks, the SQL Editor, or programmatically via the Databricks REST API and Terraform.

1. Granting Privileges

-- Syntax: GRANT <privileges> ON <securable_type> <securable_name> TO <principal>;

-- Granting traversal and read access on a catalog and schema
GRANT USE CATALOG ON CATALOG finance_prod TO `finance_analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA finance_prod.reporting TO `finance_analysts`;

-- Granting write privileges on a table
GRANT MODIFY ON TABLE finance_prod.reporting.monthly_gl TO `finance_data_engineers`;

-- Granting file access privileges on a Volume
GRANT READ VOLUME, WRITE VOLUME ON VOLUME finance_prod.raw.invoices_landing TO `ingestion_pipeline_sp`;

-- Granting external storage creation on an External Location
GRANT CREATE EXTERNAL TABLE, READ FILES ON EXTERNAL LOCATION adls_landing_loc TO `data_engineers`;

2. Revoking Privileges

-- Syntax: REVOKE <privileges> ON <securable_type> <securable_name> FROM <principal>;

-- Revoke write access from a group
REVOKE MODIFY ON TABLE finance_prod.reporting.monthly_gl FROM `finance_data_engineers`;

-- Revoke schema creation from a user
REVOKE CREATE SCHEMA ON CATALOG finance_prod FROM `contractor@company.com`;

3. Inspecting Active Grants (SHOW GRANTS)

-- Show all privileges granted on a specific table
SHOW GRANTS ON TABLE finance_prod.reporting.monthly_gl;

-- Show all privileges granted to a specific principal across securables
SHOW GRANTS TO `finance_analysts`;

-- Show grants on an external location
SHOW GRANTS ON EXTERNAL LOCATION adls_landing_loc;

---

ALL PRIVILEGES vs. Object Ownership

A critical distinction on the DP-750 exam is the difference between having ALL PRIVILEGES on an object versus being the Owner of the object.

Key Concept: ALL PRIVILEGES is an operational shortcut. It grants all current and future operational verbs (e.g., SELECT, MODIFY, CREATE TABLE) applicable to that object type. However, ALL PRIVILEGES does NOT confer administrative control. You cannot alter object definitions, drop objects, or delegate permissions to others unless you are the explicit Owner of the object or a Metastore Admin.