Securing Your Data in Analysis Services - Professional SQL Server Analysis Services 2005 with MDX - page 1002

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 em-
ployees such as number of years in the company, title, phone number, ad-
dress, 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 permis-
sion 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.
Scenario using Cell Security
Business Scenario definition: You are the director of the company and you
want to take a satisfaction survey or poll of your employees. Employees can
only view results of the survey they have filled in. However, managers can
view the aggregated results of the poll results if and only if they have more
than two direct reports. Managers cannot see individual responses of their dir-
ect reports because this is a confidential survey. As an administrator you need
to implement a UDM in Analysis Services 2005 so that you give appropriate
security restrictions to the users to see the results.
You will use the same Dimension and Cell security relational database. This
database contains tables that have the questions of the poll and the results
from the employees. The following steps show how to create the right UDM
and then apply cell security restrictions for the employees.