Simplest RLS (Row Level Security) on Power BI embedded

Language: 🇯🇵 🇬🇧

HDE, Inc. provides multi-tenant cloud services for enterprise and all the tenants’ logs are gathered in one place. I have explored the possibility of providing the logs to each tenant by using Power BI embedded. This article introduces the implementation of a web application which displays only specified tenant’s log by using RLS (Row Level Security) of Power BI.

Things to prepare

The following will be required to create RLS on Power BI embedded.

• Azure subscriptions
• Power BI Pro license (Attach the license to a user who belongs to the same Azure Active Directory as the Azure subscriptions account)
• Python3 environment
• Clone https://github.com/takehikokodama/powerbi-embedded-sample
• Power BI Desktop

What is RLS (Row Level Security)

In the repository above, there is a sample PBIX file. This is created with Power BI Desktop which is a free desktop application for Windows, so please open and look into it. PBIX file generally consists of data, models, and reports. In this case, our PBIX file includes login information of a service with datetime and tenant name(domain) but trimmed other information such as user, IP address, user-agent and etc, which are unnecessary for this article. The PBIX file is only concerned about two tenants, which are “tokyo.example.com” and “osaka.example.com”. For reference purposes, I also put the raw data which is used in the PBXI file in the repo.

In the PBIX file, I created the simplest report, which is the count of login per hour.

The report contains all the tenant’s log, of course. RLS makes it possible to display only tenant A’s log for tenant A’s user and B’s log for B’s user and so on.

Users in “tokyo.example.com” will see the log below.

tokyo.example.com

And Users in “osaka.example.com” will see the log below.

osaka.example.com

To use Power BI RLS, “roles” are required. In this sample, the name of the role is “viewer”. With this role, users only see the row whose “domain” equals to “USERNAME()” which is injected from outside.

The web application introduced in this article implements RLS. Let’s look at the overview of the web application.

Web application overview

This is the flow of the web application.

1. Web Application gets access token for Power BI API from Azure AD.
2. Web Application sends roles and tenant information along with access token and gets Embed token from Power BI.
3. Web Application returns Embed token to a browser.
4. Browser (powerbi.js) sends embed token to Power BI and gets the report.

Next is the steps to create this environment including web application and Power BI embedded.

App registrations

App registrations are required for web applications to get access tokens to call Power BI API.

Go to Azure Portal and move on to “App registrations”. Fill out the “New application registration” form like so.

Why “Native” in “Application Type”? Never mind. It will be mentioned later part of this article.

And then, attach permissions for Azure AD.

And for Power BI.

Do not forget to click “Grant permission”

Copy “Application ID” for later use.

Create Power BI embedded

Create Power BI embedded from Azure Portal. At this time, specify Power BI Pro licensed user into “capacity administrator”.

Once Power BI embedded is created, access https://app.powerbi.com/ and create app workspace. Then specify Workspace name and select Power BI embedded which you have just created. You should also enable dedicated capacity. The workspace name will be required later so don’t forget it.

In the “Files” menu of this page, upload the sample PBIX file in the repo.

Execute web application on local

Go to the cloned repo and install application.

pip install .

Set environment variable like this.

export PBI_AUTHORITY="https://login.microsoftonline.com/common"
export PBI_RESOURCE="https://analysis.windows.net/powerbi/api"
export PBI_WORKSPACE_NAME="Power BI embedded sample"
export PBI_REPORT_NAME=powerbi-embedded-sample
export PBI_VIEW_ROLE=viewer
export PBI_USERNAME=${username}
export PBI_PASSWORD=${password}
export PBI_CLIENTID=${client_id}

“PBI_USERNAME” “PBI_PASSWORD” should be Power BI Pro licensed user’s login ID and password. “PBI_CLIENTID” is Application ID of what you registered into Azure AD.

“PBI_WORKSPACE_NAME” is the name of Power BI workspace and “PBI_REPORT_NAME” is file name of the PBXI file.

Then, execute the command below. And the web application is launched.

powerbi-embedded-sample

Access “http://localhost:8000/” and you will see the link of “tokyo.example.com” and “osaka.example.com”. Each link will guide you to each report created by Power BI with RLS. Any authentication is skipped in this sample.

Basically that’s it for quick introduction to Power BI embedded with RLS but there are things I can’t understand. As password is specified into environment variable of this web application and the application type is selected as “native”, the OAuth 2.0 grant flow is “password” instead of “client credentials”, which is generally used with this type of web application. Using password grant is not a good idea, as explained by Mr. Sakimura. There seems to be a problem between Power BI and Azure AD. While Power BI must be accessed with licensed user’s permission, there seems to be no way to get the user’s access token with client credentials grant flow on Azure AD. I hope either Azure AD or Power BI license system will be updated.