locked
Dimension Security Question RRS feed

  • Question

  • Hi, can anyone tell me if it is possible to have a dimension which has dynamic security implemented for only certain rows,

    My dimension (DimTrade) has 5m members, and each member is assigned an attribute called "SecurityLevel" (values are 1 (unsecure) to 5 (super secure)) User/Dynamic security has to be implemented for members which have a securitylevel of 3,4,5, (by implemting a bridge table to join DimUser to DimTrade) but security levels 1 and 2 are open to all cube users and therefore don't need any security other than a user being in the AD group to access the SSAS database.

    I would like to create a SSAS multidimensional cube (SQL Server 2014 Enterprise) to roll this feature out, but need to know if and how this can be achieved. I don't want to break the DimTrade dimension into 2 separate dimensions based on the SecurityLevel of members.

    Thanks, David

    Thursday, March 2, 2017 6:57 AM

Answers

  • This is certainly doable, and there are several options available. You could define an allowed set of members as (granted through bridge UNION having security level 1 or 2). You could define a denied set as (not granted through bridge AND having security level 3-5). If  you'd rather have the set defined in terms of bridge table only, you could include all your security level 1 and 2 members in the bridge table, or you could abuse the additive nature of the SSAS security model and add another role for all users that explicitly grants access to security level 1-2 members.

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, March 2, 2017 7:26 AM

All replies

  • This is certainly doable, and there are several options available. You could define an allowed set of members as (granted through bridge UNION having security level 1 or 2). You could define a denied set as (not granted through bridge AND having security level 3-5). If  you'd rather have the set defined in terms of bridge table only, you could include all your security level 1 and 2 members in the bridge table, or you could abuse the additive nature of the SSAS security model and add another role for all users that explicitly grants access to security level 1-2 members.

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, March 2, 2017 7:26 AM
  • Hi David,

    Thanks for your question.

    I agree with Alexei,you may want to get users with securitylevel of 3,4,5.
    To get the users with securitylevel of 3,4 and 5, please try following MDX expression:

    exists([DimUser].[UserName].[UserName],{[Dim Trade].[SecurityLevel].&[3],
    [Dim Trade].[SecurityLevel].&[4],
    [Dim Trade].[SecurityLevel].&[5]})

    Then you can mapping users to the detailed secured data. To map users to the detailed secured data,please refer to SSAS : Setup dynamic dimension security in Analysis Services .

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 2, 2017 8:54 AM
  • Hi Willson,

    Why do you expect the set returned by the MDX you suggested,

    exists([DimUser].[UserName].[UserName],{[Dim Trade].[SecurityLevel].&[3], [Dim Trade].[SecurityLevel].&[4], [Dim Trade].[SecurityLevel].&[5]})

    to be any different than plain

    [DimUser].[UserName].[UserName]?

    Actually, why would a set of users be at all required in this situation?


    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, March 2, 2017 9:52 AM
  • Thanks for your responses - appreciated One fact that I should have said in my OP is that the bridge table will Only include users mapped to levels 3,4,5 trades - levels 1&2 will not have mapping data in the bridge table (as all authenticated users should have access to all records at these levels). Does this change the proposed solution guys? Thanks David
    Thursday, March 2, 2017 9:56 AM
  • This just makes one of the four listed options unsuitable, so you still have three more to choose from.

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, March 2, 2017 10:52 AM
  • Quite right, thanks Alexei- 3rd option is out (explicit join of user to levels 1-2). Will use the union suggestion and accept your suggestions, thank you very much...
    Thursday, March 2, 2017 11:53 AM
  • Hi David,

    Please kindly mark the solution provided by Alexei as an answer.By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 3, 2017 7:27 AM