DA-100 Microsoft Power BI – Part 4 Section 3 – Row Level Security

1. Adding role-based Row Level Security

In the previous video, we created this report and we shared it with Susan and Jane. But Jane should only be able to see bikes and Susan should only be able to see accessories and clothing. So we need to implement some security. And we implement the security on the data set. So let’s go to our data set here and click on the and we have a security setting.

So we click on that and we should easily be able to add the level of security we want. Oh, role level security is moved to the Power Bi desktop. Any previously defined role level security, and this goes way back because it’s been moved for quite a time, is no longer working. You will need to recreate role level security in Power Bi desktop. And in here, in the Power Bi service, you add members to roles you create. So, role level security is a two step process. First of all, you set it up in powered Bi desktop, and then secondly, you assign people to the roles that you have set up.

So let’s first of all talk about what on earth role level security is. It’s that people can see, or more accurately not see certain roles in your data. So here is the category. So we can see that there are four categories. And I want Jane to be able to see bikes, and I want Susan to be able to see clothing and accessories. Me, I want to be able to see the lot, and in fact, I can see the lots. You see all of the data in the Power Bi desktop. So now let’s go and create these roles. So if I go back up to the report and into the modeling tab, we have got under security manage roles. So I want to create my first role. And this role is going to be a bikes role.

So look what it says. Filter the data that this role sees so what bikes can see using a DAX photo expression that returns a true or false value. So in the product dim product category, I want to add a filter. And I’m going to add a filter based on the English product category name. Now, you can just type it, but it’s a lot easier for you to ask the computer to do some of the hard work. So the English product category name in tables, you’ll notice this is just a filter on the table. Dim product category is equal to bikes. Okay, so let’s click save.

Actually, let’s validate it, make sure it is a valid DAX expression. Yeah, that’s fine. And click save. And there we go. And nothing has happened. At least nothing visible. I can still see things like accessories and clothing and tire and tubes and that sort of thing. However, I can view as a role. So if I click on View as and I say I want to view as the roll bikes and click OK, notice what happens. So now I’m viewing as bikes. You can see that the sales amount has really gone down. We have just got road bikes, mountain bikes, towing bikes. It used to be 29 million. And I can confirm this by going back to our visualization. Here it is and clicking on the report.

So it was 29 million and now it’s 28 million. And the only sub categories I can click on our mountain bikes, road bikes and touring bikes. So now let’s create another role. So this role is not bikes and I think you can probably easily guess what’s going to happen. Let’s copy the previous photo and just say is not equal to bikes. So I’m viewing this report as bikes and you can see not very conveniently because everything’s right at the top and so is that little box there. So I’m just going to just change it so it’s not right at the box top. I suppose you would often have a title at the top and maybe that’s what it’s thinking. So here we go. I’m going to now view as not banks. And now you can see our figure is now 1,040,000 and we have got rid of all of the bike stuff.

So that is how to implement roll level security here at the Power Bi desktop. So I’m going to save this, I’m going to republish this. So you can see I already have this data set. Replacing this data set may impact a report on the dashboard and I can view the impact of the change. And that opens up another web page which shows all of these. But we don’t need that at the moment, we’ve already seen all of those previously. So I’m going to replace and now it’s done it. We can go back into our Power Bi, we can refresh it all. And you can see the amount is still 29 million and we can go into Jane. And Jane should now only be able to see the bikes and nothing else.

So here’s Jane’s account. So I’ll click refresh, currently 29 million, and it will be 20. This visual contains restricted data. See details. So contact the data set owner to request access to the data behind it. So we’ve implemented role level security in the desktop. We haven’t done so here online in the server. So what we need to do is add people to these roles. So by default, nobody can see nothing once roll level security is on, except for the owner, which is me.

So I’ll go down to this data set, I’ll go back to security, which, if you remember, said roll level security, you do it in the Power Bi desktop. It now says something different. It now says who is part of bikes and who’s part of not bikes. So Jane is bikes. So I’ll add her and obviously I can delete her by clicking on the X. I’ll click Save why not? And I’ll go to not bikes. And that is Susan. Click add, click save. So this is my account. I’ll go into Jane’s account, which is here, and I’ve refreshed her screen. And you can see we have a total of 28 million, as opposed to 29 million.

And I’ve gone into Susan’s account. I’ve upgraded her to Power Bi Pro as well, and she has a total of 1 million. So in this video, we had a look at how to create raw level security. We started off by going into Power Bi desktop and modeling and managing roles. We’ve created roles for bikes and for Not Bikes. We’ve tested these roles by going to modeling view ads, and then we’ve uploaded it. And we have then assigned people to these particular roles. In the next video, what we’re going to do is have a look and see if you can use a person’s email address or username to assign them automatically to these roles.

2. Adding Dynamic Row Level Security

