Forum Discussion
How do you apply dynamic RLS in OneLake Security
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
+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.
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