Granting Custom Access to Dimension Data
After a Microsoft SQL Server 2005 Analysis Services (SSAS) database role has read or read/write permission to the dimensions in a cube, you can define security on each dimension attribute member (also called dimension security). By default, a database role has access to all members of all dimension attributes in a cube to which they have read access. You can define a specific set of attribute members for each dimension attribute to which role members have specific access rights (AllowedSet) or to which they are specifically denied access rights (DeniedSet). You can also define the default member for each attribute hierarchy; by default, the All member is the default member. If you deny read permissions to certain attribute members, you might want to have the value for the All member be the aggregate of the members to which the role members have access to rather than the aggregate of all of the members of the attribute hierarchy. To specify this behavior, you enable VisualTotals. When you enable VisualTotals, the aggregate is calculated at query time rather than retrieved from pre-calculated aggregations.
Note
The type of access that members of a dimension role have is based on the dimension access granted, either read or read/write.
Understanding the IsAllowed Property
The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.
Understanding the AllowedSet Property
The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.
Limiting the allowed set for one attribute affects the visibility of other attributes. For example, suppose that the allowed set for the Customer attribute includes only some attribute members, but the allowed set for the City attribute includes all attribute members. In this case, the only members of the City attribute that will be visible are those cities that have customers in the allowed set of the Customer attribute. If there is a city that has no customers, attribute members for that city will not be visible. In other words, an attribute member can only be visible if that attribute member exists with at least one member of the allowed set.
Note
If you define an empty set of attribute members, no members of the attribute will be visible to the database role. The absence of an allowed set is not interpreted as an empty set.
Understanding the DeniedSet Property
The DeniedSet property uses an MDX expression to determine the attribute members to which a database role is explicitly denied access (the denied set). The denied set can include no, all (default), or some attribute members. By default, no denied set is defined.
When the denied set contains only a specific set of attribute members, the database role is denied access only to those specific members. Specifically defining a denied set may affect the accessibility of attribute members that are added after the denied set is defined.
When you define a specific set of attributes in the denied set, the effect of this denied set on the accessibility of other attributes depends on whether the ApplyDenied property is enabled. For example, suppose there is a denied set on the State attribute and the ApplyDenied property is enabled. In this case the database role will not be able to access any of the Customer attributes for those states within the denied set.
Understanding the ApplyDenied Property
The ApplyDenied property indicates whether members of a denied set are used in determining whether members of an attribute hierarchy are visible to the database role. By default, the ApplyDenied property is set to True (enabled) for each attribute hierarchy.
Note
Unlike the denied set whose affect depends on the ApplyDenied property, the allowed set is always applied in determining whether members of an attribute hierarchy are visible to the database role.
When the ApplyDenied property is enabled and there is a denied set, the database role will not be able to access any members of an attribute hierarchy if that hierarchy contains any of the members in the denied set. For example, the ApplyDenied property is enabled and the denied set is made up of states in the State attribute. In addition to not being able to access the State attribute, the database role will not be able to access the Customers attribute for any state within the denied set.
When the ApplyDenied property is disabled and there is a denied set, the database role will be able to access any members of an attribute hierarchy even if that hierarchy contains any of the members in the denied set. For example, the ApplyDenied property is disabled and the denied set is made up of states in the State attribute. Although the database role will not able to access the State attribute, the database role will still be able to access the Customers attribute for any state within the denied set.
Understanding the VisualTotals Property
The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.
By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.
However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.
If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.
Understanding the DefaultMember Property
The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:
If the database role defines a default member for the attribute, Analysis Services uses this default member.
If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
For example, a database role specifies Male as the default member for the Gender attribute. Unless a query both explicitly includes the Gender attribute and specifies a different member for this attribute, Analysis Services would return a data set that included only male customers. For more information about setting the default member, see Defining a Default Member.
Setting Access Permissions on a Member in a Dimension
Before setting access permissions on a member in a dimension, you may want to review some examples of how different access settings affect the result set that is returned when querying members. The following topics provide these example settings:
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
After you understand how the various access permissions work, you are ready to grant those permissions. To grant access permissions to a member in a dimension, a user must be a member of the Analysis Services server role or a member of an Analysis Services database role that has Full Control (Administrator) permission.
When you use Business Intelligence Development Studio to grant access permissions to a member in a dimension, you have to use the standard options on the Basic tab of the Dimension Data Access tab or use the more customized options on the Advanced tab.
Important
If a Microsoft Windows user or group belongs to multiple database roles, the effective permissions for the user or group are additive across all database roles (a union of permissions). If one database role denies the user access to an attribute member, but a different database role granted the user access to that attribute member, the user will have access to the attribute member.
To use the Basic tab to give a database role access to a member in a dimension
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Dimension Custom Data Access in the Select a Page pane, select the dimension in the Dimension list, and then select Allow Attribute on the Advanced tab.
Selecting this option sets the IsAllowed property to True.
In the Attribute list, select the attribute for which you want to specify the members that should be viewed by the database role.
To specifically deny access to certain attribute members, enter the MDX expression for those attribute members in the Denied Member Set box. All other attribute members will be viewable.
- To specifically grant access only to certain members, enter the MDX expression for those attribute members in the Allowed Member Set box. No other attribute members will be viewable.
To use the Advanced tab to give a database role access to a member in a dimension
In SQL Server Management Studio, connect to the instance of Analysis Services, expand Roles for the appropriate database in Object Explorer, and then click a database role (or create a new database role).
Click Dimension Custom Data Access in the Select a Page pane, select the dimension in the Dimension list, and then select Allow Attribute on the Advanced tab.
Selecting this option sets the IsAllowed property to True.
In the Attribute list, select the attribute for which you want to specify the members that should be viewed by the database role.
To specifically deny access to certain attribute members, enter the MDX expression for those attribute members in the Denied Member Set box. All other attribute members will be viewable.
To specifically grant access only to certain members, enter the MDX expression for those attribute members in the Allowed Member Set box. No other attribute members will be viewable.