Dynamic row-level security
The roles we’ve created so far have been static, which means that all users within a role will see the same data. If you have many rules that specify how you should secure your data, this approach may mean you have to create a number of roles as well as update the data model every time a new role should be introduced or an old one removed.
There is an alternative approach, called dynamic row-level security, which allows you to show different data to different users within the same role.
Note Dynamic Row-Level Security
Because dynamic row-level security can use a single role, this approach is preferable in large-scale implementations of Power BI where there are many users who need to see different data.
For this approach, your data model must contain the usernames of people who should have access to the relevant rows of data. You’ll also need to pass the active username as a filter condition. Power BI has two functions that allow you to get the username of the current user:
- USERNAME This function returns the domain and login of the user in the domain\login format.
- USERPRINCIPALNAME Depending on how the Active Directory was set up, this function usually returns the email address of the user.
Note Using USERNAME and USERPRINCIPALNAME
If your computer is not part of an Active Directory domain, both functions will return the same result—domain\login. Once you publish your dataset to the Power BI service, both functions will return the email address of the user.
These functions can only be used in measures or table filter DAX expressions; if you try to use either of them in a calculated column or a calculated table, you’ll get an error.
To see how dynamic row-level security works in our Wide World Importers data model, first create a new security role:
Select Manage roles on the Modeling ribbon.
Create a new security role and call it Dynamic RLS.
For the Dynamic RLS role, specify the following table filter DAX expression for the Employee table:
[Email] = USERPRINCIPALNAME()
Select Save.
Now you can test the new role:
Select View as on the Modeling ribbon.
Select both Other user and Dynamic RLS.
Enter [email protected] in the Other user box.
Select OK.
Go to the Data view.
Select the Employee table.
Note that the Employee table is now filtered to just Jack Potter’s row, as shown in Figure 2-12.
FIGURE 2-12 Employee table viewed as Jack Potter
Although this may be good enough for us in certain cases, it’s a common requirement for managers to see the data of those who report to them. Since Jack is a manager, he should be able to see data of the salespersons who report to him. For that, we can create a new role called Dynamic RLS (hierarchy) with the following table filter DAX expression:
Click here to view code image
PATHCONTAINS(
PATH(
Employee[Employee Key],
Employee[Parent Employee Key]
),
LOOKUPVALUE(
Employee[Employee Key],
Employee[Email],
USERPRINCIPALNAME()
)
)
This table filter DAX expression keeps those rows where Jack is part of the hierarchy path, which relies on the Employee table having both the ID and parent ID columns.
After you make this change, the Employee table will show four rows: Jack’s row and three rows of the salespersons who report to Jack, as seen in Figure 2-13.
FIGURE 2-13 Employee table viewed as Jack Potter
So far, you’ve created the roles in Power BI Desktop. Once you publish the report, you’ll have to assign users or security groups to roles in Power BI service separately. We review these skills in Chapter 4.
Need More Review? Row-Level Security
For more examples of implementing row-level security in Power BI, see “Row-level security (RLS) guidance in Power BI Desktop” at https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance.
