Exploring Object Level Security in Power BI - Unplugged #13

ciao friends in this unplugged video i want to talk about object level security the object level security is a feature that has been introduced in a power bi in february 2021 but it has been available for a very long time in analysis services this feature enables the definition of security groups that have no access no visibility over part of the data that you have in the model defining columns or entire tables that should be hidden to a few users when you do that every calculation that could be based on those columns or tables is hidden to the users too if they belong to a restricted security group in this video i will i will show not only how to define the object level security but also what are the consequences for the user that consume the data exposed this way because as we will see there are differences between power bi users and excel users and so if you're ready this unplugged video i will try to make no cuts no edit and so let's move to the demo machine and start to see what i prepared here i have a simple report where we show a few measures sales amount of cost margin and margin percentage this is a classical contoso database we have a sales customer products but basically we are just using the sales table and the customer table to show the data grouped by country so the sales amount is a measure that multiply quantity by net price so we have two columns in the sales table we are using here whereas the total cost is a similar measure that sum the result of the quantity multiplied by the unit cost so the margin is the difference between these two measures and the margin percentage expresses the same value as a percentage over sales amount the requirement the business requirement could be i have a group of users in the company that should see the sales amount measure but not the total cost the these users don't have to see anything related to the cost of the product which means that they cannot see also the margin and margin percentage the object level security allows me to do that saying well i want to hide the unit cost to a group of users how to do that first of all we define a security role like we do when we create security roles to hide a few transactions or a few countries or a few products so let's start creating this group no cost probably it will be a different name but this just clarifies what we want to do so the no cost group is the group that we would need so let me remove this one i don't want this and i don't have to do anything else because at this point i just define the role in power bi power bi doesn't have a user interface to edit the object level security so i have to go in external tools and open tabular editor tabular editor is able to modify the properties that are not exposed in power bi like in this case the object level security so let's zoom a little bit in this model so we can see better that i have this no cost role which has here a few properties you see here table permission roller security at the moment i didn't set any table permission table permission will allow me to hide an entire table to an user i don't want to do that if you want to do this you can go here and you can change the visibility so if you say none it means that you disable the visibility of that table whereas red you see okay you can read the data from this table i don't want to do this at the table level i want to do this at the column level which means that i have to go in the table sales and i can see all the columns here what i want to do i want to hide the unit cost which is this column if i select the column here i can go in this area of the table editor and if i scroll down you see that here we have object level security and under object security you see the list of the rows that are available so let's say that okay for no cost which is the only role that i defined so far i want to specify that this should be none i'm saying the users that belong to the no cost security group have no access to this column okay now when i save this change to the model power bi is updated and at the moment i don't see anything happen here because at the moment i didn't set here any particular change any particular visualization using that specific security role but using view as i can say i want to impersonate the no cost security role which means that when i click ok now power bi will show the data as i was an user logged in power bi.com and this user belongs to this group so i click ok and now i no longer see the entire report what is happening if i click on this report here you see that these four oh sorry let me move my head here so here you see that the i had four measures in the metrics before only one is recognized sales amount and the other three measures are no longer visible because this report was referencing three measures that now are no longer included in the list of the measures here you see that let me remove my face at all so you see that in says i have only sales amount visible i no longer see unit unit cost and i no longer see any measure that was related to unit cost if you remember the measure total cost was a tax expression that was including unit cost so any measure any calculated column any calculated table that is directly or indirectly related to a column that no longer exists in the model for that user disappears from the model and because it's no longer part of the model any report that tries to reference that measure is no longer valid now if you think for a moment if this happens when imaging this situation you created the report you publish the report in a in a in a workspace where many users can access but a few of these users belong to that security role well these users will not see the entire visual that contains at least one measure or one column that is hidden to those users because the visual is no longer valid now this means that if you connect to the model and you create a new report as a user that cannot see the unit cost you cannot see the margin and no problems you can create a new report and it works but if you want to create a report that has to be visible and can be consumed by those users that have a limitation in the access to the metadata of the model then you have to create reports that don't make any use of those vis of those columns or measures or you should create a separate pages warning the user look you cannot go there because if you go there there will be something that you cannot see now i said that the um excel has a different behavior let me explain you why so let's go in external tools and analyze and excel this model now by clicking analyze in excel now i'm creating an excel connection where i am an administrator of the model so being an administrator actually i i can do everything right and i see everything if i show you here the measures visible here you see that we have margin margin percentage says amount of cost which means that i can create the same report i originally created you remember in the so let's use here country the same report we created at the beginning in uh power bi uh probably i didn't use the counter the customer the country or the store here we go so uh well i don't know i me maybe it was different i don't remember anyway so the important thing here i probably it was the customer country not this one yes so let's say that i i like this visualization and i save this file okay and i'm administrator so i save this as browser c demo and i call this test o l s o l object level security okay now what i saved this file and you know as long as i keep the power bi desktop open the file is still valid but i should change the connection string so that i simulate that my user belongs to a particular security role because i don't remember how to do that i can use a small trick i can open let me see dac studio and dax studio has a feature when i click connect i can choose to connect to contoso here but here i want to create a connection string that says i want to impersonate the role no cost i show you this highlighted so i'm creating this connection string right so when i do that this connection string is actually using the user as no cost which means that if i go here and i look at the metadata visible here you see that i don't see total cost i don't see margin and so on as i expect now if i go here and i go and analyze in excel here i can create the connection but oh sorry this doesn't this doesn't work as i expected this is the unplugged version so i have to remember now uh how the connection string is named so let me try to go back so let's go this let's do this so i have to go back to the other excel file and i try to edit the connection string so that we introduce in the connection string the name of the role i hope i remember the connection string to use so here in the definition you see in this small dialog box this is the connection string that has all the data defined automatically by the analyzing excel feature we used before and i think is roll equal to no cost i hope this is correct let's try if i do that it's not this one so let me go back to google i will i will search this and i will be back in a moment oh yes now i found the documentation and the recommendation says i have to use roles with the plural and the string recorded this way so let's get this and modify the connection string again here we go so here oops this is correct yes and this is rows equal to no cost if i have this because i could belong to multiple roles actually here we go you see that after i changed the connection string what happened is that excel didn't fail the visualization of the pivot table what happened is that excel simply removed all the columns or measures that are no longer visible so even though the initial pivot table had the reference to margin and total cost and margin percentage now the pivot table simply show what is available removing what is not available now what is not available is no longer part of the excel pivot table so if this user now save the excel file the excel file will no longer reference sales amount even though it is later opened by an user that has that will have access to those information and as you see here the pivot table fields doesn't show anything that is not visible to the user that belong to the no cost role so i think that this is what i wanted to show you today the object level security feature is very interesting and could be very useful in scenarios where you want to hide the access to a particular column or table and when you do that any reference to that column or table is completely hidden to the user that belong to the security rule that has this restriction the consequence of that is that an existing report connected to a model where the report was using data that is no longer visible to the users that want to see the sa that report uh the every visual that has a reference to those no longer visible measures or columns will fail and the failure is for the entire visual power bi doesn't have a system like the one that excel uses that removes the invalid references right so this is a different approach i'm not saying that one is better than other they are different so you have to be aware of this difference when you design the report using the object level security feature in your 10. thanks for watching and enjoy [Music] ducks

Channel: SQLBI

Views: 7,942

Keywords:

Id: 8s3MByrZJgg

Length: 15min 31sec (931 seconds)

Published: Sat Apr 03 2021