Permission Scope in Analysis Services

This is my first post i a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this new blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This one is going to be about permission scope in Analysis Services (SSAS).

What this post will not be about: The how to setup basic dimension security in SSAS nor How do you manage Security.

In this post, I will highlight the difference between standard NTFS permission scope and the way SSAS handles Allowed and Denied sets when dealing with multiple roles. So if you define multiple roles on your solution, you should be on the lookout, because SSAS has some surprises.

NTFS

In NTFS, permissions are defined so that deny generally takes precedence over allow. As illustrated in below diagram, we can define an Allowed set as a sub set of the total set. The blue rectangle is to be seen as the full set.

NTFS Allowed SetAnd as we apply a Denied set, this will restrict the previous Allowed sub set, the intersection of the two sets, illustrated below: (The color indicates the finally allowed set.)

NTFS Denied Set

 SSAS

In SSAS however, the Allowed set takes precedence over Denied. So if you apply your usual NTFS logic to your dimensional security in SSAS, you may well be in for a surprise.

When no security is defined in a cube, everything is accessible to everyone. This color indicates the allowed set. (the complete rectangle)

Entire Cube Space

As soon as you introduce the first role, the cube is locked down to everyone not a member of said role.

Role w/ Allowed Set Introduced

If you then introduce restrictions in another role, you will get a different result than in the NTFS based security. The members of the role will still be able to see the full set of the Allowed set. Even though we just Denied that!

Role w/ Denied Set Introduced

Example

By creating two test roles, we can easily bring forward the, to some extend, unexpected behavior of SSAS.

Allowed Set Defined
Allowed Set Defined

As seen in Role Test 1, I have defined the Allowed set to contain Weeks 41 and 42 of the Year 2015. By browsing the Cube through SQL Server Management Studio, we can identify the Allowed set is working:

Allowed OK
Allowed OK

As I the introduce a new Role Test 2, and in that denying the week 42 of 2015, I would expect the browsing result to be only displaying only Week 41, but…

Denied Set
Denied Set

The result we get, when browsing the cube using both roles, shows all dates with data: (WTF! – Yes, you said it!)

Denied Set actually allowing everything?
Denied Set actually allowing everything!?

Fix

Clearly that was not the intention, by denying Week 42. So, how to fix the above violation to the Allowed set?

By adding an empty Allowed set ‘{}’ to the role containing the Denied set, in this case Test 2, as depicted below:

With this Empty Set ‘{}’ in place, we can browse the Allowed set again, but the Denied set does not restrict the result set.

Allow-Denied-Meh

Final Thoughts

While the way SSAS interprets the Allowed and Denied Sets may be straight forward, for simple security tasks. I think the above examples shows just how much testing one needs to do, in order to be totally sure the desired permissions are reflected in the end user experience.

As a side note, adding an empty set to the Denied Set of the first Role (Test1) does not alter the outcome.

 

Loading

One thought on “Permission Scope in Analysis Services

  1. Pingback: Testing Your #SSAS Cubes w/ Visual Studio – T-SQL.dk

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.