Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

ibarrau

Page level security with RLS

The first and most important thing we need to understand to get our expected result is forcing the user to use a custom navigation inside of the report. Let's avoid the default pages navigation. We might need to build a single visible Menu page to start our report. You can find a lot of post and videos talking about buttons approaches to build menu. I'll leave only one example, but you can google any other one. E.g https://blog.ladataweb.com.ar/post/187164247999/powerbi-bookmarks-episodio-3

The key feature here it's using Page Navigation for the buttons with the FX option. The FX will let us use a measure that will handle a logic to navigate depending on users roles.

A little of theory context. If we add a measure in the custom function (FX) in page navigation the engine will check if the result of the measure is a text string that matches with the name of a page in our report. The string must match exactly (key sensitive) otherwise the measure won't work.

Model

In a similar way on how dynamic RLS is handled, let's build a table that is not related with the model. The table can be managed by an excel stored in a drive or sharepoint. The table contains two columns. The user email from Azure AD and the exact name of the page they should see. You can add a row for each page a user can see.

ibarrau_0-1634664616603.png


As you can see it's not just about adding a person to each page. That's only for restrictions. We can have people that will see everything. The word "Todo" means "All".

After loading the table, we can create a simple RLS role:

ibarrau_1-1628708790843.png

 

DAX

Now that we have our model built, let's create the DAX measures for each page. Yes, you need a DAX measure for each page on the report. You are building a menu with buttons to navigate to a page, so it's a measure for each page to do it.

Let's see an example for the page "Error in Totals" in the report:

 

 

 

 

Nav_ErrorInTotals =
VAR __nav = OR(
    "Error in Totals" IN VALUES (UserByPage[PagePermission])
    , "Todo" IN VALUES (UserByPage[PagePermission])
)
RETURN
IF ( __nav, "Error in Totals", "Denegado")

 

 

 

 

We start with two conditions. If one of those is ok, then the text will return the exact string that matches the page. The first one is checking if the text "Error in Totals" is in the list of rows filtered by RLS. The second one is checking if it is an admin (remember we used a row with the page name = "Todo" for admins). The variable will return TRUE() if our table's data can find that page for the user in RLS. The RETURN with the IF to make it cleaner. The false statement in the if could be blank. The blank avoids the user navigation. The button won't do anything. However, we can improve the user experience creating a hidden page for all false statements like "Permission Denied" page or something like that (in my case the page name is Denegado). That way the user is not confused because the button will always navigate somewhere. We can even make it nicer giving information of which pages or buttons of the menu the user can access.

ibarrau_2-1628708821251.png

In order to make the navigation work we need to open de action menu in the format pane of the button.

ibarrau_3-1628708845493.png

Add the measure as the FX destination and boom! we are done. Let's see how this would work with RLS:

ibarrau_4-1628708863763.gif

Now we have built an approach restricting pages for a logged user with RLS with an external table that can be managed by business users instead of a Power Bi developer. You are also maintaining only one report instead of different copies.

I hope this helps you open your mind to find more and amazing creative solutions like this one that could be improved a lot if we hide and show buttons regarding permissions or anything you want.

Original post from ladataweb.

 

Comments
Anonymous

I was about to suggest hiding buttons that don't go anywhere, but you already mention it at the end! Good job! Although if they analyze in excel they still see all data right? Unless there's some real RLS that takes care that even if they could get into those hidden pages no data would be visible

That's a very interesting point @Anonymous . Sadly I think analyze in excel will show all data. Before implementing this it might be a good idea to know your audience because I'm not sure that you can remove the option "Analyze in excel" from Service. Unless you have other rules for data too besides the ones for the pages. They can be combine with regular RLS to keep the dataset for the people filtered.

I like the way of creating a page navigation through RLS but it's not really security, it's hiding. Which means if I would know the ID of the page I still could access it direclty. This could happen if someone shares the direct link (copy & paste from browser url) with me or am I wrong in this case?

Hi @FabricGuy , yes. You are right. That's why I introduce this as "an approach" because it can't be done completely. You can share the url of a hidden page to a user and they will be able to see that. In my opinion that's an issue of PBI. You shouldn't be able to share hidden pages in regular scenarios, becuase it's hidden for a reason 😛

However this is the most close way to get there until pbi team launch a new feature 🙂

Hey @IB Agree 🙂 Nevertheless it's a nice solution!

Hello @ibarrau ,

was really a nice solution. I was searching for some kind of solution like this and it helped me 🙂

 

How to restrict if some one using URL instead of page navigation?

@RockPowerBI this is an approach. PowerBi hasn't added page level security as a feature. If a user is trying to change URL to get more information of a hidden page, they might found it, unless you build an additional security to make the hidden page blank or empty if the user is not allowed, then they can find it.

This is the most close way to get there until pbi team launch a new feature.

How to build this ?

"unless you build an additional security to make the hidden page blank or empty if the user is not allowed, then they can find it."

 

Can help me with any Sample if you have pbix.