What is Row Level Security in Power BI?

In Power BI, row-level security (RLS) can be utilized to limit data access for certain users. One can set filters within roles to limit access to data at the row level. Members of a workspace have accessibility to datasets in the workspace in the Power BI service. This data is not restricted by RLS.
RLS can be configured for data models loaded into Power BI Desktop by users. It can also be configured on datasets that use DirectQuery, such as SQL Server.
 

Why Row-level security is important?

Row-Level Security (RLS) is significant because it makes security in your application easier to design and code. RLS aids in the implementation of data row access limits.
 

What is Dynamic row-level security in Power BI?

One of the most effective and practical methods to restrict data views throughout an organization is by using dynamic row-level security (DRLS). DRLS filters the dataset via DAX functions depending on the Power BI service user’s log-in credentials.
 

Pre-requisite for Row-Level Security?

To create Row-Level Security, we must execute each of the following steps one at a time :-
1. Make a list of users who will have access to the information. According to their login context, these users will be permitted access to some of the data.
2. Develop an integrated table-valued function in SQL as the following step. The filter criteria for the table on which RLS is to be deployed will be stored in this function.
3. Establish a security policy for the table and apply the above integrated table-valued function to it as the final stage.
 

How to Implement Row-Level Security in Power BI Desktop

 

• Create a DirectQuery connection or upload the data into your Power BI Desktop report.
• Go to the Modeling tab at the top and choose Manage roles to create row-level security.
• Select Create from the Manage roles dialogue box that displays.
• After clicking on create, a list of tables from the data model will appear, from which you may choose a filter to implement row-level security.
• You’ll notice that a new sample DAX expression will be added.
• Substitute the values in the DAX expression.
• Finally, make a role for a manager who wishes to keep an eye on the statistics.

Within the Power BI Desktop, you can set roles and rules. When you publish to Power BI, the role definitions are also published. It’s a key part of Power BI’s Row Level Security. Follow these procedures to define security roles:
 
Note: For Analysis Services live connections, you can’t define roles in Power BI Desktop. This must be done using the Analysis Services model.
 

Validate the Roles within Power BI Desktop

 
After you’ve developed your roles, run them using Power BI Desktop to see how they perform.
1. Choose View as Roles from the drop-down menu.
2. Choose a role you created and click OK to apply it. The data is rendered appropriate for that role in the report.
3. You can alternatively choose another user and provide a specific user. Because the Power BI service and Power BI Report Server use the User Principal Name(UPN), it’s best to provide it.
4. Click OK, and the report is generated depending on the user’s view.
Other users typically show different results in Power BI Desktop if you’re utilizing dynamic security based upon your DAX expressions.
 

What’s Good About Row-Level Security in Power BI Desktop?

Row-level security refers to the application of protection to individual data rows. For example, a developer wants to review the Sales & Population data for Country Finland should only access data for Finland, not from any other Country.
The reason for this was because Row Level Security was not included in the Power BI model. The security setting is now part of the model in the latest version of Power BI Desktop and will be delivered with the model.
 

Limitations Row-Level Security in Power BI Desktop?

• You must recreate norms and rules in Power BI Desktop if you already specified them in the Power BI service.
• RLS can only be applied to datasets prepared using Power BI Desktop. If you wish to use RLS with Excel datasets, you’ll need to convert them to Power BI Desktop (PBIX) files beforehand.
• ETL and DirectQuery connections are the only ones that work. The on-premises approach handles live connections to Analysis Services.
• At the moment, RLS does not support Cortana.
 

Implement Row-Level Security in Power BI Service

• Go to the report you just published and open it.
• After that, go to Datasets and click on the settings button for the report dataset you just published to start using RLS.
• From the drop-down option, choose Security.
• The Row-Level Security page appears, displaying all three roles established on the desktop.
• Select Test as Role from the right-click menu of a role’s options.
 

Testing Row-Level Security in the Power BI Service

• After heading to the workspace where your file was published, locate the dataset and pick Security from the options menu, as row-level security is controlled at the dataset level.
• On the following screen, you will see the role you established in Power BI Desktop, as well as a field to fill in your role membership, or the users or groups who are members of this role.
• When you drag the mouse over the role and obtain the ellipsis, you’ll see the option to test row-level security. When you click on this ellipsis, you’ll have the option to ‘test as role.
• This will lead you to the report page, wherein row-level security appears to be applied right away.
• You can change the user you are testing by selecting the menu at the top and entering a different email address.
 

Things to Remember Beforehand

• After a PBI file is published to the service, various factors such as workspace membership roles, data set permissions, and data set security role memberships can affect whether row-level security functions as intended. 
• To grant access to a viewer, return to the row-level security page, pick the role, and input the viewer’s email address in the Members field, and after that click Add and Save. 
• When generating role membership, the ideal practice is to simply utilize security groups; it’s a lot easier to manage than directly adding names.
• To see if this succeeded, go back to your report page and enter the name of the viewer you just introduced, and the page should sort to what the view can now see.
 

What is Object-Level Security?

Model authors can use object-level security (OLS) to hide individual tables or fields from report viewers. The table or column doesn’t really exist from the perspective of the viewer. You can limit access to sensitive object names as well as data with object-level security.
 

Advantages & Limitations of Object-level Security

Advantages:
The names and information of objects are also protected to prevent a hostile user from finding that they exist. This new layer of security stops people with insufficient access levels from accessing business-critical or sensitive personal data.
Using tools that support the XMLA API, you can create OLS rules in the Power BI dataset.
Limitations:

• If a model breaks a relationship chain, table-level security cannot be set for it. At design time, an error is produced. Table Y, for example, cannot be secured because of linkages between tables X and Y and Y and Z. A query on table X cannot traverse the linkages between tables X and Y, or Y and Z if table Y is guarded. In this situation, a distinct relationship between tables X and Z might be set up.
• It is not possible to mix row-level and object-level security from distinct roles since this could result in inadvertent access to secured data. Users who are members of this kind of combination of roles receive an error at query time.
• If a dynamic calculation refers to a secured table or column, it is subsequently restricted. While there is no way to expressly secure a measure, you can secure it implicitly by changing the phrase to refer to a protected database or column.
• Relationships referencing a secured column work as long as the table in which the column is found is not secured.

 

How do I Restrict Data Access in Power BI?

With Power BI Desktop, you can utilize row-level security (RLS) to limit data access for certain users. Filters limit data on a row-by-row basis. Within roles, you may define filters. With Power BI Desktop, you can now specify RLS for data models imported into Power BI.
 
Conclusion
Row-level security is among the most essential security features in both the desktop and cloud versions of Power BI, as we’ve seen above. Users from the Azure Active Directory can be mapped to the roles and given secured access to the reports in addition to the email addresses that we mapped the users to. It’s worth noting, though, that any person with permission to edit the report and dataset will also be able to see the underlying data for other roles.