Row and column level security on unity catalog in different approaches

[Rupa0503]

I have one task for you which is related to unity catalog I want to implement row and column level security on databricks for few tables please let me know  different kind of approaches and methods.

We are using azure cloud and databricks witj unity catalog enabled 

[balajij8]

Hi Rupa,

You can follow below for different approaches for implementing row and column level security

1. ABAC Policies (Attribute-Based Access Control) 

ABAC is the new approach that uses governed tags to centrally enforce security across multiple tables dynamically. You can define policies once at catalog, schema, or table level and it automatically inherits to child objects. Its Tag-driven and it applies dynamically to any table/column matching tag conditions for centralized management across entire scope. You can set 2 types of policies. Row Filter Policies controls which rows users can see. Column Mask Policies redacts or transform column values based on its setup

You must use governed tags (account-level tags with enforced values) and create UDFs (SQL or Python functions) for filter/mask logic. Serverless compute or Runtime 16.4+ is required.

2. Manual Row Filters & Column Masks - Table Based Security

You can apply security directly to individual tables using simple commands. Its simpler for single-table scenarios but doesn't scale well as ABAC. Row filters restrict which rows a user can see in a table. Column Masks control what values a user sees for specific columns

Key difference from ABAC - The manual filters/masks apply to ONE table at a time. ABAC policies apply dynamically across multiple tables based on tags. More details here

3. Dynamic Views - Traditional SQL Based Approach

You can create views with built-in conditional logic using is_account_group_member() type of functions based on the cases. It's used when you want to expose a curated, transformed or joined version of data to users who don't have access to the underlying tables. Its Simple, portable with no special requirements. It's harder to maintain at scale.

[Rupa0503]

Other than Attribute-Based Access Control any other approaches if possible could you please explain those approaches in detailed?

[balajij8]

Yes.

Row Level Security - You can apply security directly to individual tables using direct commands. You use ALTER TABLE  SET ROW FILTER to attach a UDF that returns true/false for each row, controlling which rows each user can see. It can be applied directly to one table at a time & the filter is transparent to users querying that table. You attach a function to table & it filters data at query time

-- Create filter function
CREATE FUNCTION main.default.dept_filter(dept STRING)
RETURN dept = current_user() OR is_member('admins');

-- Apply to specific table
ALTER TABLE main.default.employees 
SET ROW FILTER catalog.schema.dept_filter ON (department);

Column Masks - You use ALTER TABLE SET MASK to attach a UDF that transforms column values such as redacting SSN to XXX controlling what users see in specific columns. Its applied per column on each table & masking happens automatically at query time. You attach function to column & it transforms at query time

-- Create mask function
CREATE FUNCTION main.default.mask_ssn(ssn STRING)
RETURN CASE WHEN is_member('HR') THEN ssn ELSE 'XXX' END;

-- Apply to specific column
ALTER TABLE main.default.employees 
SET MASK catalog.schema.mask_ssn ON (ssn_column);

Dynamic Views - You create views with CASE WHEN is_member('group') or current_user() logic embedded in the SELECT statement to conditionally filter rows or mask columns. Users query the view instead of the base table & the security logic lives in the view definition. Its standard view with hardcoded logic like traditional world.

Row Level Security using Dynamic Views

CREATE VIEW main.sales_filtered AS
SELECT *
FROM main.sales_raw
WHERE 
  -- Show all data to admins
  is_member('admins') 
  -- Show only US data to analysts
  OR (is_member('analysts') AND region = 'US')
  -- Show only user's own records to sales reps
  OR (is_member('sales_reps') AND sales_rep = current_user());

Column Level Security using Dynamic Views

CREATE VIEW main.employees_masked AS
SELECT 
  employee_id,
  name,
  -- Mask SSN except for HR
  CASE WHEN is_member('HR') THEN ssn ELSE 'XXX' END AS ssn,
  -- Mask salary except for Finance
  CASE WHEN is_member('Finance') THEN salary ELSE NULL END AS salary,
  department
FROM main.employees_raw;

Manual filters/masks secure the base table directly (actual table). Dynamic views create a separate secured object (users must query the view)

Method	Security Location	User Queries
Row Filter	UDF attached to table	Base table
Column Mask	UDF attached to column	Base table
Dynamic View	Logic in view SQL	View