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/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!

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.

7 Replies

  • tayloramy's avatar
    tayloramy
    Super User
    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

     

    • Jaize's avatar
      Jaize
      Regular Visitor
      4 months ago

      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's avatar
        rizalard0684
        Resolver III
        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
    Advocate 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

     

  • anuragccsu's avatar
    anuragccsu
    Frequent Visitor
    4 months ago

    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 

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    3 months ago

    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's avatar
      v-karpurapud
      Community Support
      3 months ago

      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.