Cell Security
In a cube role, you can implement cell security to limit the cube cells that end users in the role can view as they browse cubes. You can also grant read/write access to a write-enabled cube and limit the cells that end users in the role can update. You do this by selecting a policy and by selecting a rule or defining a custom rule for each permission.
Cell security is optional. If you do not specify cell security, end users see all cell values in cubes they are authorized to access. (However, if dimension security is specified, cells for some members might not be viewable.) If a cube is write-enabled, end users cannot update cell values. If one or more of a virtual cube's component cubes are write-enabled, end users cannot update the cell values of virtual cubes.
If a policy or rule permits updates to a cell, it can be updated if it is an atomic cell. If the cell is not atomic, it can be updated only if the client application provides a way of dispersing the update over the subordinate atomic cells. For example, in a client application a write-enabled cube is displayed with the lowest level of every dimension except Time. On the axis for the Time dimension, the nonatomic cells for months are displayed, but the subordinate atomic cells for days are not. (Days is the lowest level in the Time dimension.) A cell for June can be updated by adding $90 if the client application provides a way of dividing the +$90 update into thirty +$3 updates, one to each of the cells for the 30 days in June. Dispersion methods other than simple division can also be used. The UPDATE CUBE statement provides several methods. For more information, see UPDATE CUBE Statement.
Cell security is defined in the Cells tab of the Cube Role dialog box.
Policies
When you specify cell security, for each cube role you can select one of the following policies.
| Policy | Description |
|---|---|
| Unrestricted Read | End users can view all cell values. This policy is the default. |
| Unrestricted Read/Write | End users can view and update all cell values. |
| Advanced | End users can view and update only the cell values you specify in the permissions and rules for cell security. |
You can select the Unrestricted Read/Write policy for a cube only if it has been write-enabled. If you select this policy, and the cube is subsequently write-disabled, this policy is disabled, and end users cannot update the cube's cell values. You can select this policy for a virtual cube only if one or more of its component cubes have been write-enabled. If you select this policy, and all the component cubes are subsequently write-disabled, this policy is disabled, and end users cannot update the virtual cube's cell values.
Permissions and Rules for the Advanced Policy
If you select the Advanced policy, you can define three permissions: read, read contingent, and read/write. Other policies do not involve permissions or rules. The permissions you define for Advanced policy allow further definition through rules. These rules are similar to the rules you set for roles.
Read Permission (Advanced Policy)
This permission determines which cell values are viewable. Cells specified in this permission are viewable regardless of whether they are derived from other cells that are not viewable. For example, the calculate member Profit is derived from cells for measures Sales and Cost. (Profit equals Sales minus Cost.) If Profit is included in the read permission, its cells are viewable even if cells for Sales or Cost are not.
Note Including derived cells in the read permission incurs the risk that end users might determine cell values they cannot view. For example, if cells for Profit and Cost are viewable, but cells for Sales are not, end users can determine Sales values by adding Profit and Cost values.
You can select one of the following rules for the read permission.
| Rule | Description |
|---|---|
| Unrestricted | End users can view all cell values. This rule is the default. |
| Fully Restricted | End users can view only the cell values specified in the read/write permission or read contingent permission, subject to the limitations of the read contingent permission described later in this topic. |
| Custom | This rule provides the most flexibility. You can write an expression in Multidimensional Expressions (MDX) to identify the cell values that are viewable and that are not viewable. |
Read Contingent Permission (Advanced Policy)
This permission determines which cell values are viewable. However, cells specified in this permission and derived from other cells are viewable only if all the other cells are also viewable (that is, included in the read permission; or included in read contingent permission but not derived). Cells specified in this permission but not derived from other cells are viewable.
For example, if Profit is included in the read contingent permission, its cells are viewable only if cells for both Sales and Cost are included in the read permission, or if they are included in the read contingent permission but not derived from other cells. If Cost was included in the read contingent permission and derived from other cells, Profit would be viewable only if those cells were included in the read permission or if they were included in read contingent permission but not derived. Thus, with the read contingent permission, a chain of contingencies can be created when a cell is derived from others, which in turn are derived from others, and possibly so on.
If a cell is included in both the read and read contingent permissions, the read permission is enforced, but the read contingent permission is not.
| Rule | Description |
|---|---|
| Unrestricted | End users can view all cell values that are not derived from other cells. If a cell value is derived from other cells, it is viewable if all the other cells are included in the read or read/write permission. |
| Fully Restricted | End users can view only the cell values specified in the read permission or read/write permission. This rule is the default. |
| Custom | This rule provides the most flexibility. You can write an MDX expression to identify the cell values that are viewable and that are not viewable, subject to the limitations of the read contingent permission described earlier in this topic. |
If the rules define a cell as not viewable, the cell itself is visible but its value is not. Thus, cell security does not restrict the members that are visible and thereby the size of the visible cube. Rather, it can limit the ability to view the cell values associated with specified members. To limit the members that are viewable, use dimension security. For more information, see Dimension Security.
If the rules define a cell as not viewable, and an end user in the cube role queries this cell, by default the Analysis server returns the formatted value #N/A. The end user sees this value for the cell unless the client application translates the value, or another value is specified by setting the Secured Cell Value property in the connection string. For more information about this property, see Secured Cell Value Property.
Read/write Permission (Advanced Policy)
This permission determines which cell values are updatable.
You can define and grant this permission for a cube only if it has been write-enabled. If you grant this permission, and the cube is subsequently write-disabled, this permission is disabled and end users cannot update the cube's cell values.
Cells specified in the read/write permission are also viewable as if they were specified in the read permission, not the read contingent permission.
| Rule | Description |
|---|---|
| Unrestricted | End users can update all cell values. |
| Fully Restricted | End users cannot update cell values. |
| Custom | This rule provides the most flexibility. You can write an MDX expression to identify the cell values that are updatable and that are not updatable. |