Forum Discussion
Onelake security with large tables
Thank you for the response,
What is so confusing for me is that I could make it work with a DirectLake on SQL model, just in a very inconvenint way:
1. Give user read on the lakehouse
2. Give user read role in Onelake security
3. This is the confusing one, I had to explicitly add an RLS rule on all tables I wanted the user to see.
With no RLS rules added, I didn't see everything as one would expect, I saw nothing. My only guess to why this worked is that when RLS applied, the query got small enough to not having to fallback to DirectQuery and thus not going through the SQL endpoint, but this is just my guess, I have no clue tbh. This is however not a feasible solution so I moved on.
Anyways, it turned out we had not used any V-Ordering on the table, so I turned V-Order on and ran optimize + z-order and i shrunk the table by almost half, so now DirectLake on OneLake is working again.
Super UserHi FabricJacob,
What size spark nodes are you using?
THe way OneLake security works, it spins up additional spark instances to check for access, so if you reduce the size of the default node in your workspace, you should have a better experience. I wish this was documented better.
Also, DIrectLake on SQL models do work with OneLake Security, there's just one more step, you need to switch the SQL Endpoint auth mode to user identity mode instead of delegated identity mode. You can do that by opening the SQL endpoint in Fabric, clicking on the security tab, and then in there you can change the auth mode. Once it is set to User Identity, OneLake security applies to the SQL Endpoint and to all DirectLake on SQL models.
To be honest I know very little of what's going on with Spark Nodes, but Node size in all my workspaces is Medium 1 - 6 nodes. What's the drawback on reducing the size?
I should have mentioned it, but User identity mode has been set during all my testing of this, and the semantic model uses Default SSO connection.