How to use DYNAMIC ROW-LEVEL-SECURITY (RLS) in Power BI // Beginners Guide to Power BI in 2021

in this video i want to cover how you can set up dynamic row level security for your power bi report we're going to go through it step by step together with some simple examples so you can follow along as well all of that and more so without further ado let's get started hi my name is welcome to the solutions abroad youtube channel where i focus on teaching beginners the wonderful world that is power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so i got a comment in one of my videos asking me to cover rls again but specifically covering the difference between dynamic and static wireless which i didn't cover in my previous rls video if you haven't seen that check it out but to summarize it if you don't want to check it out this you create a role within the power bi desktop of your reports you publish it into the service and then you assign people in this row level security that you've created in the role in order to apply the rls the row-level security for your data it's a pretty simple implementation and it works but the problem is that this logic of who has access to what is embedded to this single report so it means that if you have to create multiple reports in the future you need to recreate these roles into those specific reports and then you have to reassign the people in those reports to apply the row level security to them and this in turn becomes very tedious especially if you have multiple reports with their own roles and different people assigned to them it can be a lot of work managing all of those and this type of implementation is called the static rls so pretty much what i did in the previous video what we need to implement is dynamic rls which is the ability to control permissions uh not individually in those reports but as part of your data set in order to set up a dynamic rls we need two things we need to find out who's logging in to the reports and we need to find a way to figure out and filter the data based on who is running the report so here's a very simple demo that i prepared today we have a user's data table which has some employee information it has annual salary data which we'll treat as a sensitive data we want to protect this data so that only the right people can see data for which user we have a regions table which just lists the region and we also have a permissions table so this permissions table solves our first problem which is you know saying who has access to which region and if we look at the model view here we just have a relationship between the region and the user data the idea is that whoever is logged in at the moment they should only be able to see the regions that are assigned to them based on the permissions table that we have here so you'll see in the permissions table here we have the names we have email addresses and the regions that they have access to so what does this mean so what this means is that uh for example if i log in as my email for another solutions abroad uk this tells me that i have access to eu and any data which means that if i log in to this report uh if i try to use it in the service i shouldn't be able to see apac in my in my data set no matter what now how do we figure out who's logged into the report itself well there's a handy function that we can use called the user principal name which determines who is logged in and using the report currently and this is what we'll use to essentially filter our reports and i've already created a measure that kind of emulates how this would look like here and if you look at the um formula bar here it's pretty much just the function here user principle name and to preview how that looks like i'm going to drag it in the card here so you can see a preview and you'll see it gives me who is logged in at the moment now if you're using report server or you're using local machine like i am now it's giving me the local username that i have now and by the way if you're using report server you should be using the username function however if you publish this report into the service obviously this will be your power bi service account which will be the email address so that's what we have here and don't worry if it's confusing at the moment we'll do some demo on how this would look like so from here what we need to do is we need to set up the filtering capabilities of this permissions table so we want to be able to filter the regions table which should filter the user data table because that's what we have set up here so what we'll need to do is we will need to create a relationship between the permissions table to the region and we'll say whatever is selected in the permissions table we want to filter to the region which in turn will filter the user data table now if you just drag the region um to the regions table here it will or rather it might show the flow of data the flow of filtering the arrow here just that you will use the region table to filter the permissions table we want actually for it to go the other way so to overcome this we'll double click on the relationship here you need to make sure that you cross filter to both directions this way the filtering can go towards the region table as well and it's very important you need to take this apply security filter in both directions so this means that if you filter or rather if you use the rls to filter the permissions table it should apply a security filter on on the tables that are related to it so hit okay here you see that that should change to like a double arrow and now i think we're ready to test from here so as before when you want to set up an rls for the first time we'll click here on the modeling manage rules and we'll create a new role here and bear in mind here in the previous version we have to create uh different roles let's say eu apac but in this case we'll just create one role we'll say permissions world and we want to filter the permissions table and we want to filter the email now we want to filter the email based on the user principle name so if you remember this is the function that we had before which will uh show us who's logged in at the moment so let's save this so let's hit the check box here to make sure everything is okay and then we'll hit save and now what we'll do i will just copy this um this value here my email and then let's try to emulate how this would work so let's hit view as we select these and i'm just pasting as if i'm logged in as fernan and okay so just to have a look at what's here so for fernan the email fernand we should not be able to see apac or or apac people in our user data table that is the main objective so we'll simulate the fact that i've logged in so if we hit ok here you'll see that it filtered us the data so as for nan if i log in and view this report in the service i should only be able to see what is given to me based on the permissions table and you see that i didn't have to create separate row level security for region eu or na and then assign myself to it all i had to do is check who's logged in and then use the permissions table to filter my data accordingly so let's stop viewing for now and let's publish this into the service so we can actually see this in action so let me publish this save and i'll just publish it in my workspace now let's open that report so when you open it for the first time you'll see that whoa you can see everything even though you're logged in as fernan um if you're the owner or yeah if you're the owner or the publisher of the report you will as default have access to the whole of the data set and the rls only kicks in for other people accessing it but if you want to see how it would look like if you are logged in as yourself let's go to the workspace let's go to the dataset here click the ellipsis security and you'll see here that you will have the permissions rls which we have created here what you can do to simulate it here in the service you can click here and test this role so you'll see you have the kind of view as like as if you were assigned to eu and na so now that we can see that it works let's share this to somebody else let's show it to john and let's see how john will perceive this report because john also has some limitations with this report right so a couple of things that we need to do first we need to add john to the row level security just make sure that he has access to the data save and also we need to go to the report and we need to share it to him right so we need to hit share people in your organization it doesn't really matter let's do specific people instead and let's say let's give him access to everything and let's type john so john is part of my organization and i want to share it to him i'm going to copy the link and then i'm going to share this to john so now we're signed in as john and you can see here we're signed in as john at solutions world uk and we have just copied that link to share to john so as john will access that link so i'll just enter it here and it'll give me access to that report and now you'll see that the report recognized me as john and it's filtered the data to only be able to see the regions that i have access to which is apac nna so i don't see any employees that are in eu one important thing to understand with the rls is that because it's a security filter and if the rls is applied even if you manage to get a hold of the data sets you still won't be able to disable that security filter so what i mean by that is for example let's go to my workspace and let's create a new report let's say we can create a report from published data sets that we have access to and you'll see whoa i have access to this data set that has been shared to me so remember when i shared it to john for the first time i gave him access to be able to build reports on top of this data set now uh let's see what it looks like so i'm gonna check check this data set to use and let's hit create so now you see that i have access to the underlying data here which is great but what you'll see when i bring in region you'll see that the data set doesn't allow him or rather it doesn't allow john to see the other regions so it still has the rls applied to it regardless if you have access direct access to that data set which is great when it comes to securing your data and that's really it for this video i hope it helped you understand how easy it is to start setting up dynamic row level security in your power bi reports leave a like in this video if it helped you it's the best way to let me know that you enjoy this type of content get in touch using the social media links that i included in the description box below and thank you so much for watching guys see you again on the next one

Channel: Fernan

Views: 72,209

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, power rls, rls, row level security, power bi dynamic rls, dynamic rls, dynamic row level security, how to use dynamic row level security power bi, data security power bi, security filter power bi, power bi rls, power bi dataset security, power bi service

Id: axTyP7I2Bso

Length: 12min 12sec (732 seconds)

Published: Mon Jun 21 2021