In the previous video, we modified our dashboard to introduce role level security and you can see that we have got three different members, philip, Susan and Jane, with three different amounts of totals, 29 million, 28 million and 1 million. So they have access just to the things that they should have access to, because Susan is all about everything except the bikes and Jane is just about the bikes. And we did this by saying, okay, if it is bikes then we have a bike’s role and if it’s not bikes, then we have a not bikes role. However, it would be better perhaps to use their names. So we’ve got three names, we got my default name and we’ve got Susan and Jane. So it’d be great if we could use this and we can. If we go to power bi desktop, there are two functions that we can use.

And those functions are, these are DAX functions, username and user principal name. Now it is important to know the difference between the two in power bi desktop. So where we are right now, then username will return something in the format of a domain user. So you’ll see these especially on business computers. In power bi desktop, user principal name results in an email address. So for instance, Susan, at, falcats, et cetera. So that is on the power bi desktop in the power bi service, then both username and user principal name will result in the user principal name, which is the email address or what looks like an email address. So if you’re going to be using this on power bi desktop and you want the user name as opposed to user principal name, then if you’re going to publish it on the power bi service, you need to ensure that what you’re doing. Your DAX formulas accounts for the name in both types. However, username can also be used on services other than the power bi service. For example, maybe you’re building an app and you’re passing in a username. So that’s when username function can also be quite useful.

So let’s have a look at user principal name. As you can see, we’re probably going to be using this as we are transferring this to the power bi service. So let’s see what current value the username and user principal name have. So I’m going to insert a card and I’m going to insert into the card the username and user principal name. So I need to add in the user principal name here, but I can’t see a way of doing it. So what I need to do is insert a calculated column, a new column or a measure. Which one do you think it is? Well, I’m going to get a column because a measure sounds like something that’s numerical and text is not numerical. So the username equals and it’s just username or bracket, close bracket. So let’s a look and see what we get. And unfortunately we get an error message. We have that custom data, username, user culture and user principle name are not allowed in calculated columns. They can only be used in measures. Okay, no biggie. So let’s get rid of that.

And instead we’ll add a measure. So a new measure and it’s exactly the same. So there’s my new measure and I’m going to drag it into the card and you can see this is my domain and backslash and this is my username. So computer name, domain and username. So exactly what we were meant to get. So here we are, username domainstore, user. So let’s get the user principal name. It’d be interesting to see how the computer knows my email address. So user principal name equals user principal name, open brackets, close bracket and let’s replace this in the card. And it’s exactly the same. The computer doesn’t actually know my email address.

So what uses this? Well, first of all let’s go back to modeling and I’m going to view as now there is a fourth option that we haven’t used, reviews non, which is the standard option and reviews bikes and not bikes. But another option is other user. When you check that you’ll see that you actually have space to put in who that other user is. So I’ll put in Susan. And now you’ll see that the user principal name is Susan At and it gives the full email address. And out of curiosity, what’s the username? Username is exactly the same. Now I want to dip onto power bi service.

So I’m going to be using in this case user principal name, as I say, username only to be used in power bi desktop. If you have got a username domain, backslash, whatever. So what’s the next step? Well, the next step is to insert a new role. So I’m going to insert a new role. So this is probably called whatever my role. And in this role we only want those roles in dim product category where something is equal to user principal name. So what could be equal to user principal name? Right. So let’s go into our data. And here we have category key, english product name, French product name.

What I’m going to do is insert a new calculated column. So we’ll get a new column and I’m going to say if the product category key is one then it is Jane’s. If it’s two then it’s nobody’s. Nobody owns components and if it’s three, if it’s four then it’s Susan’s. What DAX functions have we got that allow us to do this sort of conditional logic? And the one I’m going to use is switch. So switch allows us to have a single input and then if the answer is one then this one is Jane’s. If the answer is two then it’s nobodies. What’s a good way of writing nobodies? Blank. You can do null strings and other things but blank is a pretty good way. And if it’s three, then give me Susan. And if it’s four, also give me Susan. So now we can see while looking at the data who owns this. So now let’s go back into our manage roles. And so here’s my role. And so we want if I click on the dot and got to add filter. And we have Huna owns who owns equals user principle name. Right? So is that all we need? Well, nothing seems to have happened. We’re still viewing the report as Susan, but we have a total of 29 million and we are seeing bikes and not bikes. So what’s happened? Let’s just have a look at the top. Now viewing the report as Susan, if I click back into Manage Roles, we have this new My role, which is the dynamic filtering on user principal name. We’re not using My role at all. So we have to log into somebody else and activate the role, my role. So let’s click back on view as we have a checkbox for Susan of a user.

