EClytics: Business Intelligence Services
How Can We Help?
< All Topics
Print

Row-level Security in Power BI

Row-level security is a type of security in a “Power BI report” that depends on the authorized row levels. In other words, it is “specified access on authorization context of the user who wants to log in.” With Power BI, you can willingly grant data access to selected people from the workspace. It helps to prevent data mishandling. The role filters we use for RLS impose limitations on data access at the row level.

E.g., Let’s talk about the accounting data of an American MNC. The company is widespread in various locations all over the globe. Let’s say we have ‘accounts data’ from the office in California, but we don’t want to share it with the same department in New York. RLS will let you manage the data access in its report for California. No user from the same team in New York will be allowed to access the same data if you apply RLS. That is how you can protect sensitive data and make a report robust and secure. Likewise, there are multiple ways to use RLS for a report.

Use-case scenarios for RLS:

‘RLS’ is used in so many different ways. Some of the fundamental use cases involve:

Location-specific RLS:

It means that users from a particular location are only allowed to access the data for that area, e.g., A manager from California can access the data of that region only and so on.

Department-based RLS:

A user is allowed to access data according to specified divisions in it. The users can be of the exact location but different departments such as Product, Production, etc.

Designation-based RLS:

In this scenario, a user can fetch the data according to their designation. e.g., A sales manager will have access to all sales data, irrespective of any other parameter.

Others:

Other than these three, there are numerous ways to set up your RLS, which can be time-specific, date-specific, and so on.

Types of RLS:

There are two ways to use RLS, namely:

  1. Static
  2. Dynamic

Here is a detailed definition:

Static RLS

When a Power BI developer manually develops the security-based logic, it is called Static RLS.

Important facts:

  • In Static RLS, we are limited to a specified parameter, such as a specific department, area, etc.
  • For example The area-specific access of sales data for a sales team.
  • It is restricted and used in cases where you have static security requirements and don’t need to update/change that often.  
  • The users in this method are not easily customizable.

Benefits:

It is fundamental and easy to use. You can set this up with the least possible participation from the IT dept.

Limitation:

It is static, as the name says, so it is still plus demanding to maintain in the longer run.

Dynamic RLS

The logic is defined within the ‘PBIX file’ and the data model using a relationship in Dynamic RLS.

Important facts:

  • It is applicable where you have dynamic security requirements.
  • It works for places having a wide range of users with more security roles.
  • We can customize the users much more often in it.
  • It involves a more perplexing logic.
  • It is valid where we require more than one parameter. E.g., sorting based on role, region, division, etc., or a combination. When a finance department has to access the data of a territory that includes multiple areas, it is applicable.

Benefits:

It is effortless to maintain and understand. It can solve substantial problems. Also, it is self-driven and used frequently.

Limitation:

The setup is very time-consuming and requires higher involvement from IT.

How to implement RLS in your Power BI report:

In Static:

Directions:

To set this up follow the instructions down below:

  • Open the Power BI report, and out of the tabs at the top, select the ‘Modeling’ tab and then select ‘Manage Roles’.
  • Click on ‘Create’ and in the new role, section add your customized name. Here, we have named it as, ‘new role’ only for example.
  • In the ‘Tables’, select the three horizontal dots(…). Then, select ‘Add filter and select the field from the table you want to filter by.
  • Now, edit the DAX expression with the logic you want to filter the field by (e.g., here we have ‘state = Kerala’).
  • Tick mark on the ‘Verify DAX Expression’ section, then select save.

To check the output and validate further:

  • From the tabs above, select ‘Modeling’ and then Select ‘View As’. In this section, select the role set up by you, and then, Select ‘Ok’. Verify the filtering process.
  • Now, Save and Publish the Power BI Report to a workspace.
  • Open the Power BI Workspace.
  • In the dataset section, select the three horizontal dots(…) then select (‘More options’), and then Select ‘Security’.
  • Add the users to the roles you established and Save.
  • Select the three horizontal dots(…) next to the security group (‘More options’) and Select ‘Test as role’ to ensure that the RLS is working as expected.

In Dynamic:

Directions:

To set this up follow the instructions down below:

  • Open the Power BI report and within this, select the dimension table.
  • Create a relationship between the fact table and the dimension table, with the logic of the dimension table filtering the fact table.
  • After this, Select ‘Modeling’ from the tabs on the top and then opt for ‘Manage Roles’.
  • Select ‘Create’ and specify a name to identify your new role.
  • Under the ‘Tables’ section, select the three horizontal dots(…) and then select ‘Add filter’ 
  • Choose the field from the table you want to filter by.
  • Now, edit the DAX expression with the logic you want to filter the field by and then select the check mark to ‘Verify the DAX Expression’. Now, press ‘Save’.

To check the output and validate further:

  • From the tabs above, select ‘Modeling’ and then Select ‘View As’. In this section, select the role set up by you, and then, Select ‘Ok’. Verify the filtering process.
  • Save and Publish the Power BI Report to a workspace.
  • Now, open the Power BI Workspace.
  • In the dataset section, select the three horizontal dots(…) then select (‘More options’), and then Select ‘Security’.
  • Add the users to the roles you established and Save.
  • Select the three horizontal dots(…) next to the security group (‘More options’) and Select ‘Test as role’ to ensure that the RLS is working as expected.

I hope you got an overview of how Row-level security works! We at EClytics deal with Power BI Consulting and training both. Contact us to know more.

Thanks for reading!

Add comment

Table of Contents