How do you apply dynamic RLS in OneLake Security

[Jaize]

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!

[tayloramy · Accepted solution]

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

[v-karpurapud]

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.

[v-karpurapud]

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.

[anuragccsu]

@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 

[BHANUPURAM]

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;

GO

STEP 2: Create a Table value function as follows:

CREATE FUNCTION Security.fn_owner_rls(@owner_id INT)

RETURNS TABLE

WITH SCHEMABINDING

AS

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()

    )

);

GO

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);

GO 

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

[tayloramy · Accepted solution]

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

[Jaize]

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.

[rizalard0684 · Accepted solution]

+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.