Securing Cell Values - Microsoft SQL Server 2008 Analysis Services - page 752

FIGURE 37.4 The cell for store sales in Canada has an error because it is secured from
users of TestRole by the cell security.
Contingent Cell Security
When you specify Read as the access permission type of the CellPermission object,
Analysis Services allows or denies the user rights to see the values covered by the MDX
expression in the Expression property. However, this might expose more information
than you intended. Consider, for example, the following calculated member:
[Measures].[Profit] = [Measures].[Store Sales] - [Measures].[Store Cost]
If you define cell security as in the previous example (which denied user access to values
of store sales in Canada), you don't prevent the user from seeing the profit of sales in
Canada or from seeing the cost of the stores in Canada. Therefore, a smart user can
deduce the following information:
[Measures].[Store Sales] = [Measures].[Profit] + [Measures].[Store Cost]
If you want to prevent such a situation, you can modify the access type of the
CellPermission object to deny access to the value of the Profit member in addition to
the value of Store Sales . However, if you have many calculated members or other calcu-
lations, the process may become cumbersome. You can instead define the access type as
ReadContingent and prevent the user from accessing Profit , because the cell that Profit
depends on— Store Sales —is secured. You can do this in BI Dev Studio by moving the
cell security expression from Enable Read Permission to the Allow Reading of Cell Content
Contingent on Cell Security, as shown in Figure 37.5.