Role Level Security Enforcement

Hi Mik,

Creating the security setups on the dimension can be done, but since I am using a dimension table field value, I found it easier to use the manual setup as opposed to the dynamic setup according to the following steps:

1. Created AD Groups that corresponded to the dimension values. For example, I have a dimension field called “Territory” and it is either populated with values of “East” or “West”. I then created two AD Groups called “EastRegMgrs” and “WestRegMgrs” whose members are comprised of the managers for those respective territories. “FredWilliamson” is my West Regional Manager and “RobertSchlafen” is my East Regional Manager.

   

    

2. I then create two roles “WestRegionalMgrs” and “EastRegionalMgrs”, and each role has the corresponding AD group as its member. “WestRegionalMgrs” has as its member the “WestRegMgrs” AD group.

   

    

3. I then created two Security Setups that map the respective territory values to their role members. The WestRegMgrs Security Setup maps the Territory field value of “West” to all of its role members.

4. The “WestRegMgr” security setup is mapped to the “WestRegionalMgr” role, so all of those role members will only see values where the “Territory” field value is “West”.

When I launch PowerBI and view the Semantic Model as Fred Williamson, I only see the West values. Conversely, if I launch PowerBI as Robert Schlafen, I only see the East values.

In the screenshot above, Robert Schlafen is on the left showing the East values and Fred Williamson is on the right showing the West values. For the tables displayed, you can see on the right that the Territory field is being selected from the EE dimension table and includes the Sales_Amount value from the Sales fact table.

If I try to open the Tabular model as a user that is not a role member, and is also not an Analysis Services Instance Administrator, then I will get a message stating that the model cannot be opened as the user does not have access to the database.

If you can provide more information regarding how the dimensions and fact table values and relations, I can see if there is a way to do that dynamically. The trick is to map individual logons to certain values that will then filter the entire model.

Hi Greg, 

Thanks for your reply! 

That is exactly the setup we use, but the issue that I was describing lays if new data gets added.

Say for example, suddenly another region gets added to your data model. I noticed you are using adventure works and let’s say Europe is added in your “territory table”. Your night load picks up the change that happened in your ERP system and if you haven’t manually configured a role for European Managers, the next morning European data will be visible for both Western and Eastern Managers, because the system will not know where to allocate or restrict it to. This might be a bad example with regions as they could be blocked, but if you have data part of the same hierarchy of a lower grain this could be possible.

So, we are exploring of a more restrictive model when it comes to RLS, that no user sees any data until a RLS has been defined.  

Hi @mmax77 ,

given this quote: “but if you have data part of the same hierarchy of a lower grain this could be possible”, I assume you are describing the situation where a user has access to a node in the hierarchy and leaves are added that you would want to exclude by default? Otherwise the default for RLS is that you only get access to rows if you have a positive GRANT. 

If you are dealing with hierarchical data you can avoid this by only granting at the lowest level (leaves). As DENY overrules GRANT, you cannot explicitly deny all access to All.

Hi @mmax77 do the comments above help answer your question? If so please help us by marking  a best answer above. Please let us know if you have any follow up questions