locked
Dynamic Dimension Security in the Cube RRS feed

  • Question

  • Hello friends,

                         I have a Scenario like this

    2 fact tables(Sales, Revenue) and 1 dimension table(Rpt lvl)

    I want to implement a  dynamic security on Departments so that particular department user can only see his department sales and revunue totals.

    Step1: I have created 2 tables one is Users( with user id) and Dept Security (M2M relationship b/w users and Depts).

    Step2 :Created a users_dim  , Dept_sec_fact (bridge table-factless fact table) in Sales cube (as seen in  the fig)

    Step3: Created a role with no members in it and  in Rpt_lvl -->Dimension security advanced tab-->Allowed member set--> MDX script

                   NonEmpty(
    [REPORT LEVEL].[DEPT NO].Members,
    (
    StrToMember ("[USERS].[USER ID].["+ Right(UserName, Len(UserName) - Instr(UserName, "\"))+ "]"),
    [Measures].[DEPT SEC FACT Count]
    )
    )

    After processing the cube when I log In as a diff user I am seeing all the depts which he does not have access.

    In database Joehogan has access to AAA00 only but when I drag Dept no he is able to see all 30 depts.

    Do I miss any logic?

    Thursday, January 12, 2012 9:04 PM

Answers

  • Yes thats correct.  Theres usually an issue with the person being an machine local admin also, but i will assume she is not a local admin.

    The users donot have to be a member of the admin role to access the cube via excel.  A standard role will suffice, infact, if they are a member of admin, any other secuiryt profile will be redundant. 

    You just have to give the nonadmin role access to the cube and then specify the security as we have already done.  As you have done, role membership can be through a domain role.

    Remember also that allowed access is acumulative so, it will increment security privalages for all roles that the user is associated with.

    We may be getting closer!

    HTH

    Paul

    paultebraak.wordpress.com

    • Marked as answer by Shilpa-ssas Thursday, January 19, 2012 8:27 PM
    Thursday, January 19, 2012 1:37 AM

All replies

  • Hi Shilpa,

    The schema looks ok, so i think its your statement.  Does it restrict the rows with a hardcoded name?

    I would try an expression like;

    Exists
    (
    	[REPORT LEVEL].[DEPT NO].Members,
    	, StrToMember ("[USERS].[USER ID].["+ Right(UserName, Len(UserName) - Instr(UserName, "\"))+ "]"),
    	, “Dep Security Bridge”
    
    )
    

    HTH

    Paul

    www.paultebraak.wordpress.com

    Friday, January 13, 2012 5:59 AM
  • Thanks for the reply Paul.

    Can you explain it little more briefly --

    The schema looks ok, so i think its your statement.  Does it restrict the rows with a hardcoded name?

    Is it regarding the UserID being a Varchar Instead of surrogate key or Number in Users Dim Table.

    And i tried with the below statement also and I did not get any results.

     

    Exists
    (
    	[REPORT LEVEL].[DEPT NO].Members,
    	 StrToMember ("[USERS].[USER ID].["+ Right(UserName, Len(UserName) - Instr(UserName, "\"))+ "]"),
    	 “Dep Security Bridge”
    
    )
    

    Thanks

    Shilpa.


    SR
    Friday, January 13, 2012 7:40 PM
  • Hi Shilpa,

    I mean, if you run the mdx with a hard coded name against the cube, do you get the restrictions?  For example, If i run this (in a quick demo i ran up), i get only 2 departments from the 3 that are available;

    select 
    {} on 0,
    exists 
    (
    	  [Department].[Department].[Department]
    	, strtomember('[Employee].[Name].&[Paul]')
    	, 'Fact Security'
    )
    on 1
    from [Security] 

    Then i would check the string being generated.

    with member measures.user_name as "[USERS].[USER ID].["+ Right(UserName, Len(UserName) - Instr(UserName, "\"))+ "]"
    select 
    {measures.user_name} on 0 
    from [Security]


    I noticed that this has no domain.  Is this your intention?

    Finally, i would grab the value from the second query and put it into the first and check you get the results your expecting (ie restricted departments).

    HTH

    Paul

    www.paultebraak.wordpress.com

    Saturday, January 14, 2012 1:35 AM
  • Hi,

    Use the MDX constructor in both Allowed member set and default member set, I think it works for the security.

     


    Prav
    Sunday, January 15, 2012 8:47 AM
  • Prav, I tried in that way too , but still I am seeing all the departments for restricted user.
    SR
    Monday, January 16, 2012 3:32 PM
  • Paul, As in my database username field does not have domain prefix (ex: shilpa.reddy) that is the reason I am using the above statement in mdx (ref: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3c024903-30e3-4075-8cc2-f6a266125c73) Let me know if i need to change any thing in mdx or in the role I did not put any member as it has to pick the user name dynamically when they log in using their windows authentication id. Thanks shilpa.
    SR
    Monday, January 16, 2012 3:39 PM
  • Hi SR,

    It will still work without the domain, is just a little more expensive. 

    Were you able to restrict the results by hardcoding the user name in the mdx above and did the user name return the correct value when the formula (strtomember) was substituted into the exists?

    Is the person an local admin on the machine?

    HTH

    Paul

    www.paultebraak.wordpress.com

    Monday, January 16, 2012 8:15 PM
  • paul,

               I am not able to restrict the departments using the below mdx statement  in the allowed member set of rptng lvl dimension.

     

     


    SR
    Tuesday, January 17, 2012 4:37 AM
  • Sorry, a bit of confusion.

    Does the mdx (run in ssms) give the correct restriction (note you have to hard code another name & correct the dimension/ cube);

    select 
    {} on 0,
    exists 
    (
    	  [Department].[Department].[Department]
    	, strtomember('[Employee].[Name].&[Paul]')
    	, 'Fact Security'
    )
    on 1
    from [Security] 

    If it does, then i would substitute the formula for deriving the user in the column definition and see if how that effects the results.

    HTH

    Paul

    www.paultebraak.wordpress.com

    Tuesday, January 17, 2012 4:42 AM
  • Hi Shilpa,

    Just as Paul mentioned, please have a check to USERS dimension, if the members does not contain domain information, you should leverage below query:

    StrToMember ("[USERS].[USER ID].["+VBAMDX!Right(UserName(), VBAMDX!Len(UserName()) - VBAMDX!Instr(UserName(),"\")) + "]"),

    If the members does contain domain information, you should leverage below query:

    StrToMember ("[USERS].[USER ID].["+UserName()+ "]")

    If the issue still exist, please feel free to let us know.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 17, 2012 5:57 AM
  • Paul,

    Sorry for the confusion from my part too..

     

    Does the mdx (run in ssms) give the correct restriction (note you have to hard code another name & correct the dimension/ cube);

     

    select 
    {} on 0,
    exists 
    (
    	  [Department].[Department].[Department]
    	, strtomember('[Employee].[Name].&[Paul]')
    	, 'Fact Security'
    )
    on 1
    from [Security] 

    The MDX that I have run in the SSMS with hard coded name does not give restricted departments. please see the following screen shot.

    Joni.Macdonald should not see the AAA00 Dept  as she does not have access to it.

     


    SR
    Tuesday, January 17, 2012 4:10 PM
  • Challen Fu,

                      I checked the user dimension by using Paul's Mdx statement and it does not have a domain name in the members.

    so  where do you want me use this below mdx statement

     

    StrToMember ("[USERS].[USER ID].["+VBAMDX!Right(UserName(), VBAMDX!Len(UserName()) - VBAMDX!Instr(UserName(),"\")) + "]"),


    SR
    Tuesday, January 17, 2012 6:17 PM
  • In details                                                         PART-1

     

     

    Here are the screen shots; I think this will give clear picture to help me out.

     

    BFMS XWALK is my user table in which users “WIN AUTH ID” and BFMS “USER ID” is stored.

     

     

    DEPT SEC PROF is a bridge table which contains M2M relationship between users and Departments which they have access to.

    Ex:   ‘Newguy’ has access to AAA00 dept and ‘Jonimacd’ do not have access.

     

     

     

     

     

     


    SR
    Tuesday, January 17, 2012 6:37 PM
  •   In details                             Part - 2

     

    So in my cube DSV, I have created a

    Dimension on BFMS XWALK and
    BRIDGE fact table on DEPT SEC PROF

     My task is to restrict the users in run time to their corresponding departments and amounts when they query the cube.

    Here “Dept No” is an attribute in “RPTNG LVL  dimension for which “Rptng Lvl id” Is the key column and RPTNG LVL dimension is joined with two  main fact tables actuals and budget.

    So I joined “Dept Sec prof” bridge fact table with “RPTNG LVL” and “BFMS XWALK” dimensions in DSV as shown in the screenshot below. 

      

      

     

     

    After this I have created a role named Security in which I have included   no members, read access to database,   read access to cube ,  read aceess to dimensions and coming to the  dimension data tab,  I went to Rptng lvl dimension advanced tab options , selected the DEPT NO attribute and in the allowed member set  I wrote the following mdx.

    Exists(
    [REPORT LEVEL].[DEPT NO].Members,
    (
    StrToMember ("[BFMS XWALK].[WIN AUTH ID].["+ Right(UserName, Len(UserName) - Instr(UserName, "\"))+ "]"),
    [Measures].[DEPT SEC PROF FACT Count]
    )
    )

     

     


    SR
    Tuesday, January 17, 2012 6:52 PM
  • In details                                       Part -3

     

    And went for cube processing ….

    Once the cube is processed …

    When I logged as myself I can see AAA00 which is correct but

     when I switch the user  in BIDS as   Joni. MacDonald     I am still seeing the Dept AAA00 which I should not. 

    positive thing is that I am not seeing any results for Joni in  DEPT SEC FACT COUNT measures but my task is to not to show the DEPT AAA00 and corresponding Budget and Actual amounts for Joni.

       

    Then I went to Management studio  and checked this MDX with hard coding  Joni’s  id and still she has access to AAA00, which she should not see.

     

     

     Let me know what should I do next to make this work.

     

    THANKS

    SR


    SR
    Tuesday, January 17, 2012 7:00 PM
  • HI SR,

    You should not specify the default member for the role, it is unnecessary and will return a set.

    Our issue is to understand why the hardcoded mdx for joni dosent restrict the rows.  You can run this as yourself (admin).  If you run the sql query do you get any results (i hope the names & tables were correct)?

    Select * 
    From DEP_SEC_PROF_FACT 
    Where RPTNG_LVL_ID = ‘AAA00’ and WIN_AUTH_ID = ‘Joni.MacDonald’
    
    

    I was assuming that there is no fact data for this combination.

    Can you also run this mdx show the results (for joni).

    with member measures.user_name as 
    VBAMDX!Right(UserName(), VBAMDX!Len(UserName()) - VBAMDX!Instr(UserName(),"\"))
    
    member measures.dim_key as [REPORT LEVEL].[DEPT NO].currentmember.member_key 
    
    select {measures.user_name,measures.dim_key}  on 0,
    [USERS].[USER ID].Members on 1
    
    from [BUDGET ACUTAL]

    Additionaly the last clause of the exists should reference the measure group (sorry but i cant see the name).

    HTH

    Paul

    www.paultebraak.wordpress.com

    Tuesday, January 17, 2012 8:40 PM
  • Our issue is to understand why the hardcoded mdx for joni dosent restrict the rows.  You can run this as yourself (admin).  If you run the sql query do you get any results (i hope the names & tables were correct)?

     

    Select * 
    From DEP_SEC_PROF_FACT 
    Where RPTNG_LVL_ID = ‘AAA00’ and WIN_AUTH_ID = ‘Joni.MacDonald’
    
    


    I was assuming that there is no fact data for this combination.

    As you said I ran the following query and no rows returned for Joni for dept AAA00 in the database.

     

    Can you also run this mdx show the results (for joni).

     

    with member measures.user_name as 
    VBAMDX!Right(UserName(), VBAMDX!Len(UserName()) - VBAMDX!Instr(UserName(),"\"))
    
    member measures.dim_key as [REPORT LEVEL].[DEPT NO].currentmember.member_key 
    
    select {measures.user_name,measures.dim_key}  on 0,
    [USERS].[USER ID].Members on 1
    
    from [BUDGET ACUTAL]

    I ran this query too in SSMS as I got the follwoing result.

     let me know if it leaves us with  any clue?

     

    Thanks

    Shilpa SRK.

     

     


    SR
    Tuesday, January 17, 2012 10:00 PM
  • Paul,

           

    //You should not specify the default member for the role, it is unnecessary and will return a set.//

     

    Does it mean I need to add myself as a member in the role?


    SR
    Wednesday, January 18, 2012 4:59 PM
  • Hi Shilpa,

    I assume your an admin on the machine so this security will not apply to you (even if you are a member of the role).  If there is no default member defined, the all member will be used.

    Can you also just show the results for this query (where AAA00 is in the screen)? and expand the measures node?

    member measures.dim_key as [REPORT LEVEL].[DEPT NO].currentmember.member_unique_name
    
    select {measures.dim_key}  on 0,
    [REPORT LEVEL].[DEPT NO].members on 1
    
    from [BUDGET ACUTAL]

    Regards,

    Paul

    www.paultebraak.wordpress.com

     

     

    Wednesday, January 18, 2012 7:47 PM

  • SR
    Wednesday, January 18, 2012 9:39 PM
  • One more please ?

    select {} on 0,
    exists
    (
     [REPORT LEVEL].[DEPT NO].Members
     , StrToMember ("[BFMS XWALK].[WIN AUTH ID].&[Joni.Macdonald]")
      , 'DEPT SEC PROF FACT'
    ) on 1
    from [Budget Actual]

    We have no row for joni x AAA00 right?  We should not see it in the results.

    HTH

    Paul

    www.paultebraak.wordpress.com

    Wednesday, January 18, 2012 9:47 PM

  • SR
    Wednesday, January 18, 2012 9:58 PM
  • Excellent!  Can you use this definition in the allowed member set?

    exists
    (
     [REPORT LEVEL].[DEPT NO].Members
     , StrToMember ("[USERS].[USER ID].["+VBAMDX!Right(UserName(), VBAMDX!Len(UserName()) - VBAMDX!Instr(UserName(),"\")) + "]")
     "]")
      , 'DEPT SEC PROF FACT'
    )

    Wednesday, January 18, 2012 10:08 PM
  • I am using this MDX in the  advanced properties -->allowed member set --> of Report level cube dimenison.

    After this do I need to process the cube  with Full process or process Index will work?

    Because when I do the full process it will take 45 minutes but fro process index it will take less than a minute...

    which one you suggest?

     


    SR
    Wednesday, January 18, 2012 10:30 PM
  • You could just do it online & save the changes?
    Wednesday, January 18, 2012 10:35 PM
  • I am sorry can you explain me in detail

    I do not know Online means in SSAS?

     

    Thanks


    SR
    Wednesday, January 18, 2012 10:56 PM
  • In bids, you  can go file -> open --> Analysis services database.  This opens the database on the server (and also creates a file solution for you).  Changes are deployed straight to the server.  Its not good for dev but all we want to see is if the security will work.
    Wednesday, January 18, 2012 11:11 PM
  • Im a little unsure of what your asking but when you open it in online mode, save changes, you can view them straight away.

    Wednesday, January 18, 2012 11:28 PM
  • paul,

            After cube processing  when I logged on as Joni ( Changing the user in BIDS) and when I drag the Dept No from Report lvl dimension and Dept sec fact count from measures  I am still seeing the Dept AAA00 .

     


    SR
    Thursday, January 19, 2012 12:33 AM
  • Is joni part of the admin group, possibly an admin on the machine?
    Thursday, January 19, 2012 12:56 AM
  • She is  in one of of the admingroup on the server. If we remove this group on the server how she can access the cube through excel.

    being an admisintrator  on the server as she has access to all the depts in the cube on the database?


    SR
    Thursday, January 19, 2012 1:29 AM
  • Yes thats correct.  Theres usually an issue with the person being an machine local admin also, but i will assume she is not a local admin.

    The users donot have to be a member of the admin role to access the cube via excel.  A standard role will suffice, infact, if they are a member of admin, any other secuiryt profile will be redundant. 

    You just have to give the nonadmin role access to the cube and then specify the security as we have already done.  As you have done, role membership can be through a domain role.

    Remember also that allowed access is acumulative so, it will increment security privalages for all roles that the user is associated with.

    We may be getting closer!

    HTH

    Paul

    paultebraak.wordpress.com

    • Marked as answer by Shilpa-ssas Thursday, January 19, 2012 8:27 PM
    Thursday, January 19, 2012 1:37 AM
  • Solved ?
    Thursday, January 19, 2012 8:36 PM
  • Hi Everyone, 

    I have similar problem but I cudn't relate this as I am new to SSAS.Can anyone of you helo me with my security issue??Thanks

     

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/cf604d3e-d718-474e-b791-9cabd3bbd02d

     

     


    • Edited by Spartaa Thursday, January 19, 2012 10:47 PM
    Thursday, January 19, 2012 10:47 PM
  • Hi Shilpa and Paul,

    I have been watching this thread for a while yesterday, I did not want to interrupt the discussion between you, today we are happy to see the truth that Shilpa has added the user to local machine administrator group, actually the user in local machine administrator group will convert to SSAS server role automatically. @Paul, through your conversation, we also see your clear analyzing thought, you have a really good troubleshooting skill.

    Shilpa thanks for marking the thread, it will be helpful to other communities who will have the same issue.

    Thanks all,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 20, 2012 2:01 AM
  • Paul, I think we are close to Success..... What happens if I take out Joni from Bfms-Dev group temporarily by creating a separate role to access the cube for Joni and add that role in membership tab of the role in BIDS. does it serve for testing? Thank you for your support. Challen fu : Thanks for the update.
    SR
    Friday, January 20, 2012 2:48 AM
  • HI Shilpa,

    That will work.  But i think it depends on the role of Bfms-Dev.  Are they supposed to be admins after all?  If not why not just remove the role from the membership of the ssas group admin.  That will be faster anyway.

    Regards,

    Paul,

    www.paultebraak.wordpress.com

    Friday, January 20, 2012 3:17 AM
  • Hi Challen,

    Sometimes these things take a little longer than expected to get through when we are all in different time zones!

    Regards,

    Paul

    www.paultebraak.wordpress.com

    Friday, January 20, 2012 3:18 AM
  • Paul,

              I am thinking to do that only but they ask me to hold on that so I am waiting for a reply from their side, will let you know once it is fixed.

     

    Thanks you for your support.

    SR


    SR
    Monday, January 23, 2012 3:16 PM