Custom Rules in Dimension Security

Analysis Services

previous page next page

Analysis Services

Custom Rules in Dimension Security

In a database role or cube role, by defining a custom rule for a dimension, you can specify which dimension members can be accessed by end users in the role. To do this, you can select the accessible levels, or specify the accessible members, or use these methods in combination. You can also specify options for visual totals and select a default member.

Level Selections

For each dimension you can specify a range of dimension levels that can be accessed by selecting one or both of the following:

Top level
Indicates the topmost level that can be accessed. Levels above the top level cannot be accessed. By default, top level is the dimension's highest level.
Bottom level
Indicates the bottommost level that can be accessed. Levels below the bottom level cannot be accessed. By default, bottom level is the dimension's lowest level.

Examples 1, 2, and 3 illustrate this concept.

Note Although members above the selected top level cannot be accessed, if a client application reveals the names of members' ancestors above the top level (for example, displays fully-qualified member names), end users might deduce cell values for members above the top level.

Member Specifications

You can specify the members that can be accessed by allowing access to some members and denying access to others.

You can combine top level and bottom level selections with member specifications. However, you cannot allow access to members above the top level or below the bottom level.

Descendants of a specified member share the same access. For example, if you explicitly allow access to England, you also implicitly allow access to London, Manchester, and England's other descendants. However, there are three exceptions:

Access to a member is allowed, but a bottom level is selected. Descendants below the bottom level cannot be accessed.
Access to a member is allowed, but access to a descendant is denied. For example, you allow access to England but deny access to London. In this case, England's descendants, except London and its descendants, can be accessed.
Access to a member is denied, but access to a descendant is allowed. In this case, the member you allowed access to and its ancestors up to and including the denied member can be accessed. The descendants of the allowed member can also be accessed. However, no other descendants of the denied member can be accessed. For example, you deny access to Europe but allow access to London. London, England, and Europe can be accessed, London's descendants can be accessed, but other descendants of Europe cannot be accessed.

Ancestors of an explicitly allowed member can also be accessed unless they are above the top level. This is true even if the ancestor is explicitly denied.

In a member specification, you have three basic choices: allow only, deny only, or allow and deny.

Allow Only (Member Specifications)

The members you explicitly allow access to and their descendants and ancestors can be accessed unless they are below the bottom level. The only nondescendants that can be accessed are the allowed members' ancestors at or below the top level.

The following diagram shows a member hierarchy with access explicitly allowed to a single member.

Examples 4 and 7 illustrate this concept.

Deny Only (Member Specifications)

The members you explicitly deny access to and their descendants cannot be accessed. Nondescendants can be accessed unless they are above the top level or below the bottom level.

The following diagram shows a member hierarchy with access explicitly denied to a single member.

Examples 5 and 8 illustrate this concept.

Allow and Deny (Member Specifications)

There are two common relationships between allowed members and denied members:

All of the explicitly denied members are descendants of the explicitly allowed members.
All of the explicitly allowed members are descendants of the explicitly denied members.

If all of the explicitly denied members are descendents of the explicitly allowed members, the members you explicitly allow access to and their descendants can be accessed with the following exceptions: (1) descendants you explicitly deny access to and their descendants cannot be accessed; and (2) descendants below the bottom level cannot be accessed. The only nondescendants of the explicitly allowed members that can be accessed are the allowed members' ancestors at or below the top level.

The following diagram shows a member hierarchy with access explicitly allowed to a single member and access explicitly denied to one of its descendants.

Examples 6 and 9 illustrate this concept.

If all of the explicitly allowed members are descendents of the explicitly denied members, the members you explicitly deny access to and their descendants cannot be accessed with the following exceptions: (1) descendants you explicitly allow access to and their descendants can be accessed unless they are below the bottom level; and (2) descendants you explicitly allow access to and their ancestors up to and including the denied members can be accessed. Nondescendants of the explicitly denied members can be accessed if they are between the top level and bottom level.

The following diagram shows a member hierarchy with access explicitly denied to a single member and access explicitly allowed to one of its descendants.

Example 10 illustrates this concept.

Note This relationship between allowed and denied members cannot be defined in the Basic tab of the Custom Dimension Security dialog box. You must use the Advanced tab.

Other relationships between allowed members and denied members are possible.

Visual Totals

