In this blog post, I describe how you can use the Row-Level Security (RLS) for Power BI, also including Power BI Embedded.
The outline of using Row-Level Security (RLS) is the following.
- Create and manage the roles using Power BI Desktop
- Publish to Power BI service
- Manage the user for the previous roles
- Share to the other users
In this example, we assume the following simple database table and use RLS with “Country” column. For example, the employees in Japan region can see the only Japan data, and the US colleagues can see the only US (North America) data.
Before using Row Level Security (RLS) described here, keep in mind that you need Power BI Pro. (This Row Level Security is the Pro feature.)
Manage Role using Power BI Desktop
First you start to build your report using Power BI Desktop. (You select the data source from database, and create the report.)
After you’ve done, please click [Manage Roles] button in [Modeling] tab in Power BI Desktop.
This displays the following window.
In this window, you can create the new roles (multiple), and for each role you can specify the filtering condition called “rule”.
For example, the following each role (“ChinaRole”, “JapanRole”, “USRole”) represents the filtering condition using [Country] column in [Sales] table.
Later we set the Power BI users in each role, and the corresponding role is used for each users.
Of course, you can specify more advanced conditions using DAX expression. Especially, you can use the username() function with rules, and this kind of filtering is called “dynamic security”.
Note : If you use the dynamic security, you must use DirectQuery and enable the switch [Enable cross filtering in both directions for DirectQuery] in the options and settings window. (see the following screenshot)
Notice : When you’re using Power BI Desktop, username() returns a user in the format of DOMAIN\USER. On the other hand, when Power BI service, username() will return the User Principal Name (UPN) which looks similar to an email address.
Manage users using Power BI service
Before sharing this report for the other users, you publish the previous report to the Power BI service using your account. (As I wrote before, you need the Pro feature.)
Notice : After you publish, you should specify the database credential (database username and password) selecting the [DATASET SETTINGS] menu in Power BI service. (The credential is not published in Power BI service for security reasons.)
After publishing, you select […] in the dataset and select [SECURITY] menu in your Power BI service. (See the following screenshot)
The previous roles (which you’ve created in Power BI Desktop) are displayed in the window. You add users (who is in the same organization in Azure AD) into each roles.
If many users exist in your tenant (organization), you can also add the security group or distribution list to the role. (Sorry, but it seems that the Office 365 Group cannot be added now…)
Check how it works, and Sharing
Before sharing your dashboard to the other users, you can check how the user can see your report.
Select […] menu in the role name, and you can see and select the [Test as role] menu. (See the following screenshot.)
The report which is filtered by the selected role is displayed.
Please click the role name in the upper bar, input the user id (UPN) in the drop-down window, and push [Apply] button.
Then the role which the selected user is belonging is applied to the report.
In the following example, the user “Jeet Jagasia” is belonging to the US (North America) Role, and the report is filtered by this role.
Even if the user is belonging to multiple roles, the corresponding all data of these roles is displayed in the report. (For example, the following person is belonging to both “JapanRole” and “ChinaRole”.)
Notice : The owner (i.e, yourself) of this report is not filtered and always see the all data. As a result, if you select yourself in the previous test window, the data is not filtered.
After the test is done, you share the dashboard including this report to the other users. All the user can see the filtered report. (If the user is not belonging to any role, no data is displayed and the error displays.)
RLS in Power BI Embedded (your custom app)
As I described in the previous post “How to use Power BI Embedded via REST“, the Power BI Embedded doesn’t depend on the Azure AD account or windows account. You can use your favorite authentication (forms auth, basic auth, etc) in your app, and you can combine these account with Power BI RLS. That is, you can freely determine “which user uses what roles” in your app.
Let’s look at the example.
Beforehand, you must understand how the Power BI Embedded works and how the access token (which is used for embedding reports) is used. In this post I don’t explain about that, but you can see “How to use Power BI Embedded via REST” for the details.
After your app authenticate the user, your app should determine what role is needed, and use the following json string as the input of access token signature. As you can see, your app can include the proper “roles” and “username” in the claims of the access token.
{
"typ":"JWT",
"alg":"HS256"
}{
"wid":"{workspace id}",
"rid":"{report id}",
"wcn":"{workspace collection name}",
"iss":"PowerBISDK",
"ver":"0.2.0",
"aud":"https://analysis.windows.net/powerbi/api",
"nbf":{start time of token expiration},
"exp":{end time of token expiration},
"roles":"{role names (multiple) delimited by comma}",
"username":"{user name}"
}The following is the simple example of how to implement using PHP.
We just added the code in bold font to the original source code in “How to use Power BI Embedded via REST“. This app shows the “View Report !” button, and if you click this button, the embedded report is displayed, which is filtered by the roles of “JapanRole” and “ChinaRole”.
If your report is using dynamic security, the value of “username” is also used in the filtering.
<?php
// 1. power bi access key
$accesskey = "9BKsnTVkRP...";
// 2. construct input value
$token1 = "{" .
"\"typ\":\"JWT\"," .
"\"alg\":\"HS256\"" .
"}";
$token2 = "{" .
"\"wid\":\"a581de28-288a-43ce-bb43-0059a991a7ce\"," . // workspace id
"\"rid\":\"9559247b-ef11-4d14-b772-29f6b4d08c3f\"," . // report id
"\"wcn\":\"mywsc\"," . // workspace collection name
"\"iss\":\"PowerBISDK\"," .
"\"ver\":\"0.2.0\"," .
"\"aud\":\"https://analysis.windows.net/powerbi/api\"," .
"\"nbf\":" . date("U") . "," .
"\"exp\":" . date("U" , strtotime("+1 hour")) . "," .
<strong> "\"roles\":\"JapanRole,ChinaRole\"," . // role name
"\"username\":\"matsuzaki\"" . // user name
</strong> "}";
$inputval = rfc4648_base64_encode($token1) .
"." .
rfc4648_base64_encode($token2);
// 3. get encoded signature value
$hash = hash_hmac("sha256",
$inputval,
$accesskey,
true);
$sig = rfc4648_base64_encode($hash);
// 4. get apptoken
$apptoken = $inputval . "." . $sig;
// helper functions
function rfc4648_base64_encode($arg) {
$res = $arg;
$res = base64_encode($res);
$res = str_replace("/", "_", $res);
$res = str_replace("+", "-", $res);
$res = rtrim($res, "=");
return $res;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Test page</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<button id="btnView">View Report !</button>
<div id="divView">
<iframe id="ifrTile" width="100%" height="400"></iframe>
</div>
<script>
(function () {
document.getElementById('btnView').onclick = function() {
var iframe = document.getElementById('ifrTile');
iframe.src = 'https://embedded.powerbi.com/appTokenReportEmbed?reportId=9559247b-ef11-4d14-b772-29f6b4d08c3f';
iframe.onload = function() {
var msgJson = {
action: "loadReport",
accessToken: "<?=$apptoken?>",
height: 500,
width: 722
};
var msgTxt = JSON.stringify(msgJson);
iframe.contentWindow.postMessage(msgTxt, "*");
};
};
}());
</script>
</body>
The embedded report is displayed like the following. (The data is filtered by “JapanRole” and “ChinaRole”.)
If you’re using Power BI Embedded .NET SDK (which is built on the top of REST API), it makes this really easy.
Now, please download the .NET SDK sample in the github, and follow the article “Microsoft Azure : Get started with Power BI Embedded sample“, and change the app settings.
If you want to add roles and username in your token, please change the source code in Controllers/DashboardController.cs as follows.
public async Task<ActionResult> Report(string reportId)
{
using (var client = this.CreatePowerBIClient())
{
var reportsResponse = await client.Reports.GetReportsAsync(this.workspaceCollection, this.workspaceId);
var report = reportsResponse.Value.FirstOrDefault(r => r.Id == reportId);
//var embedToken = PowerBIToken.CreateReportEmbedToken(
// this.workspaceCollection,
// this.workspaceId,
// report.Id);
var embedToken = PowerBIToken.CreateReportEmbedToken(
this.workspaceCollection,
this.workspaceId,
report.Id,
"matsuzaki",
new string[] { "JapanRole", "ChinaRole" });
var viewModel = new ReportViewModel
{
Report = report,
AccessToken = embedToken.Generate(this.accessKey)
};
return View(viewModel);
}
}For the details about RLS with Power BI Embedded, see the official document “Microsoft Azure : Row level security with Power BI Embedded“.
Categories: Uncategorized
tsmatz 
Great article! Tumbs up and it really helped me out!
LikeLike
When i am passing $apptoken as token (that is generated using algorithm) then i am getting 403 error code on following URL and process stops.
URL: https://wabi-us-east-a-primary-redirect.analysis.windows.net/powerbi/globalservice/v201606/clusterdetails
However when i am passing $accesskey as token then my report is showing. but RLS is not integrated with that report and complete report (without RLS) is showing as output
LikeLike