How to apply dynamic Row level Security based on Username and location?

Question

This is related to Row-level security in Power BI. Here is a dummy table on which I want to implement this RLS.

The RLS formula I am using right now is -

[location] = LOOKUPVALUE([location],[login],USERPRINCIPALNAME()) 

My end requirement is to make this RLS work on the case where if a user logs in he should see the data if the location he is in and also should see other users who are in the same location.

But lookup is throwing an error:

A table of multiple values was supplied where a single value was expected

Also, a single user can have access to multiple location and a single location could have multiple users.

How I can achieve this level of Row-level security using either Lookup or using any other possible way?

Answer 1

Your requirement can also be done through this way, you need to work on both the Power BI desktop and app server to get this right.

In Desktop

1. Power BI Desktop allows you to create Roles.Click on Manage Roles and then click on Create New Role.
2. You would need to create Roles based on the Location here.
3. For Example Create a rule Called as Location A and click on the table and give the formula to filters on that table. For example, [Location] = "A".

So, you can create 'n' number of roles like this and then publish the dashboard.

App.PowerBI.Com

1. Now, Open app.powerbi.com and then go to the corresponding dataset and right click on it, select security - you will find the groups there.

2. On the Groups, enter the emailid's that should have access to it. You can add a person in multiple groups, that will allow them to see more than one group.

Testing

1. Desktop allows you to test it as well - you can click on view as roles, to see what the user in that group sees.

Also to remember, only people who have access to the dashboard will be able to see the data inside it. So, you will need to share it with them and as well as need to add them inside the groups. If you fail to do one of it, then they won't see anything on the dashboard.

Most efficient Method

This is a better method and I think this is the one you are asking for,

[login] = username() 

The function username() automatically gets the email address of the user that is logged in.

Create the above mentioned filter on the new role in the required table. This way, you would still be adding those 50000 users manually inside the roles under the security section of the corresponding dataset.

But If they all belong to a particular group in an organization, and your Office-365 team has configured your back-end in the right way, then you will only need to enter the group.

But most companies don't have this configuration on their back-end as it is very difficult to control the data security.

Change the Data Model

To do your requirement, you also need to change your data model.

You will need to have two tables initially.

The Value Table - You can have many columns like name and all in there, but it doesn't matter.

The Email Address Table For Location as your second table.

Now Merge the Locations from Value Table to Email Address Table and that will be your final Table. Use this for visualizations but please be very clear on DAX - as you may have duplicate rows and different values.

Now Create RLS on this Table. It should work.

As the name goes, Row Level Security is applied on each rows. So, the formula will be evaluated for each row individually. So, your only option is to have a flat table with all the values.

Answer 2

I answered a question very similar to this one on another page. I will copy it here because I added a bit for the lookup function. Embedded Power BI: Add Multiple Users in Row Level Security and assign different roles

If you have more than one data to filter against in a role, pass the list as a string path to RLS, parse it into a table, then return the row when it matches with a value in the column.

We do this if we are not actually passing username, but for cases like multiple sales offices, or making a view that compares data from multiple user accounts, or when a user belongs to different hierarchies in an organization, or basically any time you want to use multiple filters..

example input using sales ids

//Username() = "020104010|020104061|020104303|020104304"

//DAX
var userIds = Username()
VAR tbl=
    GENERATE (
        GENERATESERIES(1,PATHLENGTH(UserIds),1),
        ROW ( "Key", PATHITEM ( userIds, [value]))
    )

VAR valueList =
    SELECTCOLUMNS ( tbl, "Key", [Key] )

return  [sales_id_column] in valueList

If you need to add lookup functions, you can apply it when the ROW() statement is called.. it will look like this:

VAR tbl=
GENERATE (
    GENERATESERIES(1,PATHLENGTH(UserIds),1),
    VAR CurrentKey = [Value]
    RETURN
    ROW ( "Key", LOOKUPVALUE(Table[c1],Table[c2], PATHITEM ( userIds, CurrentKey )))
)

There is also a case when the table has a many to many relationship and cannot use multiple roles as identity. Or for this case specifically when you only want to use one role to filter many tables. In that case the username looks like this:

Username() = "Username:Bob|Location:A|Location:C"

The code will have an extra step to parse the inner path after you change the ":" to a "|". Then use a if conditional to check if the current table filters for this role. This approach supports a claims-based authorization where only one role needs to be created in RLS and everything can be done client-application side.

runtime is O(k(n log m)) where m is the number of claims you have applying RLS in the current table, n is the number of rows in the table, and k is the number of tables you are applying RLS on