topic How do you apply dynamic RLS in OneLake Security in Data Engineering

How do you apply dynamic RLS in OneLake Security

Jaize — Fri, 17 Apr 2026 08:03:28 GMT

I have a fact table inside my lakehouse and another user mapping table/file, how do I create a role which applies dynamic RLS based on the fact table’s user_id equivalent to the user mapping table/file’s id?

I’m trying to use the following SQL script but unfortunately, it won’t allow subqueries:

SELECT *

FROM dbo.test_fact_table

WHERE owner_id IN (

SELECT id

FROM dbo.user_mapping_file

WHERE name = CURRENT_USER()

)

Any help is appreciated. Thank you!

Re: How do you apply dynamic RLS in OneLake Security

tayloramy — Fri, 17 Apr 2026 13:39:32 GMT

Hi @Jaize ,

The RLS rules don't allow subqueries, or the full SQL langauge at all for that matter.

All rules must be in this form:
SELECT * FROM {schema_name}.{table_name} WHERE {column_level_boolean_1}{column_level_boolean_2}...{column_level_boolean_N}

I hope this is improved when OneLake Security goes GA.

For now, the best option is to add the user column to the table you are filtering in your ETL process.

If you want to learn more about OneLake Security, there's a live event happening on Tuesday at 18:00 UTC in the Fabric Discord server! https://discord.com/invite/RVFRmQyX?event=1487504921761677557

Re: How do you apply dynamic RLS in OneLake Security

Jaize — Fri, 17 Apr 2026 14:11:14 GMT

Thank you for your response, tayloramy.

I was just wondering.. is there an equivalent to the USERPRINCIPALNAME() DAX function to fetch the UPN or email address of the viewer I can equate to my username/email column? Apparently, CURRENT_USER() is not working either.

Re: How do you apply dynamic RLS in OneLake Security

rizalard0684 — Sun, 19 Apr 2026 08:17:45 GMT

+1 to @tayloramy that you can’t use subqueries or joins in OneLake / Fabric SQL RLS (as of today).

Regarding USERPRINCIPALNAME(), unfortunately there is no equivalent of USERPRINCIPALNAME() (DAX) available for OneLake / Lakehouse SQL RLS (as of today), this may work in Power BI DAX RLS with USERPRINCIPALNAME() but not in OneLake/Lakehouse.

My only suggestion is the same as @tayloramy's above, bring your user data (e.g. owner_email) into your fact table.

Re: How do you apply dynamic RLS in OneLake Security

BHANUPURAM — Mon, 20 Apr 2026 05:18:37 GMT

Hello Jaize,

Prerequisites (Access + Where to Configure):

Ensure you’re applying RLS on the SQL analytics endpoint of a Lakehouse (this is where T‑SQL RLS is supported)

You need elevated permissions to create security objects (typically Workspace Admin/Member/Contributor or “Control permissions” on the SQL endpoint

understood that you created user table where you maintain the user details

consider a table like dbo.user_mapping_file that maps the logged-in identity to permitted owner_id values (or whatever key filters your fact table

Ensure the identity column in your mapping table matches what SQL can evaluate (commonly USER_NAME())

How to create RLS

step 1 : Create a Security Schema

CREATE SCHEMA Security;

STEP 2: Create a Table value function as follows:

CREATE FUNCTION Security.fn_owner_rls(@owner_id INT)

RETURNS TABLE

WITH SCHEMABINDING

RETURN

(

SELECT 1 AS fn_owner_rls_result

WHERE EXISTS

(

SELECT 1

FROM dbo.user_mapping_file um

WHERE um.id = @owner_id

AND um.name = USER_NAME()

)

);

Ensure that rows where fact.owner_id exists for the current user in user_mapping_file

Step 3:

Create and Enable the SECURITY POLICY (Attach Predicate to Fact Table)

CREATE SECURITY POLICY Security.OwnerAccessPolicy

ADD FILTER PREDICATE Security.fn_owner_rls(owner_id)

ON dbo.test_fact_table

WITH (STATE = ON);

RLS enforcement happens by creating a SECURITY POLICY and attaching a FILTER PREDICATE to the target table/column.

For the Lakehouse SQL analytics endpoint, remember the enforcement mode can influence whether SQL permissions vs OneLake roles drive access behavior

step 4:

Testing / Validation Steps:

SELECT * FROM dbo.test_fact_table;

You should see only the rows where owner_id is mapped to the querying identity (based on the predicate logic).

please give KUDOS if you like my solutions

Regards,

Bhanu Priya

Re: How do you apply dynamic RLS in OneLake Security

anuragccsu — Mon, 20 Apr 2026 12:55:54 GMT

@Jaize

In Microsoft Fabric's SQL Analytics Endpoint, you cannot apply a raw SELECT statement directly as a filter. This is same as explained by @BHANUPURAM .

You must:

1. Create a Security Function and then

2. Apply a Security Policy.

Why USERPRINCIPALNAME() won't work:

USERPRINCIPALNAME() is a DAX function used in Power BI measures and Power BI-side RLS.
Fabric SQL Analytics Endpoint uses the T-SQL engine.

Please refer the Microsoft Learn documentation on how to create the secrity function and policy:

https://learn.microsoft.com/en-us/fabric/data-warehouse/row-level-security

Re: How do you apply dynamic RLS in OneLake Security

v-karpurapud — Thu, 23 Apr 2026 09:42:32 GMT

Hi @Jaize

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @tayloramy , @rizalard0684 , @BHANUPURAM and @anuragccsu for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution?If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team.

Re: How do you apply dynamic RLS in OneLake Security

v-karpurapud — Mon, 27 Apr 2026 06:39:20 GMT

Hi @Jaize

Following up since we haven't heard back regarding our previous message. Have you had a chance to review the information above? Please let us know if you have any further questions.

Regards,

Microsoft Fabric Community Support Team.