SSAS: Secure Analysis Service Cube (Part-4)

SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises

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. 

Screen Capture 1 - Australia SalesRep Role

Another role called Deny CustomerInfo is created to deny access to customer data for privacy reasons as shown in Screen Capture 2. 

Screen Capture 2 - Deny CustomerInfo Role

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. 

Screen Capture 3 - Additive Nature of SSAS Roles

• 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).

Screen Capture 4 - Australian SalesRep Role with no access to Customer Data