Forum Discussion

xabikos's avatar
xabikos
Frequent Visitor
9 years ago

Power Bi embedded schema based multi tenancy

We are currently evaluating the Power BI embedded platform in the context of a cloud based solution we offered to our clients. Our software is multi-tenant and to achieve this one we use database...
lukaszp's avatar
lukaszp
Power BI Team
9 years ago
I think the use case for RLS and Schemas are very different. Schemas as far as I know (please correct me and point me to the documentation) don't enforce row level security. Meaning, any user with access to a specific schema sees the same data as other users with access to the schema. RLS solves the next problem - within a given table, showing just the rows a user has permission to see to that user. SQL Server does not let you control the schema in the connection string (as far as I know the default schema is tied to the logged in user identity). If your queries in the report are written to avoid selecting a specific schema (e.g. don't use dbo.xxxx) you should be able to use the user account to ensure the connection to SQL Server cannot access other parts of your database. I've not tried this, but if you publish to reports in two workspaces, and point them both to the same sql server with different sql users (who have appropriate schema defined) it would probably work. If you're isolating content based on schema, then the reports you build will only contain entities from an individual schema. So I'm actually not sure what using a default schema in this way buys you. You can achieve the same with just having sql user account with only permission to specific schemas. From the user perspective you can give the user a token granting access to a specific report. If the report is built using a specific schema, then it will never issue queries to another schema. Any additional details on your scenario would be appreciated.
TvB's avatar
TvB
New Member
9 years ago

Hi lukaszp, thanks for your reply. The way you describe works indeed, but only when you use written out SQL statements in the data sources without typing the schema name. When a table or view is selected in the data source wizard, its schema name is also included in the data source. When I remove the schema from the source in the advanced editor and I save the report as a pbix file, the schema name seems to remain applied. I notice this in two ways: 

When I publish the pbix file in a workspace with a sql user which has access to the specific schema (same tenant), it works. When I publish the pbix file in a wokspace with a sql user which has access to a different schema (another tenant), it doesn't.

The other way is when I open the pbix file in a text editor, I can see the schema name in a number of lines (between lots of unreadable characters).

 

RLS has nothing to do with this, the question is how can I design a schema independent report on a database with schema based tenant isolation, without using written out SQL statements?

 

  • lukaszp's avatar
    lukaszp
    Power BI Team
    9 years ago
    Thanks for clarifying. Let me see if I can track down an answer.
    • Chandrachud's avatar
      Chandrachud
      Regular Visitor
      9 years ago

      Hi All, We also are facing the same issue, as rightly said by TvB changing user credentials, removing schema definition from advanced query editor nothing works as of now.

       

      Just a suggestion if this works for you (please check the secuity aspect) you can use sql level filtering on the basis of some parameters while designing the report to see data relevant to a tenant and then disable the filters and visualization options from JavaScript while rendering the embedded report.

       

       

      • lukaszp's avatar
        lukaszp
        Power BI Team
        9 years ago
        I checked up on this - we don't support changing the schema the way you're trying to use it. You should use row level security. Please not that row level security allows you to set security filters in the App Token generated by your application. The application should create tokens in the backend (not in the JavaScript so that your keys stay secure). You should not use JavaScript filtering API as a security primitive - since it's client side someone could edit the filters supplied inflight to the server. The information stored in the App Token (username, collection of roles) is signed by your app's key so it cannot be tampered. You can read about App Tokens here: https://docs.microsoft.com/en-us/azure/power-bi-embedded/power-bi-embedded-app-token-flow