Databricks Unity Catalog Tutorial: Investigate a Grant Failure due to Missing Parent-Object Privileges

This tutorial focuses on diagnosing a grant failure within Databricks Unity Catalog, specifically related to insufficient parent-object privileges. We’ll walk through the process using Databricks SQL, emphasizing Unity Catalog’s security and governance features.

Scenario: Grant Failure Investigation

A grant was issued to a user, ‘data_analyst’, to access a table named ‘grant_requests’ located within the ‘finance’ scope of Unity Catalog. The grant failed with an access denied error. Our goal is to determine why the grant was denied by tracing back through the parent-object privilege hierarchy.

Script 1: Setting Up Initial Data and the Grant

First, we need to create a sample ‘grant_requests’ table in the ‘finance’ scope, and then issue the grant. This will act as our test case.

CREATE TABLE IF NOT EXISTS finance.grant_requests (
    request_id STRING,
    application_date DATE,
    amount DECIMAL(10, 2),
    status STRING
)
;

INSERT INTO finance.grant_requests (request_id, application_date, amount, status) VALUES
('GR-001', '2024-01-15', 10000.00, 'Approved'),
('GR-002', '2024-02-20', 5000.00, 'Pending'),
('GR-003', '2024-03-10', 7500.00, 'Rejected');

-- Grant SELECT privilege on grant_requests to data_analyst within the finance scope
GRANT SELECT ON TABLE finance.grant_requests TO USER data_analyst;

After executing this script, verify the table exists and the grant was issued. You can confirm the grant via the Databricks SQL interface, viewing the permissions assigned to the ‘data_analyst’ user on the ‘finance’ scope table.

Now, let’s attempt to execute a query that should succeed if the grant were correctly applied.

Script 2: Attempting the Query and Observing the Failure

This script attempts to select all data from the ‘grant_requests’ table. This will trigger the grant failure if the necessary parent object privileges are not present.

-- Attempt to select all data from grant_requests
SELECT 
FROM finance.grant_requests;

The expected outcome is an access denied error. This indicates that the ‘data_analyst’ user doesn’t have the required permissions to access the table, despite the grant on ‘grant_requests’.

Script 3: Identifying the Missing Privilege – Trace Back

To pinpoint the root cause, we’ll investigate the parent-object privileges. The ‘finance’ scope must grant access to its children (in this case, ‘grant_requests’).

-- Examine the permissions on the finance scope itself
SHOW GRANTS ON SCOPE finance;

The output of this command will show the permissions granted to the user or role associated with the ‘finance’ scope. If the ‘data_analyst’ user’s permissions are being inherited from the ‘finance’ scope, and the ‘finance’ scope itself doesn’t have SELECT privileges on ‘grant_requests’, then the grant on ‘grant_requests’ will fail.

Assume the output of `SHOW GRANTS ON SCOPE finance;` reveals that the ‘finance’ scope only has permissions on itself (e.g., SELECT on its own metadata). This is the likely source of the problem.

Now, let’s grant SELECT privilege on `grant_requests` to the `finance` scope itself:

-- Grant SELECT privilege on grant_requests to the finance scope
GRANT SELECT ON TABLE finance.grant_requests TO SCOPE finance;

Finally, re-run Script 2 to verify that the query now executes successfully.

Script 4: Verification of Successful Query Execution

This script confirms that the grant has been successfully applied by executing a select query.

-- Verify the query now executes successfully
SELECT 
FROM finance.grant_requests;

The output should show the data from the ‘grant_requests’ table, confirming that the ‘data_analyst’ user now has the required access.

This process demonstrates how Unity Catalog’s hierarchical privilege model works. The failure to grant privileges at the parent scope ultimately prevented the user from accessing the target object, even with a grant on the object itself.

{
  "result": [
    "GR-001",
    "2024-01-15",
    10000.00,
    "Approved"
  ],
  "row_count": 1
}