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.
See Also
Tasks
Granting Custom Access to Dimension Data
Concepts
Example 2—Explicitly Specifying a Denied Set
Example 3—Using the Except Function to Exempt Members from a Denied Set
Example 4—Using the Exists Function to Exempt Members from a Denied Set
Example 5—Using the Exists Function to Specify an Allowed Set
Example 6—Using the Exists and Except Functions to Specify Allowed and Denied Sets