In terms of security tabular and multidimensional models of SQL Server Analysis Services are very similar. Both use Roles to handle specific security settings. Those Roles are then assigned to users and groups. This is very trivial if a user only belongs to one Role – the user is allowed to see what is specified in the Role. But it can become very tricky if a user belongs to more than one role.
For both, tabular and multidimensional security settings of multiple roles are additive. This means that you will not see less if you are assigned multiple roles but only more. Lets assume we have the following Roles:
“Bikes” – is restricted to [ProductCategory] = “Bikes”
“Brakes” – is restricted to [ProductSubCategory] = “Brakes”
“DE” – is restricted to [Country] = “DE”
A user belonging to Roles “Bikes” and “Brakes” will see all products that belong to “Bikes” and all products that belong to “Brakes”. This is OK and returns the expected results as both roles secure the same dimension/table. This applies to tabular and also multidimensional.
However, if roles secure different dimensions/tables it gets a bit more tricky. A user may belong to Roles “Bikes” and “DE”. For multidimensional this is a real problem as it finally result in the user seeing the whole cube! This is “by design” and can be explained as follows:
Role “Bikes” does not have any restriction on [Country] so all countries are visible, Role “DE” has no restriction on [ProductCategory] so all product categories are visible. As Roles are additive the user is allowed to see all countries and also all product categories, hence the whole cube:
Basically you would expect to see “Bikes”-sales for all countries and “Germany”-sales for all product categories but you end up seeing much more than this. If you have every faced this problem in real life you know that this is probably not the intended behavior your customers want to see. Usually Active Directory Groups are used and assigned to SSAS roles, so this can happen quite easily without anyone even noticing (except the user who is happy to see more 
)!
Chris Webb wrote an excellent blog post on how to deal with those kinds of security requirements in multidimensional models here.
For tabular this behavior is somehow similar. A user belonging to both roles is also allowed to see all countries and all product categories – this is because security settings are additive, same as for multidimensional. Even though this is true in terms of the structure (rows, columns) of the query we still get a different result in terms of values!
Here is the same query on a tabular model with the same security settings:
This is exactly what we and our customers would expect to see – all sales for “Germany” and also every sale related to “Bikes”! In tabular models security applied to a given table cascades down to all related tables – in this case to our fact table. If a table is indirectly secured by different Roles which put security on different tables those restrictions are combined using OR. In terms of SQL this could be expressed as:
- SELECT
- [ProductCategory],
- [Country],
- SUM([Reseller Sales])
- FROM <table>
- WHERE [ProductCategory] = 'Bikes'
- OR [Country] = 'Germany'
- GROUP BY
- [Product Category],
- [Country]
Further pivoting the result would show the same as the MDX query.
Thinking back to some of my multidimensional cubes where I had to deal with multiple Roles this “slight difference” would have been really handy and would have saved me a lot of time that I put into custom security solutions using bridge tables, assemblies, etc.
In my next post I will go into much more detail on how the tabular security model works so stay tuned!
UPDATE: Part 2 can be found here
Nice piece of info, Gerhard!!
Hello,
Thank you Gerhard for all this info.
However, i wasn’t able to solve an urgent problem. Please, advice.
I have a cube designed to be accessed by 18 different university.
When a user select the dimUniversity, we want him to access only the data related to his university.
For that, i implemented this :
{StrToMember(“[Université].[Univ Agglm Cd].&[” + UserName() + “]”)} on the SSAS role advanced tab.
(as suggested in this article : http://richardlees.blogspot.ca/2010/10/ssas-dynamic-security.html)
NB: The column ‘Univ Agglm Cd’ contains the equivalent of the username values.
that didn’t work. It gives me always all the 18 universities.
Please, advice.
Thank you.
well, could be a very trivial issue
If you test with your user by adding your user to that role your admin-rights will overwrite the dynamic security and you will see everything
in general if you have 18 universities you can only have 18 users – right?
have you tried testing the security using “EffectiveUsername” connection string property?
you may also use the “test cube security”-link from the [Cell Data]-tab and enter the Username that you want to test there by simply clicking on the first button on the top of the window that appears
-gerhard
If I have five different “silos” of data, any or all of which might be exposed to a user, do I need 25 roles to cover every possible combination, or is there an easier way?
Hi Morgan,
if it is only 25, then I would probably create 25 static roles. An alternative would be to use dynamic security (just google it) but this can also introduce a lot of complexity.
regards,
-gerhard
Hi,
I have a Tabular Model with Different roles level security (i.e Role_Admin, Role_HR, Role_Employees, etc) defined in the cube Role Level manager. There are multiple dashboards built that uses live connection to access the Tabular Model and there are situations where a single user is added to more than one dashboards. Now, when such a user logon to each dashboards, he/she is seeing all data based on the highest level of access that was granted.
Is there a way to explicitly specify what defined role level name for Power BI dashboard to use when connecting to the Tabular Model?
If that is not possible, are there other ways to specify what role name for Power BI report to use when it connects to the Tabular Model, whether at Desktop design level or in Power BI Service?
Please, i need urgent assistance with this.
Thank you!
Hi, not that I am aware of
the final permissions are always a super-set/union of all single permissions and there is no way to set this on a report-level in PowerBI.
Though, there is a connection-string parameter called “Roles” which can be used to explicitly specify a role that is used https://docs.microsoft.com/en-us/analysis-services/instances/connection-string-properties-analysis-services?view=asallproducts-allversions#roles
Depending on the client tool it may be possible to inject/hard-code this value but for Power BI I am not aware that this is possible
regards,
-gerhard