Objective
Using a single table to control all static and dynamic Row-Level Security (RLS) in Power BI has several advantages:
Centralized control: With a single table, you can centralize all your RLS rules and permissions in one place, which can make it easier to manage and maintain your data model. This can be particularly useful if you have a large number of users, groups, and data sets that need to be managed.
Improved efficiency: A single table can help to streamline the process of managing RLS permissions, as you only need to update a single table to make changes to the permissions. This can save time and reduce the risk of errors, particularly if you have a large number of rules or permissions to manage.
Simplified troubleshooting: With all your RLS rules and permissions in a single table, it can be easier to troubleshoot any issues or problems that may arise. You can use the table to quickly identify and fix any errors or inconsistencies in your RLS rules, which can help to ensure that your data model is accurate and up-to-date.
Overall, using a single table to control all static and dynamic RLS in Power BI can help to improve the efficiency, simplicity, and accuracy of your data model, and make it easier to manage and maintain your RLS rules and permissions.
We want to enhance our use of Power BI by consolidating all our Row-Level Security (RLS) requirements into a single role. Currently, we have to define static RLS and dynamic RLS separately, and we have to set up multiple roles for specific dimensions in static RLS. Additionally, we have different areas for each static RLS. To address this, we aim to combine all data into a single dataset and create a single role for all RLS to achieve the following goals:
1) Combine static and dynamic RLS, allowing users to access only their own data and the data they are allowed to see based on their role or group membership.
2) Create a static RLS for multiple dimensions, such as product and customer dimensions.
3) Create a single role for each dimension, such as multiple products.
This document will provide a solution that guides you through the process, from the database to the final reports.
- U1 supports dynamic RLS, meaning that U1 can only access his data to the Fact sales table.
- U1 supports static RLS for the product dimension, with two products P1 and P2
- U1 supports static RLS for the customer dimension, with one customer C1 only
- U2 doesn't support dynamic RLS, meaning that U2 can access all data. For example, he can access U1 data.
- U2 doesn't support static RLS for the product dimension, meaning that U2 can access all products.
- U2 supports static RLS for the customer dimension, with one customer C1 only.
CYUser
VAR _DimRLS =
CALCULATETABLE (
VALUES ( 'CYRLS'[IsDynamicRLS] ),
'CYRLS'[Email] = USERPRINCIPALNAME ()
)
VAR _RLS =
SWITCH (
TRUE (),
_DimRLS = "No", TRUE (),
[Email] = USERPRINCIPALNAME (), TRUE (),
FALSE ()
)
RETURN
_RLS
VAR _DimRLS =
CONCATENATEX (
CALCULATETABLE (
VALUES ( 'CYRLS'[ProductStaticRLS] ),
'CYRLS'[Email] = USERPRINCIPALNAME ()
),
'CYRLS'[ProductStaticRLS]
)
VAR _RLS =
SWITCH (
TRUE (),
CONTAINSSTRING ( _DimRLS, "None" ), TRUE (),
CONTAINSSTRING ( _DimRLS, [ID] ), TRUE (),
FALSE ()
)
RETURN
_RLS
CYCustomer
VAR _DimRLS =
CONCATENATEX (
CALCULATETABLE (
VALUES ( 'CYRLS'[CustomerStaticRLS] ),
'CYRLS'[Email] = USERPRINCIPALNAME ()
),
'CYRLS'[CustomerStaticRLS]
)
VAR _RLS =
SWITCH (
TRUE (),
CONTAINSSTRING ( _DimRLS, "None" ), TRUE (),
CONTAINSSTRING ( _DimRLS, [ID] ), TRUE (),
FALSE ()
)
RETURN
_RLS
Test result
No Security ( Admin )
U1: User 1 login
U2: User 2 login
Note 6: User group to setup permissions:
Dynamic security is a feature that allows you to change the level of access that users have to data in a Power BI report or dashboard at runtime. It allows you to specify which data a user can see based on their identity or role within an organization.
Note 8: Sample Implementation case of Row-Level Security (RLS)
Current Setup and Objective
Given that we have three different layers for Power BI implementation:
1. App Layer – Analytics
2. Dashboards/Reports Layer - SECURE_PROD
3. Semantic Model Layer - SECURE_Data
The Model is shared and located in a separate workspace (SECURE_Data). Dashboards/Reports are in a separate workspace (SECURE_PROD), where the shared Model is used. All dashboards/reports are organized in Power BI apps.
Ensure that when a specific employee logs in, they can only view their assignments for planned and actual data, while all other dashboards should not be impacted.
Implementing RLS can effectively resolve this visibility issue. The challenge lies in deciding whether to utilize a consolidated or separate model.
Separated Model Strategy
Advantages:
- Ease of Implementation: Implementing RLS on a separate model does not impact the central Model.
- Standardized Process: The implementation process is standardized, making it straightforward.
- Maintenance Simplicity: The separate model is easier to maintain due to its independence.
Disadvantages:
- Dual Maintenance Required: Any changes requiring updates in both models increase maintenance effort.
- Complex Separation: Defining a clear boundary between models is challenging due to many involved parameters.
- Dashboard Transition: Employees need to switch to the new model, potentially necessitating adjustments to the overview pages, which could become a considerable risk to implement.
Consolidated Model Strategy
Adding security to the data model will impact all dashboards, which is not desirable. The solution is to somehow enhance the current model by leveraging an “Inactive Relationship” for RLS, which should affect only dashboards that need to have RLS, and leave other dashboards unchanged.
Advantages:
- Single Model Management: Maintains a single model, avoiding duplicated efforts and simplifying management.
- Seamless Integration: All existing parameters and functions remain effective without needing alterations.
- Unified Dashboard Access: The employee dashboard points to the same model, ensuring consistency.
Disadvantages:
- Dashboard Adjustments Needed: Modifications to dashboard measures are required to activate the RLS features.
Implementation Details
1. The model includes an inactive relationship, which is currently used only for presentation purposes and is not operational in any DAX formulas. Also, `NetworkLoginName` is used for consistency.
2. The role for a specific AD group has been configured for data viewing permissions.
3. DAX solutions have been implemented. Note that the functions `USERELATIONSHIP` and `CROSSFILTER` are not yielding the expected results.
MeasureX (RLS) =
VAR Email =
SELECTEDVALUE ( Users[Email] )
RETURN
IF (
ISBLANK ( Email ),
[MeasureX],
CALCULATE (
[MeasureX],
'DimensionX'[Email] = Email
)
)
Deployment Details - Adding a Person or Group to RLS
On the Power BI app side ( at the audience level) .
On the dashboard/report side ( at the report level, not the workspace level, as you don't want people to access the report workspace)
On the semantic model ( at the model level, not the workspace level, as you don't want people to access the report workspace)
On the RLS security setting.
If a user has access to the dataset with Build, Share, or Reshare permissions but does not have Write access, you also need to add the user to the Security settings for the semantic model. However, if the user has Write access to the dataset, then it is not necessary to add them to the Security settings for the semantic model. Typically, we apply the first case.