Blog

Rethinking Security Groups for Power BI

Hello! Yes, this is the Esbrina blog, and yes, it’s an article about Power BI Governance! About time! After learning many things with Melissa Coates and Mike Carlo, and having now some experience as a Power BI Admin, it’s time to start writing about the strategy I’m trying to put in place, in case this is useful for other admins out there.

My client is not huge, but is not tiny either around 200 pro licenses and 900 staff plus providers. Crucially it’s spread across 2 different tenants, but both organizations fall under the same CEO and many teams are cross-tenant, so yes, it gets messy.

Anyway, when I became admin they had already been using Power BI for a while, and there was not a unified strategy on how to manage things. After a few days in the job and talking with a few content creators, I realized that in some workspaces there were security groups in the shape of «{WORKSPACE_NAME}_ADMIN» or «{WORKSPACE_NAME}_MEMBER» etc. The key user told me she had no idea who were on those groups, so she added users added directly to the workspace, Then I asked if they where using Apps and they had no idea what I was talking about. As you see, quite a few red flags!

People are consuming reports through workspaces, permissions are given at the workspace level, and mostly are generously given — almost everyone is at least a member.  Good that they haven’t even thought of RLS so far. Since giving permissions to people is a time consuming process what happens is that workspaces are organized around consumers, not the owner, not even a topic. A «General» workspace is where you put everything that is not quite sensitive. Nobody has really an oversight of what goes in there and if it makes sense to be there. When I suggested of dividing that workspace, the answer was that time is too precious to be giving access to everyone that already had access to «General» (and maintain those access in time!). And they had a point indeed.

I realized that security groups had to be part of the solution, but clearly the attempt that they had in place (static security groups focused on the workspace, managed by a provider) only made sharing more complex and obscure since it was hard to tell who had access to what. Then I started thinking.

After some time I had an «Aha!» moment and thought: «When you share a report with someone, you don’t share it with that person because of their name, you do so because of their role in the organization«. Sooo… wouldn’t it be great if we could have security groups based on the role of the people in the organization? More importantly, wouldn’t it be great if such groups where maintained by someone else without our action? Well,  the good news is that most companies have an HR department (except twitter apparently) that one of their roles is to have a list of all the people in the organization with plenty of attributes, including Organizational Unit, Department, Job Title, and a lot more.

Yay! so how can we tie security to those attributes? Well, here the thing may change from company to company, but hopefully there is some process in which the info from HR makes it’s way up to Azure Active Directory. Often by updating the on-premises Active Directory which in turn syncs to Azure Active Directory. Most likely they will tell you that this process is in place and flawless. Then it’s good though that you check if this is true.

To extract data from Azure Active Directory you can use the API from Microsoft Graph. This is much easier than you might think, you just need to follow this blog post by Just Blindbæk. Just is kind enough to provide a template for Azure Data Factory which you can just reuse and adjust to your needs.

It might take a while to get approval to set this up in your organization, but it’s worth it, trust me. Once you have the json files in an Azure Blob storage you can read them easily with a dataflow and then you can reuse that in as many datasets as you want without overspending on read charges from the blob storage. If you have premium you can even read incrementally to the dataflow. In my case I added onPremisesExtensionAttributes because one of the fields allowed me to tell apart the users belonging to Staff from the rest. Also crucial is to get the accountEnabled field to filter your dataflow later, you do not need users that are not enabled. Also, since I saw that the organization had Azure AD Premium 2 licence, I created a new REST api linked service pointing to the Beta version of the API, and with that I could even extract the last sign-in of each user (I did need an extra API permission to the Audit Log to pull it off). That allowed us to detect licenses assigned to users with the account disabled and users who had not done a single sign in in more than two months! But well, that’s another story. (full list of possible attributes). For me, at the end, the call to the beta API looked like this:

users?$select=id,mail,companyName,department,displayName,
userType,assignedLicenses,onPremisesUserPrincipalName,
UserPrincipalName,jobTitle,creationType,externalUserState,
createdDateTime,deletedDateTime,onPremisesExtensionAttributes,
accountEnabled,signInActivity, onPremisesDistinguishedName, 
employeeOrgData,businessPhones,mobilePhone

Anyway, once you have the data from Azure AD, then you need to get the data from HR. You just need very innocent data so it should not be that complex to get — Display Name, email, Company, Department, Job Title. That should be enough for an initial approximation of where we want to go. Now it’s time you ask them how they map this information to what it’s there in Azure AD. And then do the comparison yourself. From my experience 100% of the times there’s at least 1 manual step in the process that makes this process vulnerable. But once you have the comparison in place as a Power BI report you can just keep comparing them periodically and raise your hand if something is off.  To compare both sources, I always follow this approach.

