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.

michelmetran's avatar
michelmetran
Frequent Visitor
17 days ago

I'm facing the same difficulty and, from what I've read and understood, there's no way around it the way I'm doing it. I'd like to confirm.

I have a lakehouse. From the lakehouse tables I created a semantic model using the Direct Lake import method. I would like to create a Row Security Level using OneLake Security. However, the query I made is not allowed. I get the error that says dynamic RLS is not possible.

SELECT * FROM digital.dim_usuarios_autorizados WHERE EmailUsuario = USER_NAME()  -- or   SELECT * FROM digital.dim_usuarios_autorizados WHERE LOWER(EmailUsuario) = LOWER(USERPRINCIPALNAME())

I thought that if I filtered the dim_usuarios_autorizados table, I could reproduce the RLS I have in another dashboard (where I use the import method and the RLS defined in the Semantic Model).

 

In the responses, I saw suggestions to use the RLS defined in the SQL Endpoint...

However, I understand that if I implement RLS in the SQL Endpoint, I wouldn't be able to use the dashboard in the Direct Lake method, correct?

Would it have to be a Direct Query method?

It's frustrating to reach the final stage and discover that it's not possible to build the RLS...

I would like to implement RLS in Lakehouse because, in this way, both the dashboards and APIs (in GraphQL) would already "filter" the data from the fact tables.