Row Level Security to Segragate Data in Azure SQL Database Performance

Question

Row Level Security to Segragate Data in Azure SQL Database Performance

Kman 41

I have a requirement which is for Doctors to only see data related to their speciality. I have decided to use row level security in Azure SQL Database rather than Database per tenant for each speciality.

I am not sure the performance of Azure SQL Database as I will be using one database with row level security to segregate the data for each Doctor, there will be a lot of updates into the Azure SQL. I am not sure if the Azure SQL can handle the updates which will be coming from a Web App.

The architecture consist of Web App, Azure Service Bus, Azure function which will transform the data and then load the JSON into Azure SQL Database.

How does the Authentication work if your using a Web Interface how would RLS work? How would it know who Doctor A or Doctor B is as they will looking at different data based on the RLS.

Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2021-06-02T20:57:44.013+00:00

Hi @Kman Welcome to Microsoft Q&A and Thank you for posting your question. Please check this Role level security doc and security policy to filter the rows each tenant can access and in the meantime I will reach out to our PG to get more insights.
Regards,
Oury
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2021-06-05T04:11:28.577+00:00

Hi @Kman From the authentication standpoint the best for RLS in Azure SQL is to use different AAD groups with different group members. A good resource for RLS implementation with example. The performance of the RLS filter is totally dependent on how you implement the filtering Table Valued Function, and indexes on the relevant tables used in the TVF.
The full working sample is available here. In general you shouldn’t be concerned about performance as the policy predicate is inline so the performance would be the same as if the logic defined in the policy would be written directly into the query. In the demo a very basic authentication mechanism was implemented . We would strongly suggest to use something more modern and secure like OAuth (via Azure AAD, Azure AAD B2C or Auth0 for example)

Regards,
Oury
Oury Ba-MSFT 21,156 Reputation points Microsoft Employee Moderator

2021-06-07T15:44:04.907+00:00

Hi @Kman Please let us know if you have further query or need more clarification.
Regards,
Oury