Now you might think «But why are you so obsessed with users metadata Bernat??» Well the answer is easy. There is something called «Dynamic Security Groups» in Azure AD. As the name implies, users are dynamically assigned and removed from groups based on rules. And these rules are based on users metadata! So if you have proper metadata in place you can define a security group for each «Department» of the company, which can be quite convenient.

Most probably you do not have the permission to create security groups, so you need to ask nicely and make it easy for the provider to create the groups you need. I decided that creating a group for each department made sense. The rule for all members of the «IT» department is:

(user.accountEnabled -eq TRUE) and (user.extensionAttribute3 -eq "Staff") and (user.department -eq "IT") and (user.userType -eq "Member")

Here we make sure that the accounts are enabled, belong to staff members, they have the correct Department listed in azure AD and that they are «Members» instead of «Guest» users (Actually you can create guest users and classify them as «members», but just don’t! it’s not supported in Power BI, meaning that even though it should not have any impact according to de documentation of Azure AD, in practice Power BI will not check if the guest user already has a license in his or her home tenant…). The true beauty of that is that this attributes are managed by somebody else, so you make a report relevant for IT and you share it for the IT department, end of your job. From now on, if somebody moves from IT to another department that was not granted access to the report, that person will lose access, and more importantly maybe, when somebody joins the IT department it will automatically have access to all of the reports that have been shared with the whole IT department through the dynamic security group. That’s pretty cool if you ask me.

Regarding group names, make sure you use the same prefix for all of them! This will make it possible to configure the Azure Data Factory pipeline that we saw before to extract only these groups so you can make sure that the groups contain the people that the rule says (you can also use the same prefix for static groups you decide are necessary for giving permissions).  I called the groups ORG_{DEPARTMENT_NAME_WITHOUT_WEIRD_CHARS} like ORG_IT for the IT department. To extract only the security groups that start with «ORG_» you could call the API like this

groups?$select=id,displayName,description,securityEnabled,groupTypes,createdDateTime,renewedDateTime,deletedDateTime&$filter=startsWith(displayName, 'ORG_')

The output of this call is useful to use in a Power BI Report and show people who is contained in each group so that the adoption of these groups increases. If someone has a spelling mistake on the department it will not be included in the group, but then the best way is to fix Azure AD — remember, «As far upstream as possible,  as far downstream as necessary»

Anyway, in my case I took it a step further and requested that Guest users in Tenant B had to be informed with the metadata they have in Tenant A. That way, even guest users have Department, job title etc informed in Azure AD. They do not have everything though. Those obscure extensionAttributes cannot be informed for guest users since they are synched from on-premise Active Directory, and guest users only exist in Azure Active Directory. Oh well, not perfect, but not the end of the world either. The same thing happens with Active Directory Organizational Units (they are concatenated in something called onPremisesDistinguishedName in Azure AD). They are synced to Azure Active Directory, but you cannot inform them for guest users, even if you have the information from their original tenant. If I’m wrong please someone call me out. At least in the client organization these attributes remain null for guest users even after everything else has been updated. So for guest users you have less metadata, but you can still build dynamic security groups with it.

If you want to group all the users from HR of the other tenant you can define the group «GST_HR» with the following rule:

(user.accountEnabled -eq true) and (user.department -eq ”HR”) and (user.userType -eq “Guest”)

What about exceptions Bernat?? Well, just share with the user if you have to. But ask yourself, why should this person have access? If it’s a one off than go on, but otherwise it might be worth studying if a more nuanced security group can be built. At the very least you can play with the job title, so that if he or she is replaced by someone else that person can inherit all permissions to reports too (as long as the new person has the same job title). The catch here is that organizations love relabeling themselves every once in a while, but I prefer to modify the rules of 20-25 security groups than keep updating security groups manually (or opening JIRA tickets to that effect!) every time somebody enters, leaves or moves around the company. Depending on how cooperative the systems department is, maybe you can even get permissions to update certain extension fields or even custom extensions you can do on Azure AD. With that you could keep more fine grained team info updated in one place only. After all, Azure AD should be the User dimension of the company right? I think that for 95% of the cases you can get away with the department and the job title. For staff, the Azure OUs can come in handy too.

Of course these groups are mostly for wide permissions and as such should only be used in Apps! When you are developing a data set you know who you are working with (or you should at least!), and it’s important to be sure who is modifying it. But hey, that’s the way I see it. With governance, do whatever works for you.

Thank you for reading  all the way until here!

I realized that dynamic security groups are not widely known and can solve some problems. They can even come in handy to avoid Dynamic RLS! think about it :). The main takeaway here is that content oriented static security groups are a pain to manage and that some sort of role based security groups might be more effective and easier to maintain. After all that’s a bit what Azure RBAC is about, right? Ok, I’ll stop writing now. Take care!