Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Cell Security: when Read permissions are actually Read Contingent

with 3 comments

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
    IIF(
    [Date].[Calendar].CURRENTMEMBER IS
    [Date].[Calendar].[Month].&[2004]&[1],
    FALSE, TRUE);

    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:
    clip_image001
  • Now create a role with the following Read permissions in the cell security tab:
    IIF(
    [Date].[Calendar].CURRENTMEMBER IS
    [Date].[Calendar].[Month].&[2004]&[1]
    AND
    [Measures].CURRENTMEMBER IS [Measures].[Internet Sales Amount],
    FALSE, TRUE)
  • Display the same query in the browser when connecting through this new role, and as expected you see the following:
    clip_image002
  • Now add the following assignment to the MDX Script:
    ([Date].[Calendar].[Month].&[2004]&[8])=[Date].[Calendar].[Month].&[2004]&[7];
  • And refresh the query, so you see this:
    clip_image003
  • 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:
    ([Date].[Calendar].[Month].&[2004]&[8])=[Date].[Calendar].[Month].&[2004]&[1];
    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:
    clip_image004

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!

Written by Chris Webb

May 20, 2008 at 5:59 pm

Posted in Analysis Services

3 Responses

Subscribe to comments with RSS.

  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.

    Unknown

    June 5, 2008 at 8:58 pm

  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?

    Thks,
    Rafael Augusto

    Rafael Augusto

    February 19, 2013 at 12:04 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: