I usually avoid using cell security like the plague for the good reason that it absolutely kills query performance. But sometimes there’s no alternative but to use it and I’m working on one such project right now. However I’ve found a new gotcha: the behaviour of cell security changed between AS2005 SP1 and SP2 and in my opinion it didn’t change for the better. The short explanation is that when you are using just Read permissions in cell security, if you have MDX Script assignments at a lower level of granularity you’ll find that the higher level members whose values are affected by the assignments have Read Contingent permissions applied to them, even though according to the MDX expression I used in for the Read permissions these values should not be secured.
Here’s the repro I’ve got for Adventure Works using AS2005 SP2:
- In AW, comment out everything in the MDX Script apart from the Calculate statement
- Add the following calculations to the Script:
CREATE MEMBER CURRENTCUBE.MEASURES.[Show Value] AS
CREATE MEMBER CURRENTCUBE.MEASURES.[Show Internet Sales Amount] AS [Measures].[Internet Sales Amount];
- As an Administrator you see the following results for a query in the cube browser:
- Now create a role with the following Read permissions in the cell security tab:
[Measures].CURRENTMEMBER IS [Measures].[Internet Sales Amount],
- Display the same query in the browser when connecting through this new role, and as expected you see the following:
- Now add the following assignment to the MDX Script:
- And refresh the query, so you see this:
- The big change is that the value for CY2004 and the All Member are now secured too. So we’re now in the situation where we’ve made an assignment that displays a value that wasn’t secured anyway, but we now can’t see the CY2004 value even though the expression in the Read permissions returns true. If you were to change the assignment so it refers to the value for January 2004, as follows:
You can see the January value either while looking at August or the ‘Show Internet Sales Amount’ calculated measure, as I would expect, because neither are aggregated from anything and the expression for the Read permission always returns True for them:
For my customer, who is migrating from AS2005 SP1 direct to Katmai, this is potentially a major problem. And to be honest as my repro shows I don’t think this new behaviour makes any sense at all anyway – if I wanted to use Read Contingent permissions I’d have used Read Contingent permissions!