Applying Cell-level Security to a Cube

Dimension-level security prevents the user from seeing certain members. Such security does not directly prevent viewing of cell values, but, clearly, if a member does not appear, the values for that member will not be visible either. In short, in most situations, dimension-level security is all you’ll ever need. In some situations, however, you might want reports to display all the members but block the values for some of the cells. When you need to secure specific cells without removing members, you need to apply cell-level security to a cube.

Prevent Values in Cells from Being Read

The Finance cube contains, among other things, expense information. It includes Headcount, Salary, and Benefits members. Salary information is typically very sensitive within a company. You might want to change a cube so that budget reviewers can see the higher level financial information but not the detailed financial information. You can use cell-level security to do this. Start by reviewing the complete information in the Finance cube.

1.	Right-click the Finance cube, and click Manage Roles. Select the Budget Reviewers role, and click Test Role. Expand all the levels of the Account dimension. Resize the browser window as needed. [View full size image] All the cells contain values. You want to prevent the reviewer from seeing the values under the Labor member.
2.	Close the browser window. Click the ellipsis button in the Cells column. The default Cell Security Policy value is Unrestricted Read. Select Advanced from the drop-down list. [View full size image] With the cell security policy set to Advanced, the advanced cell security grid becomes available. You want to control the reading of cells but only for some cells, so you’ll need a custom setting.
3.	With Read selected in the Permission column, select Custom in the Rule column and click the ellipsis in the Custom Settings column. Type Nothing under labor in the Description box. For cell-level security, you need an MDX expression that can be calculated for each cell of a grid. For each cell, before the value is displayed, the expression is evaluated. If, in the context of that one cell, the expression returns the value 1 (True), the value is displayed. If the expression returns 0 (False), the value is not displayed. To use the MDX Builder to construct an expression, click the ellipsis button to the right of the MDX box.
4.	In the MDX box, type the expression Account.Parent.Name <> “Labor”, and click Check. A message box appears informing you that the syntax is valid.
5.	Click OK to close the message box and OK to close the Cube Cell Security dialog box. Creating a custom security setting automatically sets the Read Contingent and Read/Write permissions for the role to Fully Restricted. If you were to set the Read/Write permission to Unrestricted, all users would have unrestricted access to the cube. That’s because Read/Write permission implies Read permission—even though this cube is not writeenabled. When you restrict one of the permissions, you must restrict the other permissions. Read Contingent is an advanced condition. To find more about it, click the Help button and in the Cells Tab (Cube Role Dialog Box) topic, read the description of Read Contingent.
6.	Close the Edit A Cube Role dialog box. Click the Test Role button, and fully expand the Account dimension levels, resizing the browser window as necessary. [View full size image] The detailed Labor members still appear, but #N/A appears in all the cells.
7.	Close the browser window, and close Cube Role Manager.

Allow Users to Write to Cells

Cell-level security is particularly important in write-enabled cubes because you might want different groups of people to be able to modify different cell values. The Sales Forecast cube has been write-enabled. As a default, a role gives unrestricted read permission but no write permission even if the cube is writeenabled. To allow the members of a role to write to a cube, the cube must be write-enabled and then the role must be given read/write permission.

1.	Right-click the Sales Forecast cube, and click Manage Roles.
2.	With the Budget Analysts role selected, click the ellipsis button in the Cells column. [View full size image]
3.	In the Cell Security Policy drop-down list, select Unrestricted Read/Write. Note The Unrestricted Read/Write policy is available only if the cube has already been write-enabled. A cube that has not been write-enabled has only two options: Unrestricted Read and Advanced. If you want to allow write permission to only selected cells, you create an MDX expression for the Read/Write permission precisely as described in the preceding section.
4.	Click OK to close the Cube Role Editor window, and click Close to close Cube Role Manager.

Security is an important part of an Analysis Services application, and Cube Role Manager gives you tremendous flexibility for applying security. You can apply security with broad brush strokes at the cube level, with small brush strokes at the dimension level, or with single-hair precision by using the full flexibility of MDX expressions.