PL-300 – Section 34: Part 4 Section 3 – Row Level Security

255. 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 dataset.

So, let’s go to our dataset here and then click on the dot-dot-dot (…) 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, “row-level security has moved to the Power BI Desktop. Any previously defined row-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 row-level security in Power BI Desktop. And in here, in the Power BI Service, you add members to roles you create.” So row-level security is a two-step process. First of all, you set it up in Power 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 row-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 lot, 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 modelling 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 filter expression that returns a true or false value.” So, in the product, DimProduct Category, I want to add a filter and I’m going to add a filter based on the English Product Category Name, and I could 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 notice, this is just a filter on table, on the table DimProduct Category, is equal to bikes. Okay, so let’s click save. Actually, lets validate it, make sure it is a valid DAX expression. Yep. That’s fine. And click save. And there we go. And nothing’s happened. At least, nothing visible. I can still see things like accessories and clothing, and tyre 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 role 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, touring bikes. It used to be, it was 29 million. And we can confirm this by going back to our visualisation, here it is and clicking down the report. So, it was 29 million and now it’s 28 million. And the only subcategories I can click on are 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 filter and just say is not equal to bikes. So, I’m viewing this report as bikes, so 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 to the box top. I suppose you’d 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 bikes. And now you can see our figure it is now 1,040,000, and we have got rid of all of the bike stuff. So, that is how to implement row-level, 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 dataset, replacing this dataset may impact a report or a dashboard and I can view the impact of the change and that opens up another webpage, 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 this, 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… Oh, “this visual contains restricted data, see details.” So, “contact the dataset owner to request access to the data behind it.” So, we’ve implemented row-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 row-level security is on, except for the owner, which is me. So, I’ll go down to this dataset. I’ll go back to security, which if you remember said row-level security, you do it in the Power BI Desktop. It now says something different. It now says who’s 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 to 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 row-level security. We started off by going into Power BI desktop and modelling and managing roles. We’ve created roles, for bikes, and for not bikes. We’ve tested these roles by going to modelling view as, 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.

256. Adding Dynamic Row Level Security

In the previous video, we modified our dashboard to introduce row level security. And you can see that we have got three different members, Phillip, 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’s is bikes, then we have a bikes role. And if it’s not bikes, then we have not bikes role. However, it would be better, perhaps, to use their names. So, we’ve got three names, we’ve 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, that these are DAX functions, user name, 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 user name will return us something in the format of a domain backslash user. So, you’ll see these, especially on business computers. In Power BI desktop, user principle name results in an email address. So, for instance, susan@filecats, etc. So that is own the Power BI desktop. In the Power BI Service, then both user name and the user principle 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 their user name as opposed to user principal name, then if you’re going to publish this on the Power BI Service, you need to ensure that what you’re doing in your DAX formulas accounts for the name in both types. However, username can also be used on services offered in the Power BI Service. For example, maybe, you’re building an app and you’re passing in a user name. So that’s when that 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. Or which one do you think it is? Well, I’m going to get column because a measure sounds like something that’s numerical and text is not numerical. So, the user name equals and its just user name, open bracket, close bracket. So, let’s have a look and see what we get. And unfortunately, we get an error message. We have that custom data, username, user culture, and user principal 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 that is 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 user name. So, the computer name, domain and username. So exactly what we were meant to get. So, here we are; username, domain stroke user. So, let’s get the user principal name. It would be interesting to see how the computer knows my email address. So user principle name equals user principal name, open brackets, close bracket, and let’s replace this in the card and it’s exactly the same, but the computer doesn’t actually know my email address. So, what’s your success? Well, first of all, let’s go back to modelling and I’m going to View as.

Now, there is a fourth option that we haven’t chose, we’ve chosen the option which is the standard option and we’ve used 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@, and it gives the full email address. And out of curiosity, what’s the username? Username is exactly the same.

Now, I want to deploy 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 user name, 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 could be called, whatever MyRole, 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 that’s a new column and I’m going to say, eighth the product category key is one, then it is James’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’s allowed 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 import. And then if the answer is one, then this one is James. If the answer is two, then it’s, nobody’s. What’s a good way of writing nobody’s? Blank, you can do no 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 MyRole. And so we want, if I click on the double dot and got to Add footer, and we have who owns, who owns equals user principal name, right? So that’s what we need. Well, nothing seems to have happened. We’re still viewing the report to Susan, but we have a total of 29 million and we are seeing bikes and no bikes. So, what’s happened? Let’s just have a look at the top.

Now, viewing report as Susan, if I click back into Manage roles, we have this new, MyRole, which is the dynamic filtering on user principal name. We’re not using MyRole at all. So, we have to log in as somebody else and activate the role, MyRole. So, let’s click back on View as. We have a chat box for Susan of a user, and I’m going to add a check to MyRole. So, now let’s see what’s happens. So at the top it now says, now view role as MyRole and Susan, we now have a grand total of 1 million and the banks have disappeared from everywhere. Let’s change this from Susan to Jane. And there we can see we’re now two 28 million, not the 29 million. The only things that we can see are bikes.

Now, let’s put in an email address that doesn’t exist. We’ll put in Phillip@Filecats, I’m using a different email address on the Power BI Service. So click, okay. And as you probably hope you would see, I don’t have access to any of these roles. So, the dynamic row level security is working.

So, what’s the difference between the static row level security and the dynamic row level security that we’re now using? Well, with a static one, we have to create a role. So like banks. And we have to say which role are going to be omitted or included in this case. So everything else is omitted. And then on the Power BI Service, we have to say, these people fulfil this role, With the MyRole, we have a DAX expression which takes each role and puts it in some way against a user principal name or the username if you’re using an Apple, or something like that to connect into this report and then the computer just takes you from there.

So, in the next video, we’re going to upload this into Power BI Service and we can see how Power BI Service handles this dynamic RLS, row level security. Please join me in the next video.

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

Well, let’s test it so why 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 Row Level Security that we previously had this is Dynamic, so I’m going to call this Dynamic Row 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 we are it is being published.

Now, I’m publishing it to My workspace, just as a note, if you do have a Static or Dynamic Row Level Security, and you publish to a workspace, the roles will only apply to read-only 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 you… 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, its now successfully published, so let’s now test this in the Power BI service. So, here we are back at 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 Datasets, we go down to Dynamic RLS so 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 in this particular Row. So, we’re using the MyRole because if you remember, the MyRole is the one where we’ve said that the DimProductCategory must equal User Principal Name. So, if you now go back to MyRole in the Power BI service, I’m going to add it myself, I’m also going to add a Jane and I’m also going to task 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 MyRole, so my own role, MyRole, 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 is 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 well, 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… I’m just going to go back into here, I’m going to reduce the size of this, it’s useful to note who in these testing purposes, who is the User Principal Name, I’m going to just, maybe, reduce the size of this as well, 45 font is very big, I will make 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 would 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 Datasets, 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, who is Bikes? And you notice the name has come back to me and here is not Bikes. So, let’s do the final test because that’s great to actually test and 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’re successfully shared, very quick, go back into Susan’s here. so click on Dynamic Row 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 Row Level Security, as opposed to the Static Row Level Security. We looked at creating an overall role and it says, WhoOwns, which is a name of a column within DimProduct, WhoOwns 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 Row Level Security, if this gets a bit too complex, well don’t worry, you still got the Static Row Level Security as well, and you just need to assign individuals to each of these roles, so that is Row Level Security.

• Category: Uncategorized