Dynamic Security in Analysis Services / Drillthrough
-
Hi there,
I have recently set up Dynamic Security using one of the Microsoft suggestions from their WebCast on this subject (Fact Table approach).
This works very well, and within a general Cube, users only see the data for which they have been granted permission. The problem comes when they do a Drillthrough on the data - they get all of the data - i.e. with no restrictions.
As an example, I am filtering data by Geography. What is happening is that the user has access to one Geographical region (UK), but this is the only region shown within its Parent (World), so when Analysis Services performs the Drillthrough, it takes the Parent value, searches by World and presents a security problem as it gives all data.
Any suggestions on how to overcome this would be most welcome.
Many thanks,
Nigel.
-
As an example, I am filtering data by Geography. What is happening is that the user has access to one Geographical region (UK), but this is the only region shown within its Parent (World), so when Analysis Services performs the Drillthrough, it takes the Parent value, searches by World and presents a security problem as it gives all data.
Maybe You could hide the All Level in this dimension ( if "World" corresponds to the All Level ).
Maybe this might trick AS to only retrieve a filtered rowset.
With Profiler You could monitor the SQL that AS sends to SQL Server, to see if that gives any clues.
Just curious: Can't You use the "normal" security model in AS. Or would this result in "too many" differnt security roles to administer ?.
/rockmoose
You must unlearn what You have learnt -
hi Rockmoose,
Many thanks for your response. In my example, "World" does not correspond to the All Level, so hiding the All level would not help. Profiler is great for monitoring this sort of thing - this is how I detected that the Drillthrough is specifying "World", instead of the filtered countries.
I believe that if we used the "normal" security model in AS, we would have the same problem with the Drillthrough. If you had multiple Roles and a User had the one for "UK" data, when it displays the Geography Dimension, it would say "World \ UK". When you then do the Drillthrough, Analysis Services would take the Parent value again.
We are using many "roles" and Analysis Services gets difficult to manage under these circumstances.
-
Yeah,
This is troublesome. We limit the number of roles, use "normal" security and don't do much drilltrough.
For detail data we provide traditional asp reports ( RS will maybe be used next year ).
Maybe You could define an "Action" for the Geography dimension which takes the user to an ASP page that will generate the detail data. ( I don't particularily like the drillthrough grid anyway, not very extensible
 
I envision that this ASP page will execute an sp and provide the user creds.
Since You already have the usercreds in the facttable, You could "just" select and filter :-).
Or You just provide drillthrough to a group with high security !
The others just don't have it !
/rockmoose
You must unlearn what You have learnt -
Hi Rockmoose,
Just to complete the post with info for others facing the same issue, we resolved the problem by re-applying the filter to the data returned by the Drillthrough prior to displaying that info to the user.
It works, but not as efficiently as we would like. Shame that there is no overall setting within Analysis Services.
A feature for a future version, perhaps?
Nigel.
-
Cetainly sounds like a feature request. Send it to sqlwish@microsoft.com
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply