We implemented a semantic model on Azure, granting access to our users through Azure AD groups. By creating roles that include Azure Tenant IDs and enforcing Row-Level Security (RLS) dynamic settings.
However, we’ve encountered a challenge: when changes are made to certain fields, the RLS configurations can sometimes fail to enforce restrictions properly. This can lead to unintended access, where users in an AD group suddenly find themselves with visibility into all data, rather than the limited data they should see.
To address this, we’re exploring ways to implement a more restrictive access model within the RLS role configuration. Specifically, we’re considering options that would prevent any data visibility until RLS settings are confirmed to be operational.
Proposed Solution:
Initial Data Restriction: Until the RLS settings are validated, no users should have access to any data.
Validation Checks: Implementing a validation mechanism to ensure RLS settings are working correctly before granting data access.
Has anyone had similar experiences and how did you tackle these?
Thank you for your support!
Page 1 / 1
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:
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.
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.
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.
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 Mik,
I checked further and placing the row level security setup on a dimension table field does appear to be particular to that dimension table. In order to filter the fact table, then you do need to include a column from the dimension table in your PowerBI Report Table in order to have the row level security setup filter that PowerBI Report Table. If your PowerBI Report Table is just comprised of fields from the fact table, then the filter associated to the dimension table will not apply.
One way to address this is to create a star schema type of fact table in your MDW, where the appropriate dimension fields are added to the fact table as lookup fields. Afterwards, create your row security setup on the fact table dimension field, which is more analogous to how our TimeXtender Tuesdays video is setup.
If you have any further questions, please let me know.
Greg
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.
