Forum Discussion

Jaize's avatar
Jaize
Regular Visitor
4 months ago
Solved

How do you apply dynamic RLS in OneLake Security

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/fi...
lakehouse
  • tayloramy's avatar
    tayloramy
    4 months ago

    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

     

  • rizalard0684's avatar
    rizalard0684
    4 months ago

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

BHANUPURAM's avatar
BHANUPURAM
Icon for Advocate II rankAdvocate II
4 months ago

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