Advancing Spark - Implementing Row Level Security in Databricks

RLS, or Row Level Security, is another one of those "maturity features" that is often used as an argument to demonstrate how lake-based platforms are still behind the more mature relational data stores, however from Databricks Runtime 7.3, we now have a solution!
This week, Simon looks into the is_member() function and how we can use it to implement secure, performant security within a lake-based data model! This has HUGE impacts on how successfully we can model warehouses within a lake structure, and is a great thing to see!
More info on those Dynamic View Functions here: docs.databrick...
And as always, don't forget to Like & Subscribe, and stop by our site to see if we can help you on your Data Lakehouse journey - www.advancinganalytics.co.uk

[@Obizzy8]

Very useful feature for a Enterprise lake house approach! Thanks for the constant great content 👍

[@nickhurt8416]

Awesome new capability - thanks for sharing!

[@drummerboi4eva]

Nice stuff !! Super encouraging to see performance is not deterred while using RLS in Databricks

[@gulamsardar7799]

Thanks Again Simon, keeping upto date with Spark has become so simple because of you. Though I am a sql expert, I am struggling a little with scala, can you please guide to some courses which can help me in learning scala(hands on), thank in advance !!

[@film-masti-777]

This is good but very basic level. is there any advance use case you can present pls? which includes CLS, table level, RBAC etc.

[@Monsalvo888]

Really useful, thanks!

[@SAMSARAN2108]

Thanks for sharing the RLS concepts in Databricks in this video. I have a requirement like I will create an Azure AD group (SalesAPAC) for Sales Domain- NAM Region combination at Power BI level, then users from this group should have access only Sales related workspace and its reports with NAM region data only from reports/visualizations. The same logic I need to apply the same logic here in Azure Databricks by passing userid, domain and region into Databricks. so user should be able to see only Sales related tables/views/other objects and should fetch only NAM data from those tables. As per this video, we need to create a new group for RLS it seems. Is there any way to sync the Azure AD, use it inside Azure Databricks and define the RLS logic based on the Azure AD group? Regards, Saravanan.S

[@bbrocks5530]

Did you get the solution?

[@SAMSARAN2108]

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

[@viveksomvanshi3767]

As always, very well summarized. Thanks Simon. Does this mean if we get it working at enterprise level then we don't need any sort of OLAP engine i.e. Sql db, warehouse? Also, in general if I can achieve similar objectives through schema and views in sql then what will be the advantages of RLS since organization structures are generally complex than demonstrated particularly in any RLS concept?

[@AdvancingAnalytics]

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

[@l_combo]

Thanks for sharing, this is a great start, how do you see this scaling to n dimensions e.g. member of a country (shown), member of a business unit, member of a role etc. otherwise I suspect the more traditional security makes more sense on the layer where the data is being analysed / view such as BI.

[@AdvancingAnalytics]

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 :)

[@zycbrasil2618]

Hi Simon.. Data object privileges right? Does it support column level security?

[@nickhurt8416]

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

[@mohdshoaib3296]

on global temp view is-member is not working ..any guidance.thx

[@AdvancingAnalytics]

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? :)

[@umarhussain9334]

Awesome, how expensive is this compared to an analysis service with RLS (say S1)

[@AdvancingAnalytics]

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.

[@umarhussain9334]

@@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

[@BitaRastgar]

it is nice but it is not dynamic. create a view/table using is_member(country) , then if you remove a user from a group, that user will still have access to all data when the view/table was created! it would have been nice if a user is removed AFTER view/table creation, then that user would be allow to see what he/she allowed to see right now. by the way, is this part of SQLAnalytics also?

[@gran_turing]

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.