Row level security

by

Implementing Row‑Level Security in Microsoft Fabric Using Delta Tables and Security Policies

Row‑Level Security (RLS) is essential when multiple users access shared datasets but should only see data they are authorized for. In Microsoft Fabric, you can implement RLS using Delta tables, table‑valued functions, and security policies — similar to SQL Server, but adapted for Lakehouse/Warehouse workloads.

In this article, we’ll walk through:

  • Creating a security schema
  • Building a user‑to‑company access table
  • Inserting initial data
  • Creating a table‑valued function (TVF) to determine access
  • Applying the TVF via a Security Policy
  • Testing the final result

Let’s dive in.

Step 1 — Create the Security Schema

Before creating security‑related objects, we define a dedicated schema to keep things organized.

SQL

CREATE SCHEMA Security;

✔ What this block does

Creates a Schema called Security, which keeps all access‑control objects grouped logically and separate from your business data.

📄 Step 2 — Create the usercompanyaccess Table

This table stores which user has access to which company, along with metadata such as reason, activation flag, and timestamps.

SQL

%%sql

CREATE TABLE Security.usercompanyaccess (

principal_name STRING NOT NULL,

company_id STRING NOT NULL,

access_reason STRING,

active_flag BOOLEAN,

created_utc TIMESTAMP

)

USING DELTA;

✔ What this block does

  • Defines a Delta table that stores user–company access mappings.
  • principal_name should match the authenticated Fabric username (USER_NAME()).
  • active_flag lets you deactivate access without deleting history.
  • Delta format ensures ACID transactions and versioning.

➕ Step 3 — Insert User Access Records

Next, we seed the table with two sample users.

SQL

%%sql

INSERT INTO Security.usercompanyaccess

VALUES

(‘Ali.Ahmed@uk.bmt.org’, ‘1062’, ‘Dev Access’, true, current_timestamp()),

(‘Julian.Kellett@uk.bmt.org’, ‘1098’, ‘Admin’, true, current_timestamp());
Show more lines

✔ What this block does

  • Adds initial access rows for Ali Ahmed and Julian Kellett.
  • Stores their access reason and enables the access via active_flag = true.

🧠 Step 4 — Create or Alter the Security Predicate Function

This is the heart of the RLS implementation.
The function returns is_allowed = 1 if the logged‑in user should see rows for the relevant company.

SQL

— Alter the function for the SalesRep evaluation

ALTER FUNCTION Security.tvf_securitypredicate(@OBJECT_SEQ AS nvarchar(50))

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(

SELECT 1 AS is_allowed

WHERE EXISTS (

SELECT 1

FROM Security.usercompanyaccess a

WHERE a.active_flag = 1

AND a.principal_name = USER_NAME()

AND a.company_id = JSON_VALUE(@OBJECT_SEQ, ‘$.COMPANY_ID’)

)

);

GO
Show more lines

✔ What this block does

ComponentPurpose
USER_NAME()Identifies the logged‑in Fabric user
JSON_VALUE(@OBJECT_SEQ, '$.COMPANY_ID')Extracts the company ID from the row‑level JSON payload
EXISTS (...)Only returns a row if the user has an active access record
RETURNS TABLEAllows SQL Engine to use it as a filter predicate

This ensures only users matching the company ID in the dataset can view the row.

🛡️ Step 5 — Apply the Security Policy

This attaches the predicate function to your target table.

SQL

CREATE SECURITY POLICY CompanyFilter

ADD FILTER PREDICATE Security.tvf_securitypredicate(OBJECT_SEQ)

ON mdm.TESTproject_core_details_UKS

WITH (STATE = ON);

GO
Show more lines

✔ What this block does

  • Creates an always‑on row‑filtering policy.
  • The predicate is executed per row on the table mdm.TESTproject_core_details_UKS.
  • Users now only see their assigned company data.

🔍 Step 6 — Test the Policy

SQL

SELECT * FROM mdm.TESTproject_core_details_UKS;

✔ What this block does

When run by different users:

  • Ali Ahmed sees only rows for Company 1062
  • Julian Kellett sees only rows for Company 1098
  • Users without access see zero rows

This confirms the security policy is working.

🎉 Final Thoughts

You now have a fully working Row‑Level Security framework inside Microsoft Fabric using:

  • Delta tables
  • Security schema
  • Permission mapping table
  • Row‑filtering TVF
  • Security Policies

This is scalable, auditable, and easily extensible for new datasets and users.

Leave a Comment