Benny Austin

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

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

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

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

 

Benny Austin

About these ads

17 comments on “SSAS: Membership in Multiple Roles Could Throw Unexpected Surprises

  1. Joseph Fallon
    November 12, 2010

    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.

  2. Pingback: SSAS: Using AMO to Secure Analysis Service Cube « Benny Austin

  3. Tom Pollack
    October 19, 2012

    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!!

  4. Pavan Barnala
    October 25, 2012

    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.

  5. jvd
    October 23, 2013

    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

    • Benny Austin
      October 26, 2013

      hi there,
      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.

  6. jvd
    October 29, 2013

    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.

    • Benny Austin
      October 30, 2013

      Thanks for the tip. That’s probably the way to go.

  7. Jonathan R.
    November 20, 2013

    Hello Benny,
    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 ?

    • Benny Austin
      November 21, 2013

      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.

      • Jonathan R.
        November 21, 2013

        Thank you, i’ll try it this way !

  8. Jay Kshirsagar
    March 1, 2014

    Hi Benny,

    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?

    • Benny Austin
      March 1, 2014

      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

      • Jay Kshirsagar
        March 4, 2014

        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.

      • Benny Austin
        March 6, 2014

        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.

      • Benny Austin
        March 12, 2014

        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 <>

      • Jay Kshirsagar
        March 14, 2014

        Thanks Benny. Your article and help was really helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 172 other followers

%d bloggers like this: