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.

tayloramy's avatar
tayloramy
Icon for Super User rankSuper 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
      Icon for Resolver III rankResolver 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.