6.3 Setup row level security - Video Tutorials & Practice Problems

<v Instructor>In this sub lesson</v> we're gonna introduce Row Level Security. We're gonna demo setting up Row level Security, and then we're gonna discuss some next steps around Row Level Security. Okay, so quickly let's first introduce what Row Level Security even is and why you'd wanna go about using it. So what role level security allows you to do is secure your data at the role level. So for example what this means in our SalesDataMart is if we wanted certain people to only view United States information and certain people to only view information on a Canada we could go in and set up a role in the Power BI desktop that basically filters the dataset down to United States or Canada, depending on which one of the roles you actually reside in. Now, this is a fantastic way to go ahead manage your security and is light years ahead of what people had in other reporting tools and what you've even seen inside of Excel in the past because this allows you to really start finally Greenling controlling your data in your actual DataMart itself. So we go ahead and initial we'll set up some roles in the desktop. We will configure those roles with the necessary permissions using docs. And once again, this is all done in the desktops. We're gonna get ourselves back into the desktop for the very last time. Once we're all complete setting up that we then take that data set and pbix file and publish it into the service against we'll do a republish our information. And then once we get into the service itself we'll start adding people into those roles So that when they log in and our part one of the roles let's see United States or Canada they will only see the data that pertains to them. And once again, a lot of times what I've seen people do in the past with Excel spreadsheets is they would take the Excel spreadsheet to embed this type of security and just carve the Excel spreadsheet up into different spreadsheets based on their territory. So maybe one for Canada, one for United States one for United Kingdom and the rest of the territory's. And that would be kind of the rudimentary way of doing things using Excel. But now inside a Power BI we have ways to go ahead and once again create a single report and take advantage of security on top of that. A new piece of security that's been added into Power BI that is still a very new to the tool software was only introduced back in February of 2021 and it's beyond the scope of this class, but it's worth mentioning now is what's called object level security. So now where we can go in and start controlling columns of data and individual tables so this is new and emerging functionality inside of Power BI once again beyond the scope of this class, but between the two you're gonna have some very cutting-edge ways to go ahead and secure the data you want to share with your organization. Okay, so let's go ahead and get the supporting file open for this particular sub-license, we're gonna open up the SalesDataMart that is in the lesson six folder. And we'll give it a moment to go ahead and open. Okay, so my desktop files open here. What we're gonna do first here is go to the modeling tab. So we're gonna click on the modeling tab and then over under this security's group of commands we can see that we've got something called manage roles. So first we're gonna go ahead and get our roles up. I'm gonna go ahead and click on manage roles and we're gonna get prompted with this dialog box here. Okay, so the first thing we're gonna do is we're gonna go ahead and create two roles. So I'm gonna go ahead and click the create button here to go ahead and create our roles Okay, so we're gonna go ahead and create our two roles, so I'm gonna create one called EU and UK manager here So I'm gonna go like this. I'll go ahead and create that one. And then I'm also gonna go ahead and create a role called Pacific managers so we're gonna go in here type in Pacific manager, click on Create. Okay, so we have our two roles here, oops, I actually got one too many or so I'm just gonna go ahead and delete that one there. Go ahead and say yes. And now what we need to do is go into each one of these roles and actually configure them with the necessary filters. So we've got the EU and KU manager role set up. I'm gonna go down to the table-side here and click on sales territories. So what I'm gonna do here is take this piece of DAX code, like, so and drop it into the expression builder here. So I'm gonna do here is I'm gonna click on this to verify the stocks expression to make sure it's correct and it looks good. So now what will happen is if somebody logs in and they're part of the EU and UK manager role, this filter will be applied to the data set so they can only see data from the two countries. Okay, so let's go to the Pacific manager and do the exact same thing. So I'm gonna take the sales territory region drop that code in there and it looks like I did it in the wrong table. So I'm just gonna back that out go into sales territories and do the exact same thing. Once again, I'm gonna check my syntax up here and make sure it's all good, now my two roles have been set up so I'm gonna go ahead and click save and then we're gonna go ahead and test these out. Okay, so what we wanna do here to test these out is once again, in the modeling tab I'm gonna go back to the security group and I'll wanna click on this view as. So I wanna see what's visible to people with different security roles and permissions. So we're just gonna mimic what this would look like if somebody actually logged on with one of these roles. So right now, keeping in mind, you're a developer and you're gonna test all this stuff out. So I'm gonna go ahead and it alright, let's run this report as if I'm the EU and UK manager role. Go ahead and click Okay. And what we're gonna see here is that now the report changes itself. We can see it says at the top here now viewing as the EU and Uk manager. And what we can see here is hidden in behind this a dropdown box here is we can actually click on this here. And the only three countries that show up in the country drop down box, are the ones we have access to. So how is this working, so once again, this is a great way to recap the filter direction back down here in the data model. And over here in the sales territories, we essentially applied filter right here to say that the EU and UK manager can only see France, United Kingdom and Germany. Once those filters are applied, it goes down into the sales table. Cause the filters flows direction here and narrows the results set down to the data so that person can actually go in and see. So go back over to the report. And now let's go ahead and we'll stop viewing as that manager. Let's go ahead and take a look at the Pacific manager click Okay and now we're viewing as the Pacific manager, if I clicked that dropped on box, once again, we're gonna see Australia and the data is reduced down to Australia. Okay, so that's great, we've got our roles set up on the desktop we verified that they're sort of the way we want. Now what we need to do is actually get this into the actual Power BI service. So what I'm gonna do here now is go back to my home tab and we're gonna get ready to run the publish process. Okay, so what I wanna do here is I wanna stop viewing as that role and get ourselves back to the default state once again, a reminder that the way that the report is set right here with these filters here that's how the report's gonna be published and it'll keep those filters as its default view. Okay, so we're gonna go ahead and click the publish button here to get this into the Power BI service. Once again, it's gonna remind me to go ahead and save my work before I go ahead and do that. It's gonna go ahead and push this dataset into a workspace, I first have to choose the workspace I want to go into my workspaces where it's gonna go click Select. We are gonna be prompted here with a warning saying this is gonna replace a dataset and may impact two reports in one dashboards. So if I want, I can actually go and view the impact of this change in the Power BI service itself. And I know things are gonna be fine here. So I'm just gonna go ahead and say replace. So I'm gonna go ahead and push the information up into the service and we'll go ahead and give it a moment here. And we can see that the published process was successful. So now I'm going to go ahead and get myself into the Power BI service. Once again, a couple of different ways to go about doing it. We can actually click on this open SalesMart.pbix but I'm actually just gonna go to my web browser and say got it here to close that off. I'm gonna go into the web browser and open up our My workspace. <v ->Okay, so I'm in my Power BI my workspace</v> and now I can see I've got the the dashboard report and the data set here, we can also see that everything has been recently refreshed. So as of right now these times are actually a current refresh of this. So what I'm gonna do here now is I'm actually gonna go into my actual dataset. So I'm gonna go ahead and click on the SalesDataMart. Okay, so let's go onto the SalesDataMart here I'm gonna find the three ellipses right here I'm gonna click on this. And what we can see here is a set of commands here. So I'm gonna go to the security command right here. And what we can see now is we're being brought to the role level security dialogue box. So now what I wanna do here is I could go in and add in various people into this group here. So I can go ahead and add people by via email addresses individuals or groups here, if I wanted to. So I'm not actually gonna go ahead and add any people in here I'm just gonna show you mimicking this on the service as well. So now what we can do is we'll click these three the ellipses and actually we'll test the security out in the service itself, okay. So let's go ahead and let's take a look and see what the support looks like if we ran it as the EU and UK manager lets click on the ellipses, say test as role. Now it'll bring it back to the report as we saw it back in the service. And we can see now that we're viewing this as the EU and UK manager. And if we go to the dropdown box here are the actual countries who can see. So basically the exact same thing we saw backed on a desktop so, once again we will like to go through the progressions here to make sure the security is exactly how we want, things are working, got published into the service properly so we're constantly testing things out. Now we're ready to start adding people into those individual roles, either via individual email addresses or groups. So a few different ways to go about doing that. Okay, so that's, as far as we're actually gonna take this example here. So I'm just gonna go ahead and go back to my role of a security dialogue box and leave this discussion here often what will come up as some additional topics here, what is called dynamic rule of a security. So in this case here, this data model we built up does not have a security model that we can borrow from. We actually have to go ahead and create it on our own. And this is a very common scenario in a lot of people's analytics as they're pulling data from spreadsheets and text files and maybe various different sources as there isn't a security model built around it they need to do that on their own. But what if you have a security model already in place in a source system, then what you can do is take advantage of what's called dynamic role level security. Now that is beyond the scope of this class here but I just wanted to leave you with that just in case you say, wait a minute, I've got a security model in a system that I wanna go in and implement. Why would I go ahead and recreate that in Power BI you can actually take advantage of that to go ahead and secure that. And just as a final reminder some really new stuff that's come out and Power BI as of the end of February, 2021 is what's called object level security, so I'm personally very excited to start taking a look at that because there are many different ways to go ahead and secure your data inside a Power BI to really give that robustness around a security model, to give you the confidence to distribute information out to individual users. Okay, so that brings us to the end of this sub lesson on Row Level Security.