Row Level Security in Power BI

by Super User on ‎04-25-2016 12:13 PM

 

In PowerBI.com under Datasets in Left side pane click three dots for your dataset and then click SECURITY.

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

On Row-Level Security page click Create New Role.

 

2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Continue on warning sign that says if you re-publish this dataset you have to create row level security again.

 

3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Create roles as per requirements. In this case I have dataset that has some sales figures for different states across Australia so I am going to create roles for each of those states thereby limiting users to only see the sales data for their state.

 

4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next step is to add users to those roles created above. Select role on left and under Members tab add users by typing in their email address and click Save. Please note you can only add users who have signed up to PowerBI.com before. If you try add users who have not signed up when you save it will give error.

 

5.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Under Rules tab select table from your dataset and use DAX query to limit access to the rows in that table. Here I am limiting to only those rows in table Sheet1 where State column has value Western Australia. For this post I am keeping it simple but as you can see you can write complex DAX queries to achieve level of security you want.

 

 

Sheet1[State]="Western Australia"

 

 

6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Once saved next time users of that role access the dashboard they will see the same report with filtered data. Below are the screenshot of the original dashboard and one seen by the user of Western Australia Users role. You may need to download these images and zoom in to be able to see difference in number values for each visuals.

 

7.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

user.png

Comments
by Anand Occasional Visitor
on ‎04-26-2016 09:54 AM

Hope something can be done for the desktop version as well

by wynhopkins Member
on ‎04-26-2016 07:53 PM

Hi Ankit

 

How did you handle checking that the filters had worked, did you have to log in as a particular user?

 

 

by Super User
‎04-26-2016 08:21 PM - edited ‎04-26-2016 08:22 PM

Hi Wyn, I had this dashboard shared with internal user. So after implementing RLS, I just asked him to access the dashboard.

by wynhopkins Member
‎04-26-2016 09:20 PM - edited ‎04-26-2016 09:24 PM

Ah, OK.  I've flagged this as request

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13260438-preview-impact-of-row-le...

 

Please vote!  Smiley Happy

 

 

On another note, I'm actually in discussions with the PowerBI support guys as my Row Level security is causing me isues.  Applying a rule to one user causes all other user's dashboards to blank out.  Very odd.

by ashishrj Senior Member
on ‎04-26-2016 10:11 PM

Hello Folks, you can also check our webinar conducted on RLS in Power BI. Link provided below:

by Super User
‎04-26-2016 10:14 PM - edited ‎04-26-2016 10:18 PM

 @wynhopkins Sure will do (voting).

 

I think it is expected behaviour as when you implement RLS you're saying you want to control access to your dataset. So then when you apply RLS for that user his access is controlled but you haven't setup anything for other users and so they all see blank.

 

I think by design, if you don't have RLS implemented everyone can see, when you implement RLS then you have to manage access for each users you want report to see. And it make sense otherwise it introduces ambiguity where one user has controlled access while others (that you may have forgotten to include in a role) can see everthing.

 

I've submiited this idea along the lines of above to have better design. Smiley Happy

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13573167-row-level-security-and-p...

 

 

 

by wynhopkins Member
on ‎04-27-2016 04:24 AM

Hi Ankit

 

That is indeed what is happening.  I can see the logic, just didn't realise it worked that way. 

If you wanted someone to see an unrestricted data set would you create a rule called "All States" and use something like [State] <> ""

 

 

 

by vidotom Regular Visitor
‎04-27-2016 06:50 AM - edited ‎04-27-2016 07:03 AM

It is a most welcome feature!

 

However it raises the question too: REST API. It would make an awful lot of sense to automate the management of the roles. E.g. in an enterprise grade business solutions users come and go, and they are usually already managed in roles somewhere. Synchronizing the custom roles of the business application with the roles for the datasets in Power BI is an extra management task. Which demands automation, otherwise it will be a nightmare to manage the roles of users in yet another system manually. So much that I opened a new idea for it here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13616925-rest-api-for-managing-ro...

 

Please vote for it if you find that missing feature useful.

