none
Best way to implement security for measures in SSAS

    Question

  • Hi SSAS gurus,

     

    I have a requirement where I need to implement security on some measures such that it is visible to a set of users and not accessible to another set of users. Now, I also have SSRS reports defined on top of these measures which are accessible by both the set of users. I want the reports to be displayed such that the measures should be visible to the users with access but invisible or N/A should be displayed when the other set of users access the reports. What would be the best way to go forward for this?


    Cheers, Jason 
    Monday, March 22, 2010 7:08 AM
    Moderator

Answers

  • Just implemented a solution and that works!!! Thanks a lot Raymond and Craig!

    The solution is actually a hybrid of Raymond's and my idea. The steps I took are

    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 for all keys and this attribute is hidden in the product dimension.

    2) I needed to have security access on the measure Gross Margin Original. In the calculated members of the cube, I made the following calculation

    create member CURRENTCUBE.[Measures].[Gross Margin] as 

    iif(iserror([Product].[RoleSecurity].[1]),0,[Measures].[Gross Margin Original]);

    3) I have 2 roles called RestrictedUsers 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 RestrictedUser role tries to view the GrossMargin 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".

    Drawback

    1) If the user becomes aware of the hidden measure called Gross Margin Original, he will be able to query it out

    I would love to hear further drawbacks from the experts, guess maybe I will make a new thread for it.


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Wednesday, March 24, 2010 11:17 AM
    Moderator
  • Hi Jason,

    Good idea! I heard it before. It should work. However, one little shortage is: you need to hide every related measure with another calculated measure, right? You can also do it will SCOPE. Anyway, you can try it. Also, you gave me one idea about the RoleCheck dimension. (I haven’t tried it yet). How about this:

     

    1)    Modify the fact table to add a dummy column: RoleCheck with a dummy number 1.

    2)    Create a dummy dimension table (RoleCheck) like this:

    RoleCheck

    1            

    3)    Create a dummy dimension base on RoleCheck, set RoleCheck as the key attribute.

    4)    Join the dimension to the measure group with key attribute (RoleCheck). So you can query the measure with RoleCheck.

    5)    Apply dimension data security on this dimension. For example, Role1 cannot access to RoleCheck, in Advanced tab, select the option ‘Enable Visual Totals’.

    6)    Test the cube, then all the measures belong to that measure group will be NULL. So, SSRS will not throw an error.

     

    The big shortage is: the lowest granularity is the measure group – I mean it will hide the whole group. But in a real word, generally, one user can access one measure, then he should access to other measure in the same group. Another thread about hide the measure with scope:

    http://social.msdn.microsoft.com/Forums/en-ZA/sqlanalysisservices/thread/d460a92b-606d-4109-bc6d-a062cc476ebb

     

    Hope this helps,

    Raymond

     

    Wednesday, March 24, 2010 3:20 AM
    Moderator

