How to write Azure Databrick code to restrict RLS and automatically add to AD Group

Question

Hi there, I would like to ask how I can write a code to restrict Row-Level-Security in Azure Databrick. Let me describe the situation:

1.       Input

-            I have an Excel file named External_Suppliers_ADGroup2 that uploaded into the Databricks

-            Everytime I need to add a new user – I just insert new rows including Email of the user and SupplierNumber that the user should see

Upload the mapping file into Azure Databricks

 

2.       Need to restrict RLS on Azure Databrick

 

I will create a notebook to restrict the RLS on level Azure Databrick – not in PBI (for the sake of PBI better performance). So I write like this – called [DataBrick Code]:

---

select * from db_abc.dim_Controll

Where CASE WHEN (is_member('AD_Group1_Internal))  THEN ___##Show All ___

WHEN (is_member('AD_Group2_External'))  THEN ##Show RLS ??

ELSE FALSE 

END

---

·       So the purpose is I want only that user email can see that specific supplier

From the above example: mmerino@abc.com only see SupplierNumber 33000000

 

·       I have 2 AD Group, the AD Group 1 is internal, so no need to restrict. AD Group 2 is for external that’s needs restriction.

·       Below is my data model, mapping by SupplierNumber

3.       Adding new users into the ADGroup2:

After doing the RLS code in Databrick, I would like to add these new users email into the ADGroup2. ADGroup2 are external suppliers, who can access the report at RLS level.

 

My Questions:

1.       How can I  write the [DataBrick code], to restrict the RLS to ADGroup2? Can you help me with the code

2.       How can I write a code on Azure Databrick that after restricting the RLS, also automatically add these new user emails into ADGroup2?

 

Thank you.

Answer 1

@Daniel Hoang Nguyen - Thanks for the question and using MS Q&A platform.

For Q1, there can be many ways, one way is to create View and in SELECT of columns you can put this CASE and use the IS_MEMBER.

See if the below code helps.

For Q2, you can check this article: Configure SCIM provisioning using Microsoft Entra ID (Azure Active Directory) describes how to set up provisioning to Azure Databricks using Microsoft Entra ID.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.