by fredrikg Frequent Visitor
on ‎04-28-2016 04:05 AM

This is better than nothing, but I am waiting for when the current user's identity can be used in the DAX-expression, like using the EffectiveUsername in SSAS.

That way, we could model our own roles/security and not having to create "static" roles in Power BI.

 

For instance, the example you give with the nurse having access to a patient's records isn't really doable at this time - that would require maintaining individual Power BI roles for each patient.

 

 

by vidotom Regular Visitor
on ‎04-28-2016 04:31 AM

Indeed, this solution does not replace the necessity of having user context accessible in DAX. That would help tremendously for lots of cases in my business too, however I understand that it is not implemented by design. It is a pitty.

 

But having these static groups is a great help when you need to deal with e.g. organizational units, where you would like to allow the users to access data in the reports that concerns them, and nothing else. It's just a pain to manage it, as they are static at the moment. If there would be a Rest API to manage the roles, then you could implement the per patient scenario too: using a tonne of individual groups. Which is impossible to maintain mannually, but with the currently missing Rest API it would be manageable. Awkward, but feasible.

by fredrikg Frequent Visitor
on ‎04-28-2016 04:37 AM

Definitely awkward :-)

Would be interesting to know if user context in the DAX is something which is being worked on. If so, I should wait for that, instead of spending time on static groups.

 

Anyone knowing anything about that?

by vidotom Regular Visitor
on ‎04-28-2016 04:46 AM

I join the question: is there any news of making the logged in user information accessible to DAX? That would allow user context based queries. Which is important not only for security reasons, but for user experience in general too!

by Super User
on ‎04-28-2016 08:19 AM

@fredrikg @vidotom I was pointing to the fact that if large number of patients are classified into a group let's say 'Clinic ABC Patient's' role and then nurse as users are added to thsoe roles then you can have that nurse only accessing data for his/her patients. You don't necessarily need to create individual roles for each patient as often in real world scenario you will find single nurse looking after many different patients.

 

But agree user context accessible would also be very useful. Smiley Happy 

 

 

by vidotom Regular Visitor
on ‎04-28-2016 08:23 AM

@ankitpatira Agreed. For the nurse vs. patient type of data model this grouping could work just fine. Just managing it is a challenge without an API to use for automation.

by rl_evans Frequent Visitor
on ‎05-04-2016 12:00 PM

This solution has very limited usefulness as it is not scalable.  Attempting to manage users in roles in yet another non-centralized repository quickly becomes a nightmare.  BTW - This is not row level security, this is role level security.  Row level security is achieved when data is filtered based on the attributes of the user, not the attributes of a role.

by Super User
‎05-16-2016 02:43 PM - edited ‎05-16-2016 02:43 PM
by shj997 Frequent Visitor
on ‎06-07-2016 11:55 AM

How do you apply more than one rule for a user?  For instance

 

AND(AND([Variable 1] = "XXXX", [Variable 2] = "XXXX"), [Varible 3] = "XXXX") 

 

does not work but 

 

AND([Variable 1] = "XXXX", [Variable 2] = "XXXX")

 

does work.  

 

I have 5 rules I want to implement for a specific user so how can this be done?  Many thanks.  - Steve

by jcox Frequent Visitor
‎06-16-2016 08:38 AM - edited ‎06-16-2016 08:39 AM

@shj997 I don't know if you've already gotten an answer but to make multiple rules with a DAX statement in RLS you need to place '&&' in between the rules. So for example: 

 

[Region] = LOOKUPVALUE(User[Region], [User], USERNAME())&&

[SSC] = LOOKUPVALUE(User[SSC],[User], USERNAME())

 

This works with the dataset that I have loaded with RLS. Let me know if that answers your question. 

by fredrikg Frequent Visitor
on ‎06-20-2016 02:06 AM

@jcox Bear with me, I am beginner in DAX: your example looks fine, as long as a user only has permissions to a single Region and SCC, right? How could this be done if a user could have permissions to multiple Regions and/or SSC:s?

 

/Fredrik

