Implementing measure security in SSAS 2008
Reposted from Jason Thomas blog with the author's permission.
Recently, I was asked to implement security on a set of sensitive measures, such as profit and all calculated measures on top of them as part of the cube development. It looked like a very straight requirement and I never thought I would have so many sleepless nights over it for the next 2 months.
My very first way of thinking was using the default Microsoft cell security, where I would define a read-contingent policy for the sensitive measures. If you do not define an explicit read-contingent policy, the role will be able to read the derived measures of the sensitive Profit measure despite the fact that it does not have access to the Profit measure.
This led to 3 main problems for me:-
1) Reports which are running normal under the PowerUser role go berserk when running under the restricted RegularUser role. Empty rows started appearing in spite of having NON EMPTY in the MDX queries. Currently, I am following this issue with Microsoft and this is the latest reply I have got
" We have figured out the problem is caused by the cell security in the cube. When you set the "Enable read permissions" in the cell data, the non empty function doesn’t work for the special role. All the null records still display even you are using non empty. For the admin role, the non empty can filter out all the null records. For example, when you query the cube by the scripts below, you can get 42 records without null by PowUsers. If you are using the role RegUsers, you can get 132 with a lot of null. "
2) Performance went for a toss, and reports run under the RegularUser role started taking much longer time than when it was run under the PowerUser role. Later, I found that this could be mitigated with a technique given in this book (a definite must read for Expert cube designers, gives the inner details of SSAS like no other book has given) which I will try to put in another blog.
3) Even though the restricted measures were showing as #N/A in the cube under the RegularUsers, the reports started throwing an error when I just expected the #N/A again. There was a requirement for a report which had the sensitive data along with some 5 other tables and all other data needed to be visible to the RegularUser.
So time for a workaround. Luckily, I got guidance from Raymond Lee, MSDN forum moderator and came up with this approach.
1) I already had a dimension called Product. Added a dummy attribute to the key of Product dimension, and called it as RoleSecurity. The value of this attribute would be 1 and this attribute is hidden in the product dimension.
2) I needed to have security access on the measure Profit. I renamed that measure to Profit Original and made it hidden. Then, in the calculated members of the cube, I created the following calculated member
CREATE MEMBER CURRENTCUBE.[Measures].[Profit] AS IIF( ISERROR ([Product].[RoleSecurity].[1]), 0, [Measures].[Profit Original]);
3) I have 2 roles called RegularUsers and PowerUsers. PowerUsers have access to all measures, so no need to do anything there. For the RestrictedUsers role, select the Dimension Data tab and then use the "Deselect all members" for the RoleSecurity attribute. This implies that the users of this role will not have access to any of the values of this attribute.
Once this is deployed, if a user of RegularUser role tries to view the Profit measure, he will see 0 as the result, while the PowerUsers will see the actual value. In the reports, the 0 can be used to conditionally display a message like "Not authorized" if needed.
RegularUser PowerUser
The advantages of this technique is that
1) Since dimension security is used, it doesn't affect performance like cell security does.
2) Reports will not throw up error even if the measure is missing.
3)Most importantly, the issue of null rows is solved
The drawbacks are
1) The Profit Original measure is not secured, jut hidden. If the user becomes aware of the measure and also knows how to use MDX well, he will be able to query it out.
These are the advantages and disadvantages I can think of. In any case, it solved my issue because my business users don’t know MDX and will not be using any tool to query. It would be great if I get to know your views on this technique.
Here is the link for the forum post regarding this.
 |
Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients like Unilever, Imperial Tobacco, IATA, Schneider Electric, etc. He is currently working at MindTree Ltd, Bangalore (www.mindtree.com) and his personal blog site can be found at http://road-blogs.blogspot.com His blogs can also be found at the popular technical site BeyondRelational.com at http://beyondrelational.com/blogs/jason |