locked
Data level security issues RRS feed

  • Question

  • Hello,

    I have a working SSAS cube. I implemented data level security on his cube by using 2 tables that join to the dimension tables. One is called users (list of all users) and assignments (shows which data the user has access to). The 3 levels of security are Country, Agency and Media Type. So, for example, if we have a user named John Doe we can assign him all of the Agencies and Media Types in the USA. So John Doe should only be able to see the country USA.

    Let me try to explain my issue...

    We are using excel and Performance point to access the cube. When we open the reports, using our John Doe example, we can see all of the countries, BUT the data security is working because we can only see data for the USA. Is there a property or something that I can change that will prevent this from happening? I need John Doe to only see countries that he has access to, and not see all countries but only see data in the countries that he has access to.

    Thank you all in advance and please let me know if I was clear enough.

    Dave


    Dave SQL Developer

    Friday, July 27, 2012 11:49 PM

Answers

  • Rather than replying to this thread, you may want to start a new thread and reference this one. You're much more likely to get someone else to respond when no one has yet replied.

    http://martinmason.wordpress.com

    • Marked as answer by DaveDB Monday, August 20, 2012 6:33 PM
    Tuesday, July 31, 2012 7:32 PM

All replies

  • There's a couple of ways to do so and probably none of them are all that good. You could create roles and use an Allow Set on the cube dimension to specify which countries should be displayed. You'd have to create a separate role for each possible case.

    I believe there's other ways to do so by creating a custom assembly and overriding the SessionOpen or CubeOpen events. I'm finding a hard time finding an example of that approach though.

    Martin


    http://martinmason.wordpress.com

    Saturday, July 28, 2012 12:07 PM
  • Hello Martin,

    Thank you for your response. I truly find it hard to believe that I have to go through all of this just to ensure that a user can only see the countries that he has access to when the data level security that I created works. Perhaps you are over thinking this or I wasn't clear enough.

    Lets break this down.... Lets say that my cube only has country level security. John Doe has access to USA data only. When John Doe logs into excel or Performance point he can see all of the countries but only the data from USA. I need John Doe to only see what countries he has access to.

    I was talking to one of the SSAS experts here at my office and he said that there is a property available that can prevent this from happening, but he doesn't remember where it is. So, I was hoping someone on this forum would know.

    Thanks again for your response Martin.

    Dave


    Dave SQL Developer

    Monday, July 30, 2012 1:55 PM
  • Keeping it really simple, does the following satisfy your requirements?

    • Create a new role in the database, USA User.
    • Grant the role access to your cube.
    • On the Dimension Data tab of the role editor, select the Country cube dimension (not the database dimension as both will be shown, though you may also want to restrict access on the database dimension)
    • Select the "Deselect All Members" option on the right and check the USA member so only the USA member will be displayed.

    Thought you had a requirement for cross-dimensional access requirements and that requirement is much harder to solve.

    HTH, Martin


    http://martinmason.wordpress.com

    Monday, July 30, 2012 2:13 PM
  • Hi Martin,

    Unfortunately, I have dynamic data level security and the privileges are assigned through the tables and not simply by going through the roles and manually changing them. We have over 200 users at this time, so this approach would not work. I wish it would be this easy :)

    Thank you again


    Dave SQL Developer

    Monday, July 30, 2012 3:15 PM
  • Hi Dave,

    You're probably going to be best off addressing your requirements by creating an assembly that handles the SessionOpened event of the Microsoft.AnalysisServices.AdoMdServer.Server object http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.server.sessionopened or the CubeOpened event of the Microsoft.AnalysisServices.AdoMdServer.AdoMdConnection object. http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.adomdconnection.cubeopened.aspx

    Unfortunately, it's just not going to be that easy. Alternatively, you could define a single role that calls an SSAS stored procedure to return back a set of the allowed members. Either way, I don't any way to address without building a custom .NET assembly, at least with Excel.

    With PPS, you could possibly use a MDX query to create a filter that only returns NonEmpty members based on a selected measure assuming you're using Per User Security. SSRS would address the situation the same way. Excel, though, there's no way I can think of where you could address without a custom assembly.

    Martin


    http://martinmason.wordpress.com

    Monday, July 30, 2012 5:15 PM
  • Thanks for your help. I will look into this.

    Dave


    Dave SQL Developer

    Tuesday, July 31, 2012 3:41 PM
  • Does anyone else have any ideas? Is what Martin proposing the only option for this? I am surprised that only 1 person replied to this post. Is that because this is the answer?

    Thanks


    Dave SQL Developer

    Tuesday, July 31, 2012 6:16 PM
  • Rather than replying to this thread, you may want to start a new thread and reference this one. You're much more likely to get someone else to respond when no one has yet replied.

    http://martinmason.wordpress.com

    • Marked as answer by DaveDB Monday, August 20, 2012 6:33 PM
    Tuesday, July 31, 2012 7:32 PM
  • Hi Martin,

    I used your assembly approach and it worked!

    Thanks


    Dave SQL Developer

    Monday, August 20, 2012 6:33 PM
  • Hi Dave,

    I have the exact requirement as yours. but having hard time to get the solution. Can you share the approach and steps if possible as to how you accomplished this?

    Wednesday, October 31, 2012 5:35 AM