none
SSAS Dynamic Security Issues RRS feed

  • Question

  • I wanted to implement a dynamic security in my cube. but it doesn't work.

    here is the system: I have a fact table with an ID_OrganisatinnalUnit

    i have an Organisational Unit dimension

    I have a security table in my DWH with  the ID_OU and the Windows Login.

    I want to create a role with advanced security like this :

    Exists(Dim_OU.Members, STRTOMEMBER(Dim_Securite.Login.&'+username) (it's pseudo code)

    I did something like that in a previous mission but in my fact i had the ID_USer related to the Dim_Security, now i don't know how to make my Dim_Security with this model.

     Any Ideas ? do you need more infos ?

    Thanks in advance


    Ludovic Bouaziz - MCSD .net
    Wednesday, August 24, 2011 7:38 AM

All replies

  • Hi Xldaoul,

    Is there any relationship between Organisational Unit dimension and User dimension? If no, can use Named query to add Windows login to Dim_Security, then the expression you given should work. See http://blogs.msdn.com/b/azazr/archive/2008/08/15/dynamic-security-in-ssas-cube.aspx for an example on how to implement dynamic security. If you have more quesiton regarding dynamic security, provide more details about these dimension and fact tables structure and relationships.

    thanks,
    Jerry

    Thursday, August 25, 2011 8:57 AM
    Moderator
  • Hi Jerry, thanks for your answer.

     

    i read the link you provides but it doesn't help me much.

    yesterday i tried another approach (http://www.sqlmag.com/article/sql-server-analysis-services/protect-udm-with-dimension-data-security-part-2)

    so i change my fact table to a named query, because in my fact i had the ID_OrgUnit related to the DIM_OrgUnit, i did a inner join to my security tables to retrieve and add in the fact the User_ID,

    next i did a named query Dim_Security with User_ID and Login inside in my dsv

    so finalyy i have something like that

     

    my fact table Contract  and 9 dimensions tables including Dim_OrgUnit table, DIM_Security Table and relationship between fact table and dim tables.

    then i do the dimension for the Dim_security table with no hierarchy , a hierarchy in the Dim_OrgUnit.

    In the cube usage, for my Contract measure group, i define the usage with the Dim_OrgUnit on ID_OrgUnit and with the Dim_Security on the User_ID

    I Process and it works fine.

    Next, i create a role, i add inthe membership some users (test, test 2, test 3) i set the access to my cube in the cubes tab.

    i go in dimension data, i select in the dimension ddl Dim_OrgUnit, in the basic tab i select my attribute hierarchy Organisation

    I select the option Deselect all member

    in advanced tab for Organisation (attribute security defined) in allowed member set i write :

    Exists( [Dim_OrgUnit].[Organisation].members, StrToMember('[Dim_Security].[Login].&['+Username+']'),'Contract')

    sometimes at this step but not always, i have an error message MDX saying that my Dim_Security was not found but i click space here or there and the error simply disapear.

    then i go to cell data : Test Cube Security link

    deploying,publishing...

    I shift right click on excel (execute as MyDomain\Test) i connect to my cube and i browse my measure nb of contracts and here i have 10000 (i should Have 100 corresponding to the count distinct contract on my fact table for the OrgUnit related to Test user, 10 000 is the count total of contract in this table, so my security doesn't work)

    do i do something wrong ? is it possible that i implement my security too late in the process of building my cube ? also all my measures are calculated members, could it doesn't work because of that ?

    Thanks,

    Ludo

     


    Ludovic Bouaziz - MCSD .net
    Thursday, August 25, 2011 3:55 PM