body {
font-family: sans-serif;
}
h1 {
color: #333;
}
h2 {
color: #666;
}
p {
line-height: 1.6;
}
ul {
list-style: disc;
}
pre {
background-color: #f4f4f4;
padding: 10px;
border: 1px solid #ddd;
overflow-x: auto;
}
code {
font-family: monospace;
}

Databricks Unity Catalog – Repair Ownership After Retired Service Principal

This tutorial focuses on repairing ownership of a Unity Catalog table after a production service principal has been retired. It demonstrates the necessary steps using Databricks SQL, prioritizing clarity and hands-on learning.

Scenario

A production service principal, prod-sp@example.com, previously had exclusive access to a table named production_sales within the production_db.sales schema in Unity Catalog. This principal has now been retired. We need to restore ownership of the table to a new principal, repair-team@example.com, to ensure continued access and operation.

Script 1: Initial Setup and Verification

This script creates a sample table and grants initial ownership to the retired service principal. We then verify the ownership to confirm the setup.

CREATE TABLE IF NOT EXISTS production_db.sales.production_sales (
    sale_id INT,
    product_name STRING,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
)
;

SET CABINET_SYSTEM = 'ROW_LEVEL_SECURITY';

GRANT USAGE ON TABLE production_db.sales.production_sales TO 'prod-sp@example.com';
GRANT SELECT ON TABLE production_db.sales.production_sales TO 'prod-sp@example.com';

SELECT  FROM production_db.sales.production_sales LIMIT 10;

Explanation:

• `CREATE TABLE IF NOT EXISTS …`: Creates the `production_sales` table within the `production_db.sales` schema.
• `SET CABINET_SYSTEM = ‘ROW_LEVEL_SECURITY’`: This enables row-level security, a key component of Unity Catalog’s governance.
• `GRANT USAGE ON TABLE … TO ‘prod-sp@example.com’`: Grants the `prod-sp@example.com` principal the ability to access the table.
• `GRANT SELECT ON TABLE … TO ‘prod-sp@example.com’`: Grants the `prod-sp@example.com` principal read access to the table.
• `SELECT FROM … LIMIT 10`: Retrieves a sample of data from the table to verify its existence and initial data.

This script establishes the initial ownership and demonstrates how to verify that the specified service principal has access. The output will be sample data, confirming the table was created and accessible by the service principal.

Script 2: Retirement and Ownership Repair

This script simulates the retirement of the service principal and then executes the ownership repair. We also verify the change.

-- Simulate Retirement (This action is not directly replicable within SQL)
-- In a real scenario, this would involve revoking the principal's access and potentially deleting its associated credentials.

-- Now, repair the ownership
REVOKE USAGE ON TABLE production_db.sales.production_sales FROM 'prod-sp@example.com';
REVOKE SELECT ON TABLE production_db.sales.production_sales FROM 'prod-sp@example.com';

GRANT USAGE ON TABLE production_db.sales.production_sales TO 'repair-team@example.com';
GRANT SELECT ON TABLE production_db.sales.production_sales TO 'repair-team@example.com';

SELECT  FROM production_db.sales.production_sales LIMIT 10;

Explanation:

• `REVOKE USAGE ON TABLE … FROM ‘prod-sp@example.com’`: Revokes the `prod-sp@example.com` principal’s access to the table.
• `REVOKE SELECT ON TABLE … FROM ‘prod-sp@example.com’`: Revokes the `prod-sp@example.com` principal’s read access to the table.
• `GRANT USAGE ON TABLE … TO ‘repair-team@example.com’`: Grants the `repair-team@example.com` principal the ability to access the table.
• `GRANT SELECT ON TABLE … TO ‘repair-team@example.com’`: Grants the `repair-team@example.com` principal read access to the table.
• `SELECT FROM … LIMIT 10`: Retrieves a sample of data from the table to verify the change in ownership.

This script performs the core steps to repair ownership after a service principal is retired. The `REVOKE` statements remove the previous principal’s access, and the `GRANT` statements assign ownership to the new principal.

Script 3: Final Verification and Audit

This script provides a final verification step to confirm that the ownership has been successfully transferred. It also provides a deterministic output.

SELECT CURRENT_USER(); -- Verify the current user is repair-team@example.com
SELECT  FROM production_db.sales.production_sales LIMIT 10;

Explanation:

• `SELECT CURRENT_USER()`: This statement returns the current user’s identity. In this context, it should return `repair-team@example.com` after the ownership repair.
• `SELECT FROM production_db.sales.production_sales LIMIT 10`: Retrieves a sample of data from the table to verify that the principal has access.

This script confirms that the ownership has been successfully transferred to the `repair-team@example.com` principal and that the principal now has access to the table. The `CURRENT_USER()` function is key to verifying the active user.

Output

-- Output from Script 1: (Sample Data - will vary)

-- +-------------+----------------+-----------------+-------------------+

-- | sale_id     | product_name   | sale_date       | sale_amount       |

-- +-------------+----------------+-----------------+-------------------+

-- | 1           | Product A      | 2024-01-26       | 100.00            |

-- | 2           | Product B      | 2024-01-27       | 50.00             |

-- | 3           | Product A      | 2024-01-28       | 75.00             |

-- | 4           | Product C      | 2024-01-29       | 25.00             |

-- | 5           | Product B      | 2024-01-30       | 120.00            |

-- | 6           | Product A      | 2024-01-31       | 80.00             |

-- | 7           | Product C      | 2024-02-01       | 40.00             |

-- | 8           | Product B      | 2024-02-02       | 90.00             |

-- | 9           | Product A      | 2024-02-03       | 60.00             |

-- | 10          | Product C      | 2024-02-04       | 30.00             |

-- +-------------+----------------+-----------------+-------------------+

-- Output from Script 3: (Final Verification)

-- +------------------------+

-- | CURRENT_USER            |

-- +------------------------+

-- | repair-team@example.com |

-- +------------------------+
-- Sample Data from Script 3 (will vary)

-- +-------------+----------------+-----------------+-------------------+

-- | sale_id     | product_name   | sale_date       | sale_amount       |

-- +-------------+----------------+-----------------+-------------------+

-- | 1           | Product A      | 2024-01-26       | 100.00            |

-- | 2           | Product B      | 2024-01-27       | 50.00             |

-- | 3           | Product A      | 2024-01-28       | 75.00             |

-- | 4           | Product C      | 2024-01-29       | 25.00             |

-- | 5           | Product B      | 2024-01-30       | 120.00            |

-- | 6           | Product A      | 2024-01-31       | 80.00             |

-- | 7           | Product C      | 2024-02-01       | 40.00             |

-- | 8           | Product B      | 2024-02-02       | 90.00             |

-- | 9           | Product A      | 2024-02-03       | 60.00             |

-- | 10          | Product C      | 2024-02-04       | 30.00             |

-- +-------------+----------------+-----------------+-------------------+