Databricks TL;DR: Table ACLs and Dynamic Security

Table ACLs and Dynamic Security

/tldr: Implementing governance with SQL: granular access control and data redaction.

Governance Security SQL Dynamic Views

1. Table ACLs (Access Control Lists)

Table ACLs provide **coarse-grained security**, controlling who can perform specific actions (like SELECT, MODIFY, CREATE) on database objects (catalogs, schemas, tables, views).

The `GRANT` and `REVOKE` Syntax

Permissions are assigned using standard SQL DCL (Data Control Language). The most common privilege is SELECT.

GRANT Syntax


GRANT SELECT ON TABLE <catalog>.<schema>.<table_name> TO <user_or_group>;


GRANT CREATE ON SCHEMA <catalog>.<schema_name> TO <user_or_group>;

REVOKE Syntax


REVOKE SELECT ON TABLE <catalog>.<schema>.<table_name> FROM <user_or_group>;

**Hierarchy:** Permissions are checked in a hierarchical manner (e.g., catalog > schema > table).
**Principle:** You must have permissions granted *explicitly* to interact with the object.

2. Dynamic Views for Row-Level Security (RLS)

Row-Level Security (RLS) restricts which rows a user can see in a table, based on their identity or group membership. Databricks achieves this using **Dynamic Views** combined with built-in functions.

RLS Implementation Example (Filtering by User)

The key is using functions like current_user() or is_account_group_member('<group_name>') inside the view definition's WHERE clause.

Dynamic View Definition for RLS


CREATE VIEW restricted_sales_data AS
SELECT * FROM base_sales_table
WHERE
    -- User can only see sales data for their own region
    region = CASE
        WHEN is_account_group_member('eu_sales_team') THEN 'Europe'
        WHEN is_account_group_member('us_sales_team') THEN 'USA'
        ELSE 'None' -- Default to deny access
    END;

**Security Policy:** Once the dynamic view is created, you must **revoke** the SELECT permission on the **base table** and only GRANT SELECT on the **dynamic view**.

3. Dynamic Views for Column Masking

Column Masking (a form of Column-Level Security - CLS) controls which columns a user can see, or how the data in those columns is formatted (e.g., masking or hashing sensitive data).

CLS Implementation Example (Masking PII)

Instead of using the WHERE clause, the logic is applied directly in the SELECT statement, determining the output for each column based on the user's role.

Dynamic View Definition for Column Masking


CREATE VIEW secured_customer_pii AS
SELECT
    customer_id,
    order_id,
    -- Apply masking logic to the SSN column
    CASE
        WHEN is_account_group_member('pii_full_access') THEN ssn_number -- Full access for admins
        ELSE CONCAT('XXX-XX-', RIGHT(ssn_number, 4)) -- Masked for everyone else
    END AS ssn_number,
    transaction_amount
FROM base_customer_pii;

**Dynamic:** The view logic is evaluated at query runtime for every user.
**Best Practice:** Always apply both RLS and CLS via Dynamic Views, restricting access to the underlying raw tables.

Dynamic Views are the single most flexible and powerful tool for fine-grained security in Databricks SQL.