cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NeehaRathnaJ
Frequent Visitor

Dynamic RLS using Rest API

Hi All,
I want to achieve the following scenario: 
Assigning Users to Roles for performing Dynamic Row Level Security on Datasets using API.

 

Now, I have found out in my research is that this can be done in the following ways:

  1. By creating Security/ Mail-enabled Security groups on the Admin Portal of Microsoft 365 and adding programmatically to that group. This doesn't help me as I want an API provided by Power BI to do the task. Admin portal access is limited in my organization and hence I need another way/
  2. By using Power BI Embed APIs to assign roles using JavaScript through EmbedToken and EffectiveIdentity. According to the explanation I have seen on the web, I have understood (or maybe misunderstood) it to be happening on a one-time basis. As for that particular session of API call, the user would be able to view the report with the role assigned as per the API Call and not permanently.

I would like to know if there is an API in the API Catalog of Power BI RESTful APIs to fulfill my requirement on a permanent basis. I searched through the Dataset section of the official documentation but couldn't find any RLS-specific API.  There are APIs for giving Users access to the workspace or as a group. Can anyone please help me out here?

 

The following links were referred to along the process:

1 ACCEPTED SOLUTION

Yes. You only have approaches but not exacly the API request as you wanted. We should stay tuned on the updates becuase the idea says "complete". It might be released soon.

Regards


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

7 REPLIES 7
apuente93
Regular Visitor

Hi Neeha,

 

We are actually looking for something similar to what you mentioned in point #2 and don't mind that that the role filter isn't permanent. For our use case, we would like to have 1 workspace 1 report and 1 dataset. For each user, we would like to restrict data in report based on their needs dynamically. So for example, we have Amazon, Apple and Google users. Each user can access the same report, but with different data. Sometimes, we would like to restrict Amazon from certain fields they originally are assigned, and sometimes we would like to give them access to more fields in the data. We want to be able to do this programatically, without having to do any manual work through Power BI Desktop. Could you provide API calls / resources to accomplish this? If I understand correctly, what we need is very similar to #2. We don't mind that the role isn't permament, we only care about storing the roles /filters we use for future use or auditing purposes. Any help is greatly appreciated! Thank you!

ibarrau
Super User
Super User

Hi. There is no such endpoint. The Power Bi Rest API doesn't have a request to control RLS.

Just for the heads up, this is an approach on how to do dynamic RLS: https://blog.ladataweb.com.ar/post/652895563673649152/powerbi-seguridad-de-filas-rls-con-regla

You need to maintain a table, it can be an excel, database, etc. That table might have the way to change with code. I'm showing that to brainstorm you.

The only similar way it's the one you are talking about. If you build your web app and control your own custom login, then you can decide in the app which RLS role to use for each user. The thing is, you still need a table to tell you that. Then the solution is close to the first one again.

I hope that make sense.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hi, thanks for the response and your suggestions.

The main transaction table has a column (with User IDs) for applying the filter logic while defining roles. And if the ID of the logged-in user matches it then, that specific data is visible to the logged-in user. I am able to achieve that step.

My major concern is if I "build a web app and control my own custom login, then I can decide in the app which RLS role to use for each user"(as you mentioned), there is the API called by passing an Effective Identity. Is it going to permanently add the user to that role? By that I mean, if the same user logs into Power BI service, that user will be able to see the data as per the assigned role? And afterward also? 

An idea regarding the same thing is mentioned on this Microsoft Idea page and there is an admin reply to it saying it was done too. Is it the same Effective Identity method ?

I'm surprised about the idea in "Complete" state because it's not there in the API doc. You can check the comments below they are asking for it. Maybe it will be released soon.

Regarding your questions. When you build a custom app with custom login, users are control by you. That means they can use any account to login (of course it should help your rls table). I mean they don't need a Power Bi license, they are using a custom app and not Power Bi Service. In case they have access and get in Power Bi Service they should see anything because you haven't shared anything with them. Don't worry about that scenario.

If you can't develop the app there are already companies with apps like that. The company I work for has one 😛
I hope that helps,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Exactly me too. And it was completed in March 2022. Yeah, I checked that one of the users pretty much recently commented and asked about it. I thought maybe the person who commented just couldn't find the API 😂

Yeah, I do know that the users accessing the App don't need a Power BI license or an account of Power BI. I wanted to check for such a user who also has access to the dashboard on the service but hasn't been assigned the role yet. So that, I can test whether this API call causes a permanent update or is it on the fly happening for that API call only.

Thanks for the offer😀 The company I work for also develops them 😬

Ok then, to conclude we both agree that there hasn't been any such API provided by the Power BI REST APIs catalog that fulfills this requirement (as of 28th April 2022). Right? 

Yes. You only have approaches but not exacly the API request as you wanted. We should stay tuned on the updates becuase the idea says "complete". It might be released soon.

Regards


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Yup. Right. Thanks for confirming and the help!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors