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.
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.
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.
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.