locked
Implementing data security from dashboards RRS feed

  • Question

  • I implemented data security the way it described in a great blog: http://hccmsbi.blogspot.com/2007/08/implementing-user-specific-security-in.html

     

    Everything works great from any OLAP client except PPS.

     

    When I publish a test dashbaord on the Sharepoint site and login as a test user (who is supposed to see only subset of records) I still see ALL records.

     

    In the SQL profiler I can see the user making the query and the query itself.

     

    Let me describe the problem again:

     I execute the query from SQL Query analyser and I get the expected number of the records for the logged in windows user.

    The same query executed from PPS report published on the SharePoint site returns ALL records like if the current user was a loclal administrator.

     

    Yes, I have <add key="Bpm.ServerConnectionPerUser" value="True" /> in the web.config files and I can see the identity of the user making the query in the SQL Profiler.

     

    It may be an SSAS problem and looks like a security breach.

     

    The worst case scenario I will need to implenment the data security using dshboard filters.

     

    Thanks,

     

    Vlad

     

    Saturday, December 6, 2008 3:16 PM

Answers

  • Hi Vlad,

     

    If you've defined custom security on a specific SSAS role you may want to provide the role name in the Data Source definition.

     

    Also "I see all records" - where? In a filter, analytic grid / chart report. What kind of element is "all" the data being shown?

     

    Cheers,

    Nick

     

    Saturday, December 6, 2008 8:24 PM

All replies

  • Hi Vlad,

     

    If you've defined custom security on a specific SSAS role you may want to provide the role name in the Data Source definition.

     

    Also "I see all records" - where? In a filter, analytic grid / chart report. What kind of element is "all" the data being shown?

     

    Cheers,

    Nick

     

    Saturday, December 6, 2008 8:24 PM
  •  

    Thank you very much Nick, 

    Using the Role name in the dashboard connection definition solved the problem and the dashboard works as expected now.

    Still I would be very curious to know how PPS Monitoring framework is using the Roles defined in SSAS when getting the data from AS database.

    Here is what I did and what I got without using the role name in the connection definition:

    1.      I defined the two Roles in AS database:

    a.      Admin, with full control and only local admins are members of the role.

    b.      Employees. The data security is defined for this role the way that, there is only one Employee member available for this role whose ID matches UserName (current windows user).

    2.      In my test dashboard I have one report with a data grid which displays Employees on the rows.

    3.      Since during the dashboard design I’m logged is as administrator, I can see all employees which is expected.

    4.      After I publish the dashboard on sharepoint site and log in as an ordinary user (not admin).

    5.      After I refresh the dashboard I can still see all employees.

    6.      Using SQL profiler I see that the MDX query is execute under the user (not admin) identity.

    7.      Executing the same MDX query under the user identity from SQL query analyzer returns only one user (which is expected).

    Once again putting the Employees role name in the dashboard datasource definition fixes the problem.

    The question is how PPS is retrieving the dataset?

    Looks like it caches the data retrieved from al user who logged in first, and then uses only the cache, after I log in is as a different user and try to refresh the data.

     

    Thanks again,

    Vlad

    Sunday, December 7, 2008 10:17 PM
  • Hi Vlad,

     

    Glad I was able to help. There should be no caching done by the roles based on who was first. Is there some possibility your test user has some elevated system privilege somewhere? It seems that SSAS is recognising that the user has some elevated privileges somewhere along the line. By explicitly naming the role in the data source you are telling SSAS that that is the privliege set that is to be used. Otherwise SSAS will simply apply the highest level of access that is available to the user making the query. Somewhere, somehow your test user has higher access.

     

    Cheers,

    Nick 

     

    Monday, December 8, 2008 12:29 AM