Did you get the solution?

@@bbrocks5530 Actually our organisation didn’t move towards Azure Databricks, but we may require this for snowflake. Thanks for reaching me.

I'd hedge my bets more - "in some circumstances, Databricks can function as your OLAP", not absolutely every case, depending on user volumes, query frequency, latency requirements etc, it's a complex question for a yes/no answer! And complexity is what it's about with RLS vs Views too. If I have three company segments, managing that via different views is easy, if I have 100, and they frequently change and evolve, that's a huge headache in code maintenance. Also affects who can do the change - a support team can easily add new groups, add/remove members, but asking support teams to define and maintain views (and apply the relevant security to the object!) is harder. As always, loads of different ways to approach it, it's just another tool in our belt to design the right security model for the problem. Simon

Yeah, it scales as far as you can create groups to back it up. There's a fair bit of potential given you can now create & map users to groups through the API, so a little python utility can do the user mapping for you... but you're right, when you get into decent numbers of different roles, it'll become fairly difficult to look after. That said, the alternative to the "is_member0" function looks at the current user instead, so you could change it to a full, many-to-many user table that does the security, giving you full flexibility inside you model - it's slightly more of a pain to implement though :)

Yes see docs.microsoft.com/en-us/azure/databricks/security/access-control/table-acls/object-privileges#column-level-permissions

If you take a blunt example of an S1 AAS and a 2-worker Databricks cluster, both ~25Gb RAM. Then it's around £1,100 AAS compared to £1,246 Databricks. But that assumes they're both turned on - Databricks has much better scaling and - the killer advantage - doesn't need to hold all the data, so can be using this technique over masses of data stored cheaply in the lake. If used right, I'd say Databricks is the much cheaper option.

@@AdvancingAnalytics my thoughts exactly with a properly partitioned using the schema of your choice this becomes so much more flexible. Throw in the power of python and this becomes a good sell. Thanks for the video v helpful

Never tried it on a global temp view, it's worth getting in touch with Databricks to discuss the use case. Worst case... just save it as a persisted view? :)

That's exactly how it works, the filtering happens at runtime not at view creation time dynamically based on the user querying the data. This is part of the security model that is used with SQL Analytics as well as Table ACL clusters.