by sabhishek Frequent Visitor
on ‎08-05-2016 03:02 AM

@ankitpatira You have mentioned in one of your answers(ref link below) that with May release we can assign email distro list/ AD groups to the roles. However when I am trying to add the distro list email address, it is not recognising it.

 

https://community.powerbi.com/t5/Service/Dynamic-RLS-based-on-User-Organisational-Hierarchy/td-p/360...

 

Is it because except few, all the other group memebers doesn't have Power BI account?

 

Can you please help me with this?

by JonathanA Visitor
on ‎08-18-2016 07:36 AM

The Security option is mising in the menu when clicking the elipsis.  Any idea why this woudl be?

by Super User
on ‎08-18-2016 07:37 AM

@JonathanA RLS is now in the Desktop only. It was removed from the Service.

by JonathanA Visitor
on ‎08-19-2016 12:06 PM

Are their plans to make RLS available again for the PowerBI service? Is it in Preview for some tenants or has it been pulled completely?  This is very important feature for many customers especially since many Content Packs cannot be imported into PBI Desktop.

by Super User
on ‎08-19-2016 12:10 PM

@JonathanA Ideally, you should be creating your reports in the Desktop for a number of reasons. It's been pulled from the Service, and you need to build the reports in the Desktop in order to utililze it. Content Packs can still be leveraged, you just start in the Desktop rather than building in the Service.

by Shubham Member
on ‎09-01-2016 01:52 AM

@ankitpatira Can you please tell us more about AD accounts specific data which should be secured by Login.

Suppose - I have a table which is having 70 rows belongs to me only, this tables has one column which is AD account value but not domain value like "sjain" only but my login id is domain\sjain

So would i need this "domain\" as well in my table or it will authenticate automatically after creating and assigning roles.

Username() is giving domain\sjain

 

by javed_khan Frequent Visitor
on ‎03-15-2017 04:45 AM

@ankitpatira I have create a row level security and given permission to the user and its working fine.

now i have create a group workspace and given that same user view only permission in that case also its working find. but when i give group workspace view edit permission for the same use then row level security is not working.

basically i want to setup row level security and give use permission to edit the report and can create adhoc reporting on the top of same security.

 

Thanks,

by javed_khan Frequent Visitor
on ‎03-15-2017 04:50 AM

@ankitpatira I have created a row level security and given permission to the user and its working fine.

now i have created a group workspace and given that same user view only permission in that case also its working fine. but when i give group workspace view edit permission for the same user then row level security is not working.

basically i want to setup a row level security and give user permission to edit the report and can do adhoc reporting on the top of same security.

 

by jatneerjat Member
on ‎06-18-2018 06:52 AM

Hi @wynhopkins @ankitpatira,

 

Could you please help me in below.

 

I have a report in which i have created a role as "UserEmail = userprincipalname()"  and added a security group in which there are multiple users so any user logged in will see only his data.

Now  there is another admin group which can see all data so how can I create the role in same report so that users from one group can see only info related to them and other groups(admin) user can see all info on the report.

 

Currently whenever a user who have not added to any role logged into power bi service(Already have access to the report) then the report appeared as below.Why the user not able to see the visuals?

 

13.PNG

 

When I click on 'See details'

 

14.PNG

 

Thanks,

Jat

 

 

by wynhopkins Member
on ‎06-18-2018 05:15 PM

Hi @jatneerjat,

 

You would need to set up another role in PBI Desktop wihtout any filters applied and assign the new admin group to that role

by jatneerjat Member
on ‎06-18-2018 10:02 PM

Hi   @wynhopkins,

 

I tried creating the role as [ID] <> "" and added admin group into this role but then also user from admin role not able to view the data on report.

So how to create a role so that user from admin group can see all the data in report.

 

Thanks,

Jat

 

by wynhopkins Member
on ‎06-18-2018 10:22 PM

Hi Jat,

 

Just create a Role without any filters applied at all 

by jatneerjat Member
on ‎06-18-2018 10:29 PM

Thanks alot @wynhopkins. it works.

 

Regards,

Jat