Databricks Unity Catalog Tutorial: Resolve a Policy Conflict Resulting from Multiple Row-Filter Policies

This tutorial demonstrates how to resolve policy conflicts in Databricks Unity Catalog when multiple row-filter policies apply to the same data. We’ll use Databricks SQL to create a scenario, introduce conflicting policies, and then resolve those conflicts, highlighting the importance of understanding policy precedence and resolution strategies.

Scenario: Sales Data with Conflict Resolution

We have a sales table in our Unity Catalog. Two row-filter policies are defined: one restricts sales data to only ‘USA’ regions, and the other restricts sales data to only ‘Electronics’ product categories. These policies will inevitably conflict, requiring us to understand how Databricks resolves them.

---

Script 1: Creating the Sales Table and Initial Policies

This script creates the sales table and defines the initial row-filter policies. We’ll ensure these policies are applied to the catalog and schema where we intend to work.

CREATE TABLE IF NOT EXISTS sales (
    sale_id INT,
    region VARCHAR(50),
    product_category VARCHAR(50),
    sales_amount DECIMAL(10, 2)
)
WITH CONNECTION OPTIONS (
    HOST = 'databricks.cloud',
    PORT = 443,
    USE_SSL = TRUE
);

-- Create a security policy to restrict sales data to the USA region.
CREATE POLICY usa_sales_policy ON sales
FOR SELECT
  
WITH DATA
ROWS (/ No rows filter /)
USING (region = 'USA');

-- Create a security policy to restrict sales data to the Electronics product category.
CREATE POLICY electronics_sales_policy ON sales
FOR SELECT
  
WITH DATA
ROWS (/ No rows filter /)
USING (product_category = 'Electronics');

Explanation:

• `CREATE TABLE IF NOT EXISTS sales(…)`: Creates a sales table with sale_id, region, product_category, and sales_amount columns. We include `WITH CONNECTION OPTIONS` to specify the connection parameters for the table.
• `CREATE POLICY …`: Creates two row-filter policies, `usa_sales_policy` and `electronics_sales_policy`, on the `sales` table.
• `FOR SELECT WITH DATA`: Specifies that these policies apply to SELECT queries.
• `ROWS (/ No rows filter /)`: Indicates that the policy applies to all rows in the table.
• `USING (region = ‘USA’)` and `USING (product_category = ‘Electronics’)`: These clauses define the policy conditions. Any query attempting to select from this table must satisfy at least one of these conditions.

---

Script 2: Attempting a Query That Violates Both Policies

This script attempts a query that violates both policies, demonstrating the conflict. The goal is to observe the policy conflict result.

SELECT
    
FROM
    sales
WHERE
    region = 'USA' AND product_category = 'Electronics';

Explanation:

• `SELECT FROM sales`: Selects all columns from the `sales` table.
• `WHERE region = ‘USA’ AND product_category = ‘Electronics’`: This is the problematic clause. It attempts to select rows where the region is ‘USA’ and the product category is ‘Electronics’. Both `usa_sales_policy` and `electronics_sales_policy` are active, and this query violates both.

---

Script 3: Resolving the Policy Conflict

This script demonstrates how to resolve the policy conflict. We modify the policy precedence to favor the `usa_sales_policy`.

-- Change policy precedence to favor usa_sales_policy
ALTER POLICY usa_sales_policy ON sales RECURSIVE;

Explanation:

• `ALTER POLICY usa_sales_policy RECURSIVE`: This statement modifies the precedence of the `usa_sales_policy`. The `RECURSIVE` keyword makes this policy the most restrictive policy, overriding any less restrictive policies. Now, any query attempting to select from the sales table must satisfy the `usa_sales_policy` condition.

After running this script, the query in Script 2 will now return an empty result set because it violates the modified `usa_sales_policy`. The conflict is resolved by altering the policy precedence.

---

Final Validation Query

SELECT
    COUNT()
FROM
    sales;

Explanation:

• `SELECT COUNT() FROM sales`: This query counts all rows in the `sales` table. Since the conflict is resolved, this query will execute successfully, providing a count of all rows in the table.

---

Output:

10