4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

DP-750 Exam Focus: Understand the capabilities and limitations of Object Owners in Unity Catalog. Master the exact ALTER <securable_type> <name> OWNER TO <principal> syntax, understand the severe operational risks of individual ownership vs. group ownership (the orphan object problem), and know how to delegate administrative duties across Metastore Admins, Catalog Admins, and Schema Owners using the principle of least privilege.

---

The Unity Catalog Ownership Model

In Unity Catalog, every securable object has exactly one Owner. The owner is designated at the moment the object is created and has ultimate authority over that object.

                                +-------------------------------------------------+
                                |                  OBJECT OWNER                   |
                                |      (Account Group, User, or Service Principal) | 
                                +-------------------------------------------------+
                                                         |
                   +-------------------------------------+-------------------------------------+
                   |                                     |                                     |
                   v                                     v                                     v
  +---------------------------------+  +---------------------------------+  +---------------------------------+
  |     Implicit Full Control       |  |      Administrative Powers      |  |      Delegation & Transfer      |
  | - SELECT, MODIFY, READ/WRITE    |  | - ALTER TABLE / SCHEMA / CAT    |  | - GRANT / REVOKE to others      |
  | - Cannot be locked out          |  | - DROP TABLE / SCHEMA / CAT     |  | - ALTER ... OWNER TO <new_owner>|
  +---------------------------------+  +---------------------------------+  +---------------------------------+

Capabilities of an Object Owner

1. Implicit Full Access: The owner automatically possesses all operational privileges (SELECT, MODIFY, READ VOLUME, WRITE VOLUME, EXECUTE) on the object without needing explicit GRANT statements.
2. Schema and DDL Alteration: The owner can execute ALTER commands to modify table schemas, rename objects, add check constraints, attach row filters/column masks, or change storage properties.
3. Object Deletion: The owner can drop the securable object (DROP TABLE, DROP VIEW, DROP VOLUME, DROP SCHEMA, DROP CATALOG).
4. Access Control Management: The owner can grant and revoke privileges on the object to other users, service principals, and groups.
5. Ownership Transfer: The owner can transfer ownership of the object to another principal using ALTER ... OWNER TO.

Important Owner Constraint: Parent Traversal

Even though an owner has full control over an object (such as a table), if they do not own the parent schema or catalog, they still require USE CATALOG on the parent catalog and USE SCHEMA on the parent schema to interact with the object in SQL sessions.

---

The Orphan Object Problem & Group Ownership Best Practice

When a data engineer creates a table, schema, or volume using their individual user account, Unity Catalog assigns initial ownership to that specific user (e.g., Owner: john.doe@company.com).

  THE ORPHAN OBJECT FAILURE SCENARIO
  
  1. Engineer John Doe creates table: `prod_dw.finance.general_ledger`
     --> Owner is automatically set to `john.doe@company.com`
  
  2. John Doe resigns from the company.
     --> Entra ID deactivates John's account.
  
  3. Pipeline schema changes require adding a new column: 
     ALTER TABLE prod_dw.finance.general_ledger ADD COLUMN tax_code STRING;
     --> FAILS: Remaining team members cannot ALTER the table because they are not the Owner!
     --> FAILS: Team members cannot grant permissions to new analysts!
  
  4. Emergency resolution requires a Metastore Admin to manually intervene and reassign ownership.

Enterprise Solution: Group Ownership

To prevent orphan objects and pipeline gridlock, ownership of all production catalogs, schemas, tables, and volumes should always be assigned to an Account Group (e.g., data_engineers_finance, analytics_leads, governance_admins) or a dedicated Service Principal.

  ENTERPRISE BEST PRACTICE: GROUP-BASED OWNERSHIP
  
  Owner: `data_engineers_finance` (Account Group)
    ├── Alice (Team Member)   --> Can ALTER, DROP, and GRANT on the table
    ├── Bob (Team Member)     --> Can ALTER, DROP, and GRANT on the table
    └── Charlie (New Hire)    --> Instantly inherits full administrative power upon joining group
  
  * When Alice leaves, no objects are orphaned and no administrative intervention is required.

---

Ownership Transfer Syntax: ALTER ... OWNER TO

Ownership of any securable object in Unity Catalog can be transferred using standard ANSI SQL DDL statements.

SQL Syntax Across Securable Types

-- Transfer Table Ownership
ALTER TABLE finance_dw.gold.gl_summary OWNER TO `finance_data_leads`;

-- Transfer View Ownership
ALTER VIEW finance_dw.gold.vw_monthly_pnl OWNER TO `finance_data_leads`;

