locked
How do I implement dynamic security for the users? RRS feed

  • General discussion

  • [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]

    Answer:
    There is a dimension Reseller in our cube, and we can create roles to grant each user (Reseller) access to the cube to let them see their own sales data. However, if there is a large number of users, then we need to create lots of roles. Even if you can create so many roles, it’s still hard for maintenance. But, we can implement dynamic dimension security for each reseller. You can try the steps below:

    1. Create a Users table containing the users in the underlying database. Create a dimension named User from this table.
     
    2. Create a Factless table to maintain the relationship between the users and the Resellers:
    users | Resellers
    user1 ResellerA
    user1 ResellerB
    user2 ResellerB

    After that, create a measure group base on this table.
     
    3. The relationship in DSV is similar like this:
    User <- Factless -> Reseller <- fact table
    So, create regular relationship between the two dimensions and the Factless measure group.

    4. Create a role for the users, in Dimension Data tab, select the attribute Reseller in dimension Reseller, switch to Advanced tab, the allowed member set should be:
    NonEmpty(
    Reseller.Reseller.Members,
    (
    StrToMember("[Users].[User].["+UserName()+"]"),
    [Measures].[Bridge Reseller Users Count]
    )
    )
     
    5. Test the role in your cube browser, if it works then deny access to the dimension user and the factless measure group.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, June 22, 2010 9:28 AM

All replies

  • Hi,

    will this work for non-windows authorization as described here? http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/836a4d89-cfed-4f81-ae42-1f188d601454 ?

     

     

    Tuesday, December 28, 2010 4:47 PM
  • this is what I need, but when I try to implement this solution, the error raise out . there are 3 cubes , one entity cube , tow link cube . when I put the script into SSAS database dimension ,the below error was raise out, but when I put the script into Cube dimension or mdx query studio ,it's work fine .I think many people encounter this question , an idea

    EXISTS(


           [Area].[Id].[Id].members,


        STRTOSET("[Dim User].[User Key].["+Username+"]"),


        'Fact Reseller User'

        )

    the error :

    The browser is disabled because custom MDX expressions defined in the Advanced tab were not validated due to the following problem:

    分析字符串 [Dim User].[User Key].[PHARMA\E0159192] 时在多维数据集中找不到“[Dim User]”维度。

    actually , this is not work when I put this cript in my link cube dimension

    Wednesday, July 11, 2012 2:14 AM