Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

22.3. Securing Your Cube Data

Restricting access to certain cell values of the cube for users is referred to as cell security. For example, in the case of confidential information like employee salaries, you can allow your employees to browse information about other employees such as number of years in the company, title, phone number, address, and login information, but restrict salary information. Because you want the information viewable by the person's manager, you need to control access at the cell value level rather than for whole dimension members.

Similar to dimension security, Analysis Services allows you to specify permission to cells using the roles. Access to cell values in a cube is restricted through an MDX expression that can be defined similar to dimension security. The MDX expression needs to evaluate to true or false. You can specify read and write permissions for cells in a cube. When a query is sent to the Analysis Services instance, the cells that are part of that query result are evaluated and returned. Whenever a cell is being evaluated, Analysis Services checks the permissions set for the cell. If the permission is set, it evaluates the condition to see if the user has access to the cell. If the user is allowed to view the cell, that cell value would be returned as part of the result. If the user does not have access to that specific cell, an appropriate message will be returned to the user.

22.3.1. Scenario Using Cell Security

Business Scenario definition: ...