All replies

  • Come on guys, I am sure someone would have implemented this requirement.

    By the way, I am using SSAS 2008 and SSRS 2008


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Tuesday, March 23, 2010 2:34 AM
    Moderator
  • Hi Jason

    I believe you can do this with Cell Data security.

    1. Create a role in the SSAS Database and navigate to the "Cell Data" tab in the role designer.
    2. Check "Enable Read Permissions".
    3. You specify an MDX statement to determine if the role can access cells. An example is: "[Measures].CurrentMember IS [Measures].[Internet Sales Amount] OR
      [Measures].CurrentMember IS [Measures].[Internet Gross Profit]".
    4. Assign the relevant users to that role

    Hope this helps

    Regards,

    Craig

    Tuesday, March 23, 2010 2:53 AM
  • I have a requirement where I need to implement security on some measures such that it is visible to a set of users and not accessible to another set of users.

    I think there is no perfect solution to achieve that. In my opinion, since the users have different permissions, why do not create two reports for them, and then set permission at report server.

    Anyway, two kinds of way:

     

    You can use cell security as Craig suggested – achieve that at cube level. Then there is no problem within the report. But, Cell security has the biggest negative impact on query performance.

     

    If you want to use dimension security, then you have to achieve that at report level. The difficulty is dynamically Adding and Removing Columns. Generally, you can try the dynamic query with User!UserID:

    http://msdn.microsoft.com/en-us/library/aa237477(SQL.80).aspx

    http://www.purplefrogsystems.com/blog/?p=18

     

    Hope this helps,

    Raymond

     

    Tuesday, March 23, 2010 4:58 AM
    Moderator
  • Hi Craig,

    Thanks for your answer. The cell Data Security would have worked for me in normal scenarios and that is how I thought I will implement when I was gathering the requirements (even though I was aware of the performance issues that Raymond was referring to). But the issue is that I am using SSRS reports on top of it, and those reports will throw an Execution Error when a user who does not have access to the emasures executes the repor when my requirement is that it should show as #N/A or some other message like it shows in the cube browser :(

     

    Hi Raymond,

    From your views, it looks like the situation is much worse than I thought it was. I would have again gone with different reports for different users, but we have a html page where the link of the report is given. So then we need to write some piece of code which determines which group of user is accessing the report, and then link the appropriate report, is it possible? 

    And another drawback would be that we would have to hardcode the user groups, when what I want is that the roles of the cube should be used to check rather than hardcoding the windows group. Else, if we change the groups in the roles, then we will have to come back and change the html code also. Also, I have some Panorama views on top of the cube where the security needs to be applied, so I think I need to do something at the cube level itself.

    Maybe, it's time I need to check the dimension security feature, but I still haven;t got any clue how I can use it to work in my scenario.

     

    Guys,

    But I thought having measures security for different user groups is a common requirement. Or am I wrong? I would love to hear your views on this. Read a long time back somewhere on using SCOPE statements to implement security, though I don't quite remember how.

     


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Tuesday, March 23, 2010 5:16 AM
    Moderator
  • But I thought having measures security for different user groups is a common requirement. Or am I wrong? I would love to hear your views on this.

     

    Jason, as I know, this should be a Reporting Services issue. For other client tools (Excel), dimension security will work in most of case. But in SSRS, the fields in report must be fixed before running except dynamic query and store procedure. If cell security doesn’t meet your requirement, then you have to do more work on your report.

     

    Create a table in relationship database to maintain the group and users, and then create a hidden parameter to return the current user’s group (similar to cascading paramter). After that, use dynamic query base on the value of the hidden parameter to return the fields.

    I have struggled with this issue myself. So, any other good solution would be appreciated.

     

    Hope this helps,

    Raymond

    Tuesday, March 23, 2010 5:40 AM
    Moderator
  • Raymond,

     

    Creating and maintaining the user list would be cumbersome, is it not possible to use the built-in roles for this purpose somehow, something like the below technique?

    1) Have a dummy attribute in some dimension called RoleCheck.

    2) Apply dimension security on it such that Role1 (the same role that needs access to the measures)  has access to it and Role2 doesnot

    3) Lets say the measure that needs the security to be applied on it is [Measures].[Profit]. Hide that measure and create a calculated measure like below

    Create [Measures].[RoleProfit] as iif(iserror(([Dim].[RoleCheck].[All],[Measures].[Profit])),NULL,[Measures].[Profit]);

    I expect it to show null when the user doesn't have access and the actual measure when the user has access. Also, we will be using dimension security, which according to my understanding, would not be having performance issues like cell security.

     

    Guys, please let me know the drawbacks and potential issues of this technique, or a better technique.


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Tuesday, March 23, 2010 6:56 AM
    Moderator
  • Hi Jason,

    Good idea! I heard it before. It should work. However, one little shortage is: you need to hide every related measure with another calculated measure, right? You can also do it will SCOPE. Anyway, you can try it. Also, you gave me one idea about the RoleCheck dimension. (I haven’t tried it yet). How about this:

     

    1)    Modify the fact table to add a dummy column: RoleCheck with a dummy number 1.

    2)    Create a dummy dimension table (RoleCheck) like this:

    RoleCheck

    1            

    3)    Create a dummy dimension base on RoleCheck, set RoleCheck as the key attribute.

    4)    Join the dimension to the measure group with key attribute (RoleCheck). So you can query the measure with RoleCheck.

    5)    Apply dimension data security on this dimension. For example, Role1 cannot access to RoleCheck, in Advanced tab, select the option ‘Enable Visual Totals’.

    6)    Test the cube, then all the measures belong to that measure group will be NULL. So, SSRS will not throw an error.

     

    The big shortage is: the lowest granularity is the measure group – I mean it will hide the whole group. But in a real word, generally, one user can access one measure, then he should access to other measure in the same group. Another thread about hide the measure with scope:

    http://social.msdn.microsoft.com/Forums/en-ZA/sqlanalysisservices/thread/d460a92b-606d-4109-bc6d-a062cc476ebb

     

    Hope this helps,

    Raymond

     

    Wednesday, March 24, 2010 3:20 AM
    Moderator
  • Awesome! I will just try that out and let you know, sounds like a good idea to me.

    One doubt though, I am assuming that the performance wont suffer much in dimension security as it does in cell security, am I right to assume that?


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Wednesday, March 24, 2010 3:34 AM
    Moderator
  • Guys i know this is out of the topic.
    But when enabling these permissions is it possible to look up roles or sers from 2 different domain(active directory?

    Thanks

    Wednesday, March 24, 2010 10:18 AM
  • Just implemented a solution and that works!!! Thanks a lot Raymond and Craig!

    The solution is actually a hybrid of Raymond's and my idea. The steps I took are

    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 for all keys and this attribute is hidden in the product dimension.

    2) I needed to have security access on the measure Gross Margin Original. In the calculated members of the cube, I made the following calculation

    create member CURRENTCUBE.[Measures].[Gross Margin] as 

    iif(iserror([Product].[RoleSecurity].[1]),0,[Measures].[Gross Margin Original]);

    3) I have 2 roles called RestrictedUsers 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 RestrictedUser role tries to view the GrossMargin 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".

    Drawback

    1) If the user becomes aware of the hidden measure called Gross Margin Original, he will be able to query it out

    I would love to hear further drawbacks from the experts, guess maybe I will make a new thread for it.


    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Wednesday, March 24, 2010 11:17 AM
    Moderator
  • Hello Jason,

    I followed the steps to implement the measure security usind the dummy attribute.

    This works fien when I browse the cube using Management Studio BUT the measure data comes in Excel 2007 and even in Report builder.  I can't figure out at this this point where to look.

    My platform is SSAS 2008 on Windows 2003 Server SP2 with Excel 2007 and Report Builder 2.0.

    Awaiting a positive response,

    Tawfiq


    Moradhun
    Wednesday, September 22, 2010 6:06 AM
  • Hi Moradhun,

    If measure security  is enabled, data should not come but Measure should only be visible. Excel , RB are all boxed application which we do not have control. 

    We implemented a different version of same and in Excel measures do not show up.. But check it out before implementing  it in Production.  http://consultguru.me/post/2011/08/06/Measures-Security-in-SQL-Server-2008-R2.aspx

    -Guru

     

    Sunday, August 7, 2011 7:08 AM
  • Hi Jason,

     

    I am sure, this thread can be alive again.

     

    In your solution, you have achaived that when RestrictedUser role tries to view GrossMargin measure, he will see 0.

     

    But my requirement is, the RestrictedUser role should not be able to see the GrossMargin measure at first place, forget seeing 0 or N/A.

     

    Pls note - I want the measure name itself, not to be shown to restricted users.

     

    Pls advice.

     

    Sub

    Sunday, August 7, 2011 1:57 PM
  • Hi Sub,

    Have you reviewed content that post I referred above

    http://consultguru.me/post/2011/08/06/Measures-Security-in-SQL-Server-2008-R2.aspx

     

    It does not show measure name as well. Try that and let me know of any implementation problems. 

     

    Guru

    Sunday, August 7, 2011 3:30 PM
  • Hi Sub,

     

    If you want to make the calculated measures invisible, then you will have to convert them to base measures as given in this post - http://sqlblog.com/blogs/alberto_ferrari/archive/2009/02/05/measure-tool.aspx

    I think the same approach is followed by Guru also, so both of them should work. 

     

    A possible disadvantage of this approach would be that it would be working for only that measure. In case you have some calculated measures on top of it, you will either have to

    a) write scope statements for each of them so that they don't have #VALUE displayed in case the user doesnt have access

    b) make each of them base measures, which is not a very flexible/nice approach


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Tuesday, August 9, 2011 10:58 AM
    Moderator
  • One think that could be done if cube is small and be fully processed is.. 

    Create a table as below

    Create table CalculatedMeasures(DummyKey int, CalculatedMeasureName varchar(100))

    Insert as rows all Calculated Measures and 

    Dummy Key  Calculated Measure

    1 % Agree

    1 % Approval

    1 % Referenced

     

    Create a Named Query DSV only for Calculated Measure Group that does pivot of this table (Converting Rows to columns) and then writing Scope expression. 

    1 , % Agree, % Approval, % Reference. 

    This named query should pickup on any new row added to this measure table and then we write scope expression for new added base (Calculated) measure.

    Disadvantages could be:

    1. Since we are converting Calculated to base measures, processing of cubes needs to be done each time. Works for smaller cubes which can be fully processed.

    2. Again if a new calculated measure is needed, we would not be able to get it into cube unless processing is done.

    -Guru

     

    Tuesday, August 9, 2011 3:26 PM
  • Hello Guru,Jason

     

    Thanks for your response again.

    I did implement the steps given in consultguru, and now I have all my physcial measure and scope set up.

    But problem is , how do I show these maesures dynamically based on users.

    Example -

    User 1 can see only Measure M1,M2,M3

    User 2 can see only Measure M2,M5,M7,M8

    User 3 can see only Measure M3,M8, M20

    and so on..

     

    I have a dimension - DimOrgUsers. I want to create only one role, and define access to the measures using MDX dynamically,

    Pls note that, I have already defined security of my other dimesnion (geography,kpi) based on dimuser and bridge table approach. Now I just need to define dynamic security on the different measures M1,M2 etc. based on users. I do not want the measures to be visible at all to the end client.

    Pls advice how this can be done

     

    Regards,

    Sub

     



    • Edited by Sub.etc Saturday, August 13, 2011 4:56 PM more details given
    Saturday, August 13, 2011 4:51 PM
  • HiSub, 

    Have you used http://www.consultguru.me/post/2011/08/06/Measures-Security-in-SQL-Server-2008-R2.aspx (earlier mentioned) to convert calculated measures to base measures. Once this is done there would be [CalculatedMeasure] table. Build a fact table with CalculatedMeasure and Users Keys indicating this user has access to these Calculated Measures. 

     

    Once that plumbing is done, then for Dynamic security for same role along with Dimension security, scroll down to cube and under that for Measure Dimension write tuple set 

     

    (CalculatedMeasures.Members, Users.UserName) 

     

    Ofcourse I have not tried this, our security was implemented through .net Assembly, so we directly quried table without brining them into cube. 

     

    Let me know if you need further help,I can try and let you know.

     

    Thanks

    Guru

     

     


    Guru | www.ConsultGuru.Me
    Wednesday, August 17, 2011 3:01 PM
  • Hello,

    The method that you presented can be improved.

     

    Instead of implementing a dummy dimension or atribute, you could just add a dummy measure and since it is a cube measure you can filter it on the dimension data on the security role.

     

    Then like you did you use the calculated measure the same way.

    For example

    IIF(ISERROR([Measures].[Security]), null, [Measures].[Profit])

    I think it is much more intuitive.

     

    Hope this helps for something


    Nuno Ferreira
    Friday, January 6, 2012 3:37 PM
  • " 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. "

    Jason, is the issue above fixed by MS?

    Thursday, July 5, 2012 1:29 AM
  • I am not really sure Bruno, as I havent tested it out with the new release. Why, are you getting an issue with the new environment?

    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, July 5, 2012 2:47 PM
    Moderator
  • Hello Jason,

    I hope we can make this therad alive again.

    I have something similar problem. I have dimension group, that want to denied the acccess to all users, but that measure group should be accessible for few users. I am new to SSAS. Any help is appreciated. Thanks.

    -Punia

    Thursday, March 14, 2013 7:16 PM
  • Is it possible to change #3 to filter by measures that are not = something?  I have many measures, but only want to filter out a couple of them to all but management level users.  If so, what would the MDX look like?
    Thursday, February 4, 2016 6:02 PM
  • Hello,

    Instead of implementing a dummy dimension or atribute, you could just add a dummy measure and since it is a cube measure you can filter it on the dimension data on the security role.

    Then like you did you use the calculated measure the same way.

    For example

    IIF(ISERROR([Measures].[Security]), null, [Measures].[Profit])

    I think it is much more intuitive.

    Hope this helps for something


    Nuno Ferreira

    Friday, July 1, 2016 9:34 AM