And I’m going to add a check to My role. So now let’s see what happens. So at the top, it now says now viewing report as My Role and Susan. We now have a grand total of only a million and the bikes have disappeared from everywhere. Let’s change this from Susan to Jane. And there we can see we’re now up to 28 million. Not the 29 million. Only things that we can see are bikes. Now let’s put in an email address that doesn’t exist. We’ll put in Philip at Faucats. I’m using a different email address on the Power Bi service. So click OK. And as you probably hope you would see, I don’t have access to any of these roles. So the dynamic roll level security is working.

So what’s the difference between the static roll level security and the dynamic roll level security that we’re now using? Well, with a static one, we have to create a role so like bikes. And we have to say which roles are going to be omitted or included in this case, or everything else is omitted. And then on the Power Bi service, we have to say these people fulfill this role. With the My roles, we have a DAX expression which takes each role and puts it in some way against the user principal name or the user name. If you’re using an app or something like that to connect into this report.

And then the computer just takes it from there. So in the next video, we’re going to upload this into Power Bi service service and we can see how Power Bi service handles this dynamic RLS role level security. Please join me in another.

3. Testing Dynamic Row Level Security in the Power BI Service

Well, let’s test it. So where we’re going to test it is I’m going to save it as a different name. Very important you save it as a different name because we don’t want to overwrite the static, raw level security that we previously had. This is dynamic. So I’m going to call this dynamic, raw level security. So I’m going to save this as a local file and then I am going to publish it. So publish it to Power Bi and we’ll still go to my workspace. And here it is being published. Now I’m publishing it to my workspace. Just as a note, if you do either static or dynamic role level security and you publish to a workspace, the roles will only apply to readonly members.

So by default, my workspace is private, so anything I share is read only. So that’s fine. But if you do it to another workspace, as we’ll be doing in the future, then when you share it on, it will have to be to read only members. If it’s to edit permissions members, then they’ll be able to read everything. So it’s now successfully published. So let’s now test this in the Power Bi service. So here we are back home in the Power Bi service and we have got this dynamic RLS report. So we need to assign roles. So we go down to data sets, we go down to dynamic Ros, we click on it, we go to security. So here we have security and we’ve got roles. So we need to assign people to roles, this particular role. So we’re using the My role, because if you remember, the My role is the one where we’ve said that the dim product category must equal user principal name.

So if you know now go back to my role in the Power Bi service, I’m going to add myself, I’m also going to add Jane and I’m also going to ask Susan. So click all of those add and save. So what we can now do with a role is to test the role, there’s a little dot, dot, dot near a particular role. So if I click test role, I’m now viewing as my role. So my own role, my role, and there’s nothing there, obviously, I don’t have access to anything. Now I can select a person. So I’m going to select myself again.

So here’s me and I’m going to apply. And this is what I can see, absolutely everything because of course I am the manager. But now we’re going to change this. So I’m going to be looking at Jane and here all she will be able to see are bikes. And now if I go for Susan, she will be able to see everything except bikes. So what I’m going to do, just going to go back into here, I’m going to reduce the size of this. It’s useful to know in these testing purposes who is the user principal name.

I’m going to just maybe reduce the size of it as well. 45 font is very big. I’ll make it more like 20. Right? So just save again, publish again. So this testing facility that you’ve got in the Power Bi server, very useful. Before you share, you can see what it will look like on any particular role. Of course we can do that with the other securities that we’ve already got, the static ones. So I’ll just click refresh because we have just reuploaded this. So hopefully the name is a lot smaller. So if I go back into data sets security and test role so go back to Jane, that’s what Jane can see.

Go back into Susan, that’s what Susan can see. And if I’d wanted to test any of the other roles like bikes for instance, here is Bikes and you notice the name has gone back to me and here is not Bikes. So let’s do the final test because that’s great to actually test that it works, but let’s actually make it work. So let’s go back to this report and I’m going to share this. I’m going to share this with Jane and with Susan. So they are successfully shared. Very quick, go back into I think I’m into Jane’s Susan’s here. So click on dynamic role level security and you can see in the shared with me, you can see all shared or who shared. So you can narrow it down to a particular person. So here is what Susan can see and we can see it is everything but bikes.

And when we go into Jane, we can see that it is just bikes. Now, obviously the actual presentation of this dashboard is fairly dreadful. I wouldn’t keep it like this, but this is just a quick one just for testing purposes and auditing. So you can see who you are logged in as well. So, in this video we had a look at dynamic role level security as opposed to the static role level security. We looked at creating an overall role and it says who owns which is the name of a column within Dim product.

Who owns is equal to whoever the current user principal name is or the username. And we had to look at the difference between user principal name and username. You should only use username when you have got an actual need for it. If you are going onto Power Bi service then you don’t use username, you use that’s right user principal name. And that is dynamic role level security. If this gets a bit too complex, well, don’t worry, you’re do got the static role level security as well. And you just need to assign individuals to each of these roles. So that is role.