Databricks Unity Catalog: Diagnose Unauthorized Delta Sharing Share Access Configuration

This tutorial focuses on diagnosing unauthorized Delta Sharing share access configurations within Databricks Unity Catalog. We’ll use Databricks SQL to investigate potential access issues, building progressively complex queries to pinpoint the root cause.

Script 1: Initial Share Access Verification

This script establishes a minimal scenario and verifies the basic accessibility of a Delta Sharing share.


-- Create a table in Unity Catalog for demonstration.
CREATE TABLE IF NOT EXISTS demo_table (
    id INT
);

-- Insert some sample data.
INSERT INTO demo_table (id) VALUES (1);
INSERT INTO demo_table (id) VALUES (2);

-- Create a Delta Sharing share (Assume it's already created).
--  This share needs to be configured in Delta Sharing to be accessible.
-- For example: "demo_sharing_share"

-- Verify basic access from a Databricks SQL query.
SELECT  FROM demo_table;

-- Final Validation: The SELECT statement should return the data from demo_table.
-- This confirms the share is at least accessible at the most basic level.
SELECT COUNT() FROM demo_table;

Explanation:

We create a simple table (`demo_table`) within the Unity Catalog.
We insert some sample data for testing.
The success of this script depends on the existence and configuration of a Delta Sharing share (e.g., `demo_sharing_share`). This share must have been created and granted access within the Delta Sharing platform.
The `SELECT ` statement attempts to retrieve the data from the table through the share.
The final `SELECT COUNT()` confirms that the share is indeed accessible and returns the expected result.

Output:

<The exact output of SELECT COUNT() FROM demo_table is displayed here. The output will be a single integer value (e.g., "2")>

Script 2: Analyzing Share Access Grants – User Perspective

This script examines the access grants applied to the share from the perspective of a specific user. This helps identify if the user has the necessary permissions.


-- Analyze share access grants for a specific user.
-- Replace 'your_user_name' with the actual user you want to investigate.
SELECT
    GRANT_TYPE,
    OBJECT_TYPE,
    OBJECT_KMS_KEY_ID,
    TENANT_ID,
    ACCESS_MASK
FROM
    UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_ACCESS_GRANTS
WHERE
    USER_NAME = 'your_user_name'
    AND SHARE_NAME = 'demo_sharing_share';

-- Final Validation: This query will return rows representing the specific
-- access grants granted to the user for the share.  Check the
-- GRANT_TYPE, OBJECT_TYPE, and ACCESS_MASK columns for any unexpected
-- permissions (e.g., 'READ' vs. 'READ_WRITE').
SELECT COUNT() FROM UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_ACCESS_GRANTS
WHERE USER_NAME = 'your_user_name' AND SHARE_NAME = 'demo_sharing_share';

Explanation:

This script queries the `UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_ACCESS_GRANTS` table to retrieve access grants for a specific user.
The `GRANT_TYPE` column indicates the type of access granted (e.g., ‘READ’, ‘READ_WRITE’).
The `OBJECT_TYPE` column specifies the type of object being accessed (e.g., ‘TABLE’).
The `ACCESS_MASK` defines the level of access granted.
Crucially, this script provides the user’s access to the share. A misconfigured `ACCESS_MASK` could be the root cause of unauthorized access.

Output:

<The exact output of the final SELECT statement is displayed here. The output will be a single integer value (e.g., "0" or "1")>

Script 3: Investigating Share Configuration – Access Mask Level

This script examines the share configuration itself, focusing on the `ACCESS_MASK` level. It compares the configured access mask to the user’s granted access.


-- Examine the share configuration to verify the access mask.
-- Replace 'demo_sharing_share' with the actual share name.
SELECT
    ACCESS_MASK,
    CONVERT(VARCHAR(255), ACCESS_MASK)
FROM
    UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_CONFIGURATION
WHERE
    SHARE_NAME = 'demo_sharing_share';

-- Final Validation: The SELECT statement returns the access mask configured for the share.
--  Compare this mask with the output of Script 2 to identify discrepancies.
SELECT COUNT() FROM UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_CONFIGURATION
WHERE SHARE_NAME = 'demo_sharing_share';

Explanation:

This script queries the `UNITY_CATALOG.IMPACT.DELTA_SHARING_SHARE_CONFIGURATION` table to retrieve the share’s access mask.
The `ACCESS_MASK` column determines the level of access granted to users.
Comparing the share’s configured access mask with the user’s granted access (from Script 2) is critical. A mismatch indicates a potential access issue.

Output:

<The exact output of the final SELECT statement is displayed here. The output will be a single string value (e.g., "READ" or "READ_WRITE")>