Example 1—Explicitly Specifying an Allowed Set

In this example, a database role has the dimension security settings that are shown in the following table.

Attribute

IsAllowed

AllowedSet

DeniedSet

ApplyDenied

VisualTotals

State

True

True

False

City

True

San Jose

True

False

Gender

True

True

False

The results of these security settings are the following:

  • Only the California member of the State attribute will be visible. (The allowed set for the City attribute only includes San Jose—a city in California. This means that the allowed set not only limits the City attribute, but also limits the visibility of the State attribute to only California.).

  • Only cities that existed in the dimension when the allowed set was defined for the City attribute will be visible. Newly added cities will not be visible.

Reviewing the Result Set

Based on these dimension security settings for the database role (and based on cube access to all cells), a query on all members returns the result set that is shown in the following table.

All Level

State

City

Gender

Sales Amount

All Offices

27300

California

12900

San Jose

4200

Male

2000

Female

2200

Depending on how a user needs to use the information in the result set, the result set shown in the previous table may be too limiting for the user. For example, the total for all sales for all offices includes the sales in states to which the database role member has no permission. Therefore, a member of this database role would not be able to see the breakdown of sales by state. Similarly, the total for all sales in California includes sales for cities to which the database role does not have permission to view. Therefore, a member of this database role cannot break down the sales total by city.