For each dimension you can specify options for visual totals. These options determine whether displayed, aggregated cell values are calculated according to all of a member's descendants or only the viewable descendants. In the first case, end users in the role see actual totals; in the second, they see visual totals. A third option is available to display visual totals at and above a specified level, but display actual totals below it. This option requires an expression in Multidimensional Expressions (MDX). This expression must name the level or resolve to the level at and above which visual totals are displayed. Example 11 illustrates this concept.

Note Visual totals cannot be enabled for a cube that contains a distinct count measure. For more information, see Using Aggregate Functions.

By default, visual totals are disabled. In this case, displayed, aggregated cell values are calculated according to all of a member's descendants, regardless of whether they are viewable. If some members are not viewable, the default setting can cause some cell values to appear incorrect to end users.

Important The default setting (that is, visual totals are disabled) creates security exposures if it allows end users to deduce values for members to which they are denied access. Examples 4, 6, 8, and 9 illustrate this concept.

Default Member

For each dimension you can select a default member. The default member affects the datasets returned by queries on cubes that include the dimension. When the dimension is not displayed on an axis, by default the dataset is filtered (that is, sliced) using the default member. Example 12 illustrates this concept.

If you do not select a default member, the default member is determined by the dimension's Default Member property, which is accessed in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private).

Custom Dimension Security Dialog Box

Custom rules for dimension security are defined in the Custom Dimension Security dialog box. To indicate the levels and members that can be accessed, you can choose from two methods:

Select items by using drop-down lists, a member tree, and other elements of the dialog box. This method is used in the Basic tab.
Write MDX. With this method, you can implement all techniques permitted by the preceding method plus a few, infrequently used techniques. This method is used in the Advanced tab.

Options for visual totals and the default member are specified in the Common tab.

Example Custom Rules in Dimension Security

The examples are for an Offices dimension defined with the following levels:

(All), containing only the All Offices member
Continent
Country
City
Office

Before dimension security is implemented, a dataset returned from a cube with this dimension looks like this. (The dimension is fully expanded to show all members.)

Each example defines alternative dimension security for this dimension. Each example includes one or more MDX expressions in a custom rule for a read permission. The result on the preceding dataset is shown for each example except Example 12, which includes only an expression for a default member.

The examples are applied to read permissions so that the effects of the MDX expressions on the Offices dimension can be visually demonstrated. However, the examples are also applicable to read/write permissions. That is, if an example's expressions were used in a read/write permission, they would allow and deny access to the same members as in the read permission. Exceptions are Examples 11 and 12, which demonstrate options that are defined once per dimension and cannot vary between the read and read/write permissions.

Example 1

