DrillThrough Actions and (semi) Security in SSAS OLAP cubes

Using SSAS 2005/2008, there is no way to apply security to DRILLTHROUGH actions, so we cannot decide whether a specific user is authorized or not to perform a specific action. This is a “by design” behavior, since DRILLTHROUGH actions are initiated at the client side, we can decide whether a user can perform or not DRILLTHROUGH but, once we let him do it, he can query anything he wants.

Nevertheless, if we are not concerned with security but only with user experience, there is a simple trick to define which users can see which actions. Leveraging the Condition box, we can write an MDX condition that makes use of the UserName function (which returns the currently connected user) and decide to activate the action for only specific users, like in this action, that will be active only for “SQLBIAlberto”:

image

Clearly, since the condition can be any valid MDX expression, we can implement some more data driven logic creating a measure group that stores action names and users, in order not to hard-code the user name inside the action.

What should be very clear is that this workaround is intended to make the user experience with the OLAP cube a better one (actions are activated for only the users that want to use them) but has nothing at all to do with security. If we enable a user to initiate DRILLTHROUGH actions he will always be able to query for anything. That said, if our customer uses just Excel or a similar client and there are no serious security issues, then this solution might help him navigate the cube and activate only the required actions.