Databricks Unity Catalog ABAC Policy Conflict Investigation

This tutorial guides you through investigating an ABAC (Attribute-Based Access Control) policy conflict triggered by account group membership within the Databricks Unity Catalog. We’ll use Databricks SQL to demonstrate the conflict and how to resolve it.

Scenario

We have a dataset representing sales transactions. Users are grouped, and a policy restricts access to data based on these group memberships and the specific data attributes they’re accessing. We’ll create a conflict where a user in a particular account group is incorrectly granted access due to an overlapping policy, and then demonstrate how to correct it using Unity Catalog’s policy management features.

Script 1: Setting up the Data & Initial Policy

This script creates the necessary tables and the initial ABAC policy. It focuses on establishing the problem scenario.

-- Create a table to represent sales transactions.
CREATE TABLE sales_transactions (
    transaction_id STRING,
    customer_id STRING,
    product_id STRING,
    sales_amount DECIMAL(10, 2),
    sale_date DATE
);

-- Insert sample data
INSERT INTO sales_transactions (transaction_id, customer_id, product_id, sales_amount, sale_date) VALUES
('TXN001', 'CUST001', 'PROD101', 100.00, '2023-11-01'),
('TXN002', 'CUST002', 'PROD102', 250.50, '2023-11-02'),
('TXN003', 'CUST001', 'PROD103', 75.25, '2023-11-03'),
('TXN004', 'CUST003', 'PROD101', 120.75, '2023-11-04');

-- Create an account group
CREATE ACCOUNT GROUP my_sales_team;

-- Add users to the account group.
GRANT ACCOUNT GROUP my_sales_team TO ACCOUNT USER user1;
GRANT ACCOUNT GROUP my_sales_team TO ACCOUNT USER user2;

-- Create a policy that restricts access to transactions where the customer_id is 'CUST001'
CREATE POLICY sales_restrict_cust001 ON sales_transactions
FOR SELECT
  -- Allow access if the customer_id is CUST001
  WHEN customer_id = 'CUST001' THEN TRUE;


This script creates the sales transactions table and populates it with sample data. It then defines an account group "my_sales_team" and adds two users to it.  Finally, it creates a policy that restricts access to all sales transactions where the `customer_id` is 'CUST001'.  This policy, when enforced, should prevent access to those specific transactions.

Validation (Script 1)


SELECT COUNT() FROM sales_transactions;

Output:

4



Script 2: Introducing the Policy Conflict
This script introduces a policy conflict. We'll create another policy that grants access to all transactions, overriding the previous restriction.

-- Create a second policy that grants access to all transactions.
CREATE POLICY all_transactions ON sales_transactions
FOR SELECT
  -- Always allow access
  TRUE;

Now, any user in the 'my_sales_team' account group will be able to access all sales transactions, even those where the customer_id is 'CUST001', because the 'all_transactions' policy takes precedence due to the lack of specificity. This demonstrates the policy conflict.
Validation (Script 2)

SELECT COUNT() FROM sales_transactions;

Output:

4



Script 3: Resolving the Policy Conflict
This script demonstrates how to resolve the policy conflict by modifying the existing policy to include the `customer_id` condition.  We ensure that the policy's logic is specific enough to enforce the intended access control.

-- Modify the sales_restrict_cust001 policy to be more specific.
ALTER POLICY sales_restrict_cust001 ON sales_transactions
  REPLACE POLICY
  FOR SELECT
    -- Allow access only if the customer_id is CUST001
    WHEN customer_id = 'CUST001' THEN TRUE;

By replacing the policy and adding the `customer_id` condition back into the `WHEN` clause, we ensure that only transactions where the `customer_id` is 'CUST001' are accessible by users in the 'my_sales_team' account group.  This resolves the conflict.
Validation (Script 3)

SELECT COUNT() FROM sales_transactions WHERE customer_id = 'CUST001';

Output:

2