-- Transfer Volume Ownership
ALTER VOLUME finance_dw.raw.invoices_drop OWNER TO `finance_ingestion_team`;

-- Transfer Function Ownership
ALTER FUNCTION finance_dw.gold.calculate_tax OWNER TO `finance_data_leads`;

-- Transfer Schema Ownership (Does NOT automatically transfer child table ownership!)
ALTER SCHEMA finance_dw.gold OWNER TO `finance_data_leads`;

-- Transfer Catalog Ownership
ALTER CATALOG finance_dw OWNER TO `finance_platform_admins`;

-- Transfer Storage Infrastructure Ownership
ALTER STORAGE CREDENTIAL azure_prod_cred OWNER TO `cloud_infrastructure_admins`;
ALTER EXTERNAL LOCATION adls_finance_loc OWNER TO `cloud_infrastructure_admins`;

Authorization Rules for Transferring Ownership

Who can execute ALTER ... OWNER TO?

1. The Current Owner of the securable object.
2. A Metastore Admin (who holds root authority over all securables in the metastore).

Exam Trap: A user who possesses ALL PRIVILEGES on an object cannot transfer its ownership. Only the current Owner or a Metastore Admin has the authority to execute ALTER ... OWNER TO.

---

Multi-Tier Administrative Delegation Hierarchy

Rather than granting broad Metastore Admin privileges to many users, enterprises implement a tiered administrative delegation model based on the principle of least privilege.

+-----------------------------------------------------------------------------------------+
|                                    METASTORE ADMIN                                      |
|                 (Small platform governance team: e.g., `metastore-admins`)              |
|  - Global metastore governance, Storage Credentials, External Locations, Connections   |
|  - Grants CREATE CATALOG to domain groups; emergency ownership reassignment             |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | Delegates `CREATE CATALOG` or assigns Catalog Owner
                                             v
+-----------------------------------------------------------------------------------------+
|                                     CATALOG ADMIN                                       |
|              (Domain Lead Group: e.g., `finance-data-leads`, `marketing-leads`)         |
|  - Owns domain catalog (e.g., `finance_dw`)                                             |
|  - Creates Schemas (`bronze`, `silver`, `gold`); assigns Schema Owners                  |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | Delegates `CREATE SCHEMA` or assigns Schema Owner
                                             v
+-----------------------------------------------------------------------------------------+
|                                     SCHEMA OWNER                                        |
|             (Project Team Group: e.g., `finance-engineers`, `analytics-team`)           |
|  - Owns schema (e.g., `finance_dw.gold`)                                                |
|  - Creates Tables, Views, Volumes, Functions; grants SELECT/MODIFY to Consumers         |
+-----------------------------------------------------------------------------------------+
                                             |
                                             | Grants specific operational verbs (`SELECT`, `MODIFY`)
                                             v
+-----------------------------------------------------------------------------------------+
|                                 DATA CONSUMERS / USERS                                  |
|             (End users, BI Analysts, Pipelines: e.g., `bi-analysts`, `etl-runners`)     |
|  - Read data (`SELECT`), run reports, write data via pipelines (`MODIFY`)               |
+-----------------------------------------------------------------------------------------+

Administrative Roles Breakdown

---

Delegating Catalog and Storage Administration

A common requirement on the DP-750 exam is delegating administrative capabilities without elevating users to Metastore Admins.

1. Delegating Catalog Creation

To allow a business unit to create and manage their own catalogs without granting full metastore administration:

-- Executed by Metastore Admin:
GRANT CREATE CATALOG ON METASTORE TO `marketing_governance_group`;

-- Now a marketing lead in `marketing_governance_group` can run:
CREATE CATALOG marketing_lakehouse;
-- The marketing lead (or their group) automatically becomes the OWNER of marketing_lakehouse!

2. Delegating External Location Creation

To allow cloud engineers to manage storage locations without granting access to the underlying Azure Access Connector credentials:

-- Executed by Metastore Admin:
GRANT CREATE EXTERNAL LOCATION ON STORAGE CREDENTIAL azure_prod_cred TO `cloud_storage_engineers`;

-- A cloud engineer can now create external locations referencing that credential:
CREATE EXTERNAL LOCATION telemetry_landing
  URL 'abfss://telemetry@adlsprod.dfs.core.windows.net/'
  WITH (STORAGE CREDENTIAL azure_prod_cred);

3. Delegating Schema Creation

To allow data engineers to create schemas inside a production catalog:

-- Executed by Catalog Owner or Metastore Admin:
GRANT CREATE SCHEMA, USE CATALOG ON CATALOG supply_chain_dw TO `supply_chain_developers`;