Analysis Service supports role based model to secure cube data. Data can be secured at object level, cell level (Cell Data Access) and at member level (Dimension Data Access). There are relative merits of each approach, which isn’t the topic of this post but more details can be found here. A user can be a member of more than one role and it is important to understand the behaviour of Analysis Services role permissions in such a scenario. Analysis Services role permissions are additive in nature. This means if you are a member of a role that has been granted access to an Analysis Service object; you have access to that object even if you belong to another role which denies access to the same object. In other word the least restrictive roles permissions override the most restrictive role permissions. This could throw some nasty surprises that you never expected.
To illustrate this, I’m using Dimension Data Access method on AdventureWorks cube.
Allow Country Data but Deny Customer Info
A role called Australian SalesRep is created for the Sales Office in Australia and this role has been granted access only to the Australia member of the Sales Territory Dimension as shown in Screen Capture 1.
Another role called Deny CustomerInfo is created to deny access to customer data for privacy reasons as shown in Screen Capture 2.
Let’s say certain users in Australian Sales Office are required to have access to their sales data but no access to their customer data, so these users are granted access to both Australian SalesRep and Deny CustomerInfo roles. You would expect these users to have access to Australia’s Sales data but no drill-down to customer data. Now, let’s see what happens.
- The result in screen capture 3 will drive you nuts. It shows Sales data from all countries and for all customers! At the outset it may seem that the roles are not taking effect, but this is what happens and it is important to understand why this happens.
The reason you are able to see all customers is because the Australian SalesRep secures the Sales Territory but not the customers. So even though Deny CustomerInfo denies access to Customer data, the additive nature of Australian SalesRep role overrides the restrictive role of Deny CustomerInfo on customer data.
- So why are you able to see sales data from all the countries? That’s because the Deny CustomerInfo secures the Customer data and not the Sales Territory data. So the combined effect is Deny CustomerInfo overrides the restrictive role of Australian SalesRep on Sales Territory.
As you can see creating multiple roles and granting users memberships to multiple roles does not always solve the security requirement. In fact, if not properly understood and implemented this can create serious security violations. In this instance, the requirement can be satisfied my amending the role definition of Australian SalesRep to deny access to customer dimension, thereby eliminating the need for Deny CustomerInfo role (Screen Capture 4).
18 thoughts on “SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises”
I am SSAS newbie. I am in trouble with roles security. I want to create a role for restricting normal users to see customers for one region and another one for Managers of corporation who have access to all customers for his corporation in all regions. Manager is supposed to see all customers in region which he belongs to and customers of corporation which he is managing in all regions. Region and Corporation code are attributes of separate dimensions. Do you any suggestions?
Jay, you could create 2 roles – one for normal user and one for manager and at any point assign a user to only one role. The manager role should include all members of region that he can see for the corporation. Whereas the normal user role should include only the region that they belong to in the corporation
Thanks for your respone.
Manager is not supposed to see all members of every region.
For eg. ABC company has offices all over world. So manager who is in US region is supposed to see all memebers of ABC company and also all memebers US region but he is not allowed to see members of regions other than US which does not belong to ABC company.
US: ABC:cust1 , XYZ:cust2, ABC:cust3, ASD:cust4
ASIA: ABC:cust5, XYZ:cust6
EURO: ABC:cust7, ASD:cust8
In this case manager will be able to see cust 1,2,3,4,5 and 7.
Agian thank you for your help.
Jay, based on the details you provided dimension data access my not work in this particular scenario as the conditions to secure members contradict between company and region.. Try cell data access instead.
Jay, if you use cell data access your MDX expression to secure the cells would be something like this – [Company].[Attribute].CurrentMember IS <> OR [Region].[Attribute].CurrentMember IS <>
Thanks Benny. Your article and help was really helpful.
I’m in trouble with dynamic security, there are 2 dimension Userdepartment and Cost center in the cube to secure and the aim is to give manager the possibility to see both the reporting when department or cost center is equal to their department all other report must be unreachable. I tried creating two roles applying jvd’s tips but manager still get full access to the cube. Do you have any suggestion to this problem ?
Jonathon, this is a typical scenario where membership in multiple roles will not work and is not recommended. Instead create one role per department and secure both the cost center and user department dimensions on their respective department member. In this way users of a department can drill-through to their data but not other departments. By default the managers would still be able to see the overall total of all other departments but can’t drill through except their own department. jvd’s method works if you want to enable visual totals AND both the roles secure the same dimensions on the same member.
Thank you, i’ll try it this way !
Benny i was just mentioning general case if we require to enable visual total for two dimension then in that case we will face problem and unable to see the measure..
Do you know how to avoid this in one role?
What I have did is
create two role each for a dimension then in one role restrict one dimension and unselect all member for other dimension and enable visual total.
Same thing i did in the other role for other dimension.
Thanks for the tip. That’s probably the way to go.
Benny can you enable visual tool for both the dimension and check the measure.i think in that case you will not able to see any measure. this issue I was facing. so to avoid this i create two role
In this case there is no need to enable visual totals on customer attribute since the role does not have access to any member. I tried enabling visual totals on Sales Territory Country and it works as expected. The measures did show up.
Yes, this is actually a pain point in security implementation. In the example given above, we have to implement second role definition in the first role as well and vice versa so that when union of these 2 roles happen, you get the least privilege. I did not know that roles are additive and not intersect based until i had implemented otherwise and realized my mistake.
Thank you Benny for an explanation. I went nuts for more than a week trying to figure out the problem with members belong to multiple groups!!
Nice article Benny.
Thought provoking .. I’m an SSAS newbie and was going to use multiple roles to control denying access to Dimension Data but it looks like that won’t work so well.