Dynamic Row Level Security

[ccarpent]

Hi

I am looking for some advise on how to best address RLS on hierarchy or organisation, the key focus or relationship across my tables is based on a persons full name; First & Last Name.  So I managed to setup simple form of RLS based around this criteria, I have a manager who then has staff.  I used the example from RAZRAD; which uses 'PATHCONTAINS()' and is based on using 'UserPrincipalName()', and this works fine.  E.g. if 'John' logon he can see his records plus anyone managed by him and so on, and likewise if 'Sharon' logins in, she can only see her projects.  But if I need the director to login, or someone who needs to view all the data, they do not see any projects as they are not directly related to any projects in any tables, even though they manage the whole organisation, there is no key relationship with their details on my table(s).

1. Now, do I need to rethink my model, the users are linked to records by their names, not an ID. Not everyone who wants to see the data is actually on the table, projects are not separted by department they are seprated or identified by the staff working on that project(s), and ideally there needs to be some form of cross-over.

2. Or, do I create a seperate report, one that has no RLS in the model and is viewable by those staff I permit or grant access to see it, or am I creating extra and duplicated work?

Many thanks

Chris Carpenter

Finance & Contracts Information Analyst

[RobbeVL · Accepted solution]

Hi Chris,

If giving "global" access to some specific users, the easiest way is to just add a new role within Desktop.

Create a new group and do not set any filter on the tables.

the next step will be to assignt your directors (or Director AD group) to that group in PBI service. 

[Anonymous]

Hi All,

I am having security table SECURITY_DYN_AP and it contains GlobalID(userid) , profile, level1,level2,level3,level4,level5,level6,level7,sourcesystemregion,customername and globalcustomer

Currently we have applied row level security for GlobalID column in SECURITY_DYN_AP table by checking against loggedinuser using USERPRINCIPALNAME(). However I am also having transaction table CV_POWERBI_DAILY_SALES_VS_PLAN with columns Level1 to Level7 and the measures where in I need to filter out the data based on the LEVEL1 to Level7 values in the SECURITY_DYN_AP table. Also there is intermediary table called KY in which we have the concatenated value of Sourcesystemregion+level1 to level7+ Customer Name +globalcustomer. The transaction table is linked to KY table on KY column and this KY table is linked to SECURITY_DYN_AP table on KY column 

Please help me how can i achieve the filtering of transaction table data using row level security based on the values for LEvel 1 to Level7

Regards

Arun

[ccarpent]

Hi Arun

Are you able to post a sample data?

The key I found that worked for me was to create the relationship bwteen the tables, so in my example I had to use staff names as this was the key field to match who should look at what, which I ten mathced to a username in a separte table for example using he USERNAME().  If that person logged in then they would only seeany data with their name assigned to it, but it also allowed a manger to view all his staff as I created a hierarchy list. 

Have you seen these example? these are what I used to get my scenario to work.

https://radacad.com/row-level-security-configuration-in-power-bi-desktop - these are the examples I used and I even bought Kindle book he sells. And alos take a look at this - 

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl...

Two more links, I have not tried these but they may be of use:

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies?h...

https://blog.crossjoin.co.uk/2013/05/10/userelationship-and-tabular-row-security/

chris

[RobbeVL · Accepted solution]

Hi Chris,

If giving "global" access to some specific users, the easiest way is to just add a new role within Desktop.

Create a new group and do not set any filter on the tables.

the next step will be to assignt your directors (or Director AD group) to that group in PBI service. 

[ccarpent]

RobbeVL

Fantastic, so simple and it works a treat, thanks again.

Also, any thoughts on the significance of the 'Access' option when in PowerBI Service area?  Screen print below, I am the admin and on this occasion I just added a random user.  I does not seem to have any impact when I add a user or not, it deos not seem to take in to accouinty any setting I push through when using RLS.

Chris

Chris

[RobbeVL · Accepted solution]

Hi Chris,

These 2 settings are seperate things.

You can add anyone to the RLS rules without impacting the actual rights to the report/workspace.

But giving access to a report to users that dont have rights according to the RLS rules, will just see a blank report. 

Robbe