Example 2—Explicitly Specifying a Denied 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 |
Oregon |
True |
False |
|
City |
True |
True |
False |
||
Gender |
True |
True |
False |
The results of these dimension security settings are the following:
- The Oregon member of the State attribute will not be visible to this database role. (Oregon has been specified in the denied set for the State attribute.)
- None of the members of an attribute hierarchy that includes Oregon (in this case, the members of the City attribute) will be visible to this database role. (The ApplyDenied property is enabled.)
Reviewing the Result Set
Based on these dimension security settings for the role (and based on cube access to all cells), the following result set is returned from the cube when all members are queried.
| All Level | State | City | Gender | Sales Amount |
|---|---|---|---|---|
All Offices |
27300 |
|||
California |
12900 |
|||
San Jose |
4200 |
|||
Male |
2000 |
|||
Female |
2200 |
|||
San Francisco |
|
4400 |
||
Male |
2200 |
|||
Female |
2200 |
|||
Los Angeles |
|
4300 |
||
Male |
2100 |
|||
Female |
2200 |
|||
Washington |
|
8700 |
||
Seattle |
|
4500 |
||
Male |
2200 |
|||
Female |
2300 |
|||
Tacoma |
|
4300 |
||
Male |
2000 |
|||
Female |
2300 |
Notice that although the result set does not explicitly provide totals for Oregon, this information can still be deduced by a member of this database role. A member of this role can view the total sales for all offices and can view the total sales for both California and Washington. Therefore, a member of this database role would be able to infer the total sales for Oregon, even though that member is explicitly denied permission to see sales within Oregon. To prevent a member of this database role from being able to deduce the totals for Oregon, enable the VisualTotals property.
See Also
Tasks
Granting Custom Access to Dimension Data
Concepts
Example 1—Explicitly Specifying an Allowed 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