Databricks Unity Catalog Tutorial: Removing Stale Contractor Access without Breaking Group-Based Permissions

This tutorial demonstrates how to remove stale contractor access within a Databricks Unity Catalog environment without disrupting existing group-based permissions. We’ll focus solely on the SQL aspects, utilizing Databricks SQL and Unity Catalog features.

Scenario Introduction

Imagine a company that uses Unity Catalog to manage its data. Contractors are granted access to specific tables for limited-time projects. Over time, contractors complete their projects, but their access hasn’t been revoked. This presents a security risk as contractors might still have access to sensitive data. We will demonstrate how to remove this stale access safely, ensuring it doesn’t impact users with legitimate group-based permissions.

Script 1: Initial Assessment – Identifying Stale Access

The first step is to identify which contractors still have access. We’ll create a temporary table to track contractor access, focusing on the `contractor_id` and the tables they have access to. We will then use this to investigate and ensure they have no legitimate group access to the tables.

CREATE TABLE contractor_access (
    contractor_id STRING,
    table_catalog STRING,
    table_name STRING
);

INSERT INTO contractor_access (contractor_id, table_catalog, table_name)
VALUES
('contractor_1', 'default', 'employees'),
('contractor_2', 'default', 'sales_data'),
('contractor_3', 'default', 'finance_data');

SELECT  FROM contractor_access;

This script creates a temporary table named `contractor_access`. It stores the `contractor_id`, `table_catalog`, and `table_name` where a contractor has been granted access. The `INSERT` statements populate this table with sample data. The final `SELECT` statement displays the contents of the table, allowing us to see which contractors have been granted access to which tables.

Run this script in Databricks SQL and verify the data is populated as expected.

Script 2: Revoking Stale Access

Now, let’s revoke access for contractors who no longer have projects. We will use a `DELETE` statement to remove the corresponding entries from the `contractor_access` table. Since this is a temporary table, we will be making destructive changes that should be validated before production.

DELETE FROM contractor_access
WHERE contractor_id IN ('contractor_1', 'contractor_2');

SELECT  FROM contractor_access;

This script deletes rows from the `contractor_access` table where the `contractor_id` is either ‘contractor_1’ or ‘contractor_2’. The `IN` operator efficiently filters the rows to be deleted. The final `SELECT` statement shows the updated content of the table, confirming the removal of the contractors.

Run this script and verify that ‘contractor_1’ and ‘contractor_2’ are no longer listed in the `contractor_access` table.

Script 3: Validation – Ensuring Group Permissions Remain Intact

To ensure that removing contractor access didn’t break existing group-based permissions, we’ll verify access for users within the ‘data_analysts’ group. The data_analysts group will have access to the tables defined within the ‘default’ catalog.

-- Assuming a table named 'employees' exists in the 'default' catalog
-- and the 'data_analysts' group has been granted access.

SELECT  FROM default.employees;

This final script attempts to select data from the `employees` table within the `default` catalog. The purpose is to verify that access remains granted to the ‘data_analysts’ group, demonstrating that the contractor access removal didn’t inadvertently block legitimate users.

Run this script. A successful `SELECT` indicates that group-based permissions are unaffected by the contractor access removal process.

Output

-- Script 3 Output:
-- (This will return a result set of data from the 'employees' table)
-- Example:
-- | employee_id | first_name | last_name | ... |
-- |-------------|-----------|-----------|-----|
-- | 1           | John      | Doe       | ... |
-- | 2           | Jane      | Smith     | ... |
-- ...

Note: The exact output will depend on the data stored in the `employees` table. This is the expected result of running the final `SELECT` statement.