Databricks Governance Tutorial: Investigate Why Analysts Lost Access after a Production Deployment using Databricks SQL

This tutorial guides you through investigating why analysts lost access to a Databricks SQL endpoint following a production deployment. We’ll examine the key steps involved in tracking down the issue, focusing on user access and permissions. The scenario is a simplified production deployment of a reporting database where access was unexpectedly revoked.

Script 1: Initial Access Confirmation & User Audit

Our first step is to confirm the initial access setup and identify which users were granted access. We’ll focus on the analysts who reported losing access.

-- Create a temporary table for the analysts
CREATE TEMPORARY TABLE analysts (
    analyst_id INT
);

-- Insert the analyst IDs we're investigating
INSERT INTO analysts (analyst_id) VALUES (1), (2), (3);

-- Check the grants for the 'production_reporting' endpoint for the identified analysts.
SHOW GRANTS FOR USER 'some_analyst@example.com' ON ENDPOINT 'production_reporting';

-- Select the users granted access
SELECT
    user_id,
    user_name
FROM
    access_management.granted_access
WHERE
    endpoint_name = 'production_reporting'
    AND analyst_id IN (1, 2, 3);

This script sets up a temporary table to hold the analyst IDs, then shows the grants for the target endpoint. This demonstrates how to initially check user access. Note the SHOW GRANTS command. It’s essential to identify which users were originally granted access.

The second SELECT statement retrieves a list of user IDs and names from the `access_management.granted_access` table. This information is vital to verify who had access to the endpoint. The assumption is that the access_management.granted_access table contains all the granted access information.

We’ll need to run this script to see which users were granted access. This gives us a baseline to compare against later.

After running this script, the following result should be returned:

Output:
{
  "user_id": 1,
  "user_name": "some_analyst_1"
}

Script 2: Analyzing Access Changes – Permission Revocation

Now we investigate if the analysts’ access was revoked. We need to examine permission change logs, focusing on when access was removed.

-- Create a temporary table to store the permission changes
CREATE TEMPORARY TABLE permission_changes (
    change_id INT,
    user_id INT,
    endpoint_name VARCHAR,
    permission_type VARCHAR,
    change_timestamp TIMESTAMP
);

-- Insert the changes related to 'production_reporting' endpoint
INSERT INTO permission_changes (change_id, user_id, endpoint_name, permission_type, change_timestamp)
VALUES
    (1, 1, 'production_reporting', 'GRANT', '2024-01-01 10:00:00'),
    (2, 1, 'production_reporting', 'REVOKE', '2024-01-02 14:30:00'),
    (3, 2, 'production_reporting', 'GRANT', '2024-01-01 11:15:00'),
    (4, 2, 'production_reporting', 'REVOKE', '2024-01-02 15:45:00'),
    (5, 3, 'production_reporting', 'GRANT', '2024-01-01 12:00:00');

-- Select all the changes related to the analysts
SELECT
    change_id,
    user_id,
    endpoint_name,
    permission_type,
    change_timestamp
FROM
    permission_changes
WHERE
    user_id IN (1, 2, 3)
    AND endpoint_name = 'production_reporting';

This script simulates a permission change log. It inserts entries representing grants and revokes for the ‘production_reporting’ endpoint, specifically for the analysts we’re investigating. The permission_changes table is created and populated. The final SELECT statement filters for the specific analyst IDs and endpoint.

This script demonstrates how to track permission changes. The assumption here is the permission_changes table records every permission change, including grants and revocations.

After running this script, the following result should be returned:

Output:
{
  "change_id": 2,
  "user_id": 1,
  "endpoint_name": "production_reporting",
  "permission_type": "REVOKE",
  "change_timestamp": "2024-01-02 14:30:00"
}

Script 3: Detailed Access Verification & Timestamp Correlation

This final script verifies the access status at the time of the reported issue, correlating the access changes with the outage time.

-- Verify access for each analyst around the reported outage time
SELECT
    user_id,
    endpoint_name,
    permission_type,
    change_timestamp
FROM
    permission_changes
WHERE
    user_id IN (1, 2, 3)
    AND change_timestamp BETWEEN '2024-01-02 14:00:00' AND '2024-01-02 15:00:00';

This script filters the permission_changes table to show access status changes that occurred between 2:00 PM and 3:00 PM on January 2nd. This helps determine if access was revoked around the time the analysts reported the issue. The BETWEEN clause effectively pinpoints the relevant timeframe.

After running this script, the following result should be returned:

Output:
{
  "user_id": 1,
  "endpoint_name": "production_reporting",
  "permission_type": "REVOKE",
  "change_timestamp": "2024-01-02 14:30:00"
}