This example includes only a top level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box:
```
[Offices].[Office]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Microsoft® SQL Server™ 2000 Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box: no expression specified.
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 2

This example includes only a bottom level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box:
```
[Offices].[Country]
```
- Allowed Members box: no expression specified.
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 3

This example includes only a top level selection and bottom level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box:
```
[Offices].[Country]
```
- Bottom Level box:
```
[Offices].[City]
```
- Allowed Members box: no expression specified.
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 4

This example allows access to some members but does not include a top level selection or bottom level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box:
```
{[Offices].[All Offices].[Africa]}
```
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Note Because visual totals are enabled, the displayed Sales for All Offices is 8400, although the actual Sales is 27300. If visual totals were disabled (the default), a security exposure would exist because end users could calculate the Europe Sales by subtracting the Africa Sales from the actual All Offices Sales.

Example 5

This example denies access to some members but does not include a top level selection or bottom level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box: no expression specified.
- Denied Members box:
```
{[Offices].[All Offices].[Europe].[England].[London].[Office 6],
[Offices].[All Offices].[Europe].[England].[London].[Office 7]}
```
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 6

This example allows access to some members but denies access to a subset of their descendants. It does not include a top level selection or bottom level selection.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box:
```
{[Offices].[All Offices].[Europe].[Italy]}
```
- Denied Members box:
```
{[Offices].[All Offices].[Europe].[Italy].[Milan]}
```
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Note Because visual totals are enabled, the displayed Sales for Italy is 4600, although the actual Sales is 9600. The displayed Sales for Europe is 4600, although the actual Sales is 18900. The displayed Sales for All Offices is 4600, although the actual Sales is 27300. If visual totals were disabled (the default), security exposures would exist. For example, end users could calculate the Milan Sales by subtracting the Rome Sales from the actual Italy Sales.

Example 7

This example includes a top level selection and bottom level selection and allows access to some members between the top level and bottom level.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box:
```
[Offices].[Continent]
```
- Bottom Level box:
```
[Offices].[City]
```
- Allowed Members box:
```
{[Offices].[All Offices].[Europe]}
```
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 8

This example includes a top level selection and bottom level selection but denies access to some members between the top level and bottom level.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box:
```
[Offices].[Continent]
```
- Bottom Level box:
```
[Offices].[City]
```
- Allowed Members box: no expression specified.
- Denied Members box:
```
{[Offices].[All Offices].[Africa].[Kenya]}
```
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.).
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Note Because visual totals are enabled, the displayed Sales for Africa is 4000, although the actual Sales is 8400. If visual totals were disabled (the default), a security exposure would exist because end users could calculate the Kenya Sales by subtracting the Egypt Sales from the actual Africa Sales.

Example 9

This example includes a top level selection and bottom level selection. It also allows access to some members between the top level and bottom level but denies access to a subset of their descendants.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab

Top Level box:
```
[Offices].[Country]
```
Bottom Level box:
```
[Offices].[Office]
```

Allowed Members box:

{[Offices].[All Offices].[Africa].[Egypt],
[Offices].[All Offices].[Europe].[England]}

Denied Members box:

{[Offices].[All Offices].[Africa].[Egypt].[Cairo]}

Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Note Because visual totals are enabled, the displayed Sales for Egypt is 1100, although the actual Sales is 4000. If visual totals were disabled (the default), a security exposure would exist because end users could calculate the Cairo Sales by subtracting the Alexandria Sales from the actual Egypt Sales.

Example 10

This example includes a top level selection and bottom level selection. It also denies access to some members between the top level and bottom level but allows access to a subset of their descendants.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab

Top Level box:
```
[Offices].[Continent]
```
Bottom Level box:
```
[Offices].[City]
```

Allowed Members box:

{[Offices].[All Offices].[Europe].[Italy].[Rome]}

Denied Members box:
```
{[Offices].[All Offices].[Europe]}
```

Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Note Because visual totals are enabled, the displayed Sales for Europe is 4600, although the actual Sales is 18900. The displayed Sales for Italy is 4600, although the actual Sales is 9600.

Example 11

This example includes an MDX expression for a customized visual total.

The Europe Manager role is authorized to access values for the Europe member, all its descendants, the Alexandria member, and all its descendants. It is not authorized to access values for the Africa member. Allowing access to Alexandria implicitly allows access to Africa, but selecting the Continent level for visual totals ensures that the role cannot access actual values for Africa. As long as the role retains access to Europe and all its descendants, this visual totals selection will not impede the role's ability to view actual values for Europe.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box:
```
{[Offices].[All Offices].[Europe],
[Offices].[All Offices].[Africa].[Egypt].[Alexandria]}
```
- Denied Members box: no expression specified.
Common tab
- Visual Totals area:
```
[Offices].[Continent]
```
  The selected option is Custom - Show visual totals starting at the following level and above.
- Default Member area: no expression specified.

In a read permission, these expressions cause the example dataset to be modified as follows. (The following dimension is fully expanded to show all viewable members.)

Example 12

This example includes an MDX expression that specifies a default member. The default member is selected from the sample dimension.

The following MDX expressions are specified in the following boxes and areas of the Custom Dimension Security dialog box:

Advanced tab
- Top Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's highest level:
```
[Offices].[(All)]
```
- Bottom Level box: no expression specified. However, by default the following expression is supplied by Analysis Services to represent the dimension's lowest level:
```
[Offices].[Office]
```
- Allowed Members box: no expression specified.
- Denied Members box: no expression specified.
Common tab
- Visual Totals area: no expression specified, but visual totals are enabled. (That is, Enable - Show visual totals is selected.)
- Default Member area:
```
[Offices].[All Offices].[Africa]
```
  The Define default member and specify using MDX option is selected.

Whenever the Offices dimension is not projected on an axis, by default datasets are filtered (that is sliced) by the Africa member. Cell values in the dataset reflect Africa but not Europe.

For example, if Offices and Time are the only dimensions in the cube, and Sales is displayed with the Time dimension on the y-axis, the displayed Sales for the All Time member is 8400. The Sales for All Time and All Offices is 27300, but the dataset does not display this value because Europe values are excluded.

Custom Rules in Dimension Security

Analysis Services