Row level Security (RLS) in Azure Synapse vs Power BI

0
votes

I am building a new data warehouse system for one of my client and using Azure Synapse with Power BI. I am currently laying out the access framework for the system and exploring the Pros and Cons of applying ROW LEVEL Security at various points.

My understanding is Power BI works best with import and if I apply RLS on the database, I am not sure how RLS would work in the Power BI.

However if I apply RLS in Power BI, the reporting team would have to take care of it in every report. Also I do not want to give full security controls to the reporting team as the data is sensitive.

Can anyone here please help and suggest what would be the right way to apply RLS?

2
databasepowerbiazure-synapserow-level-securityrls

2 Answers

1
votes

There are 2 aspects of reporting from Synapse via powerBI:

  1. Connect Live: In this scenario, the RLS would be the database RLS that would be active for all users via create security policy for RLS
  2. Import : In this scenario, the powerbi report would import all the data from synapse via an account which has entire read access on synapse. And you can create RLS at report level. In case of import, having RLS at database doesn't matter as the entire data is in the dataset and RLS needs to be created at dataset level.
0
votes

Row level security is a concept you can use when you build one report but different user will see different result based on the data she has access to.

For example consider

  • you have 2 sales managers A and B in your company.

You can

  • In Power BI desktop you can prepare/import the sales data for the reporting.
  • Configure row level security for the two managers on the sales data
  • Prepare one sales report on the entire sales data

Now when manager A see the report she will only see based on the rows she has access means only her team was involved with those sales.

On the other hand when manager B see the report she will only see based on the other set of data her team was involved with.

Without row level security how would you do that? You may need to create totally different copy of same report with two different set of data.

I tried to give the example for a simple practical scenario. You may have complex scenario and you have to decide how/when to use it.

Can have a look on this Microsoft document for some more information https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls