Analysis Services

Cell Security: when Read permissions are actually Read Contingent

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:
    [Date].[Calendar].CURRENTMEMBER IS

    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:
    [Date].[Calendar].CURRENTMEMBER IS
    [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!

3 thoughts on “Cell Security: when Read permissions are actually Read Contingent

  1. SQL 2005 SP2 made several improvements to cell security in order to plug some security loopholes, although it might have gone too far in your case.
    Let\’s look some cell security loopholes plugged by the SP2 changes.  Assume there is cube where month level data are denied read access but year level data have read permission.  Here are several ways a user might defeat cell security before SP2 changes and get access to month level data.
    Method #1: Bypass cell security through query calculation.
    with cell calculation x for \'[Date].[Fiscal].[Fiscal Year].&[2004]\’ as [Date].[Fiscal].[Month].&[2004]&[4]select [Date].[Fiscal].[Fiscal Year].&[2004] on 0from [Adventure Works]
    Method #2: Bypass cell security through subselect or visualtotals.
    select [Date].[Fiscal].[Fiscal Year].&[2004] on 0from (select [Date].[Fiscal].[Month].&[2004]&[4] on 0 from [Adventure Works])
    Method #3: Bypass cell security through grouping.
    User uses Excel to create a group of a single month [Date].[Fiscal].[Month].&[2004]&[4].  Since the newly created members in the grouping level have read permission, user is able to access month level data.
    In all these cases, users can easily access secured data by modifying the values of non-secured cells.  SQL Server 2005 SP2 plugged these loopholes by changing read permission to read contigent permission at the year level whenever it detected such query structures.  I hope the above examples shed some light on the rationale behind SQL 2005 SP2 cell security improvements.  I agree that the changes were overlzealous in your case which Microsoft is currently investigating.

  2. Hi Chris,

    I’m having this same problem with a project i am working on.
    In this case even if i comment the cube’s MDX script it wont show the cells i want.

    All cells in the cube are secured (“#N/A”) except a few chosen. The problem is that if the cell is in the leaf level of a dimension i can show it, but is this cell is the result of an aggregation of its children than i can’t show it at all.

    Did you found a workaround for this SSAS feature?

    Rafael Augusto

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Are you trying to secure a parent/child hierarchy or a regular dimension? If it’s a regular dimension, you don’t need to use cell security to do what you want at all: you should just deny access to all of the members on the non leaf attributes, similar to what I show here:

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.