Permissions and Access Rights (Analysis Services - Multidimensional Data)

In Microsoft Analysis Services, roles allow administrators to define levels of security on objects in an Analysis Services database for different Windows users and groups. Each object can have a single permission associated with it per role, and each permission can have one or more access rights associated with it. In addition, a Windows user or group can be associated with more than one Analysis Services role, giving you the capability to combine permissions and access rights for complex security models in business intelligence applications.

Access Rights

The following table describes the set of access rights available for permissions associated with objects in Analysis Services databases.

Access Right

Description

Access

Provides the ability to access metadata for an object. The following types of access are supported:

  • None denies access to the object.

  • Read allows members of the role to read from the object.

  • ReadContingent allows members of the role to read a cell value only if the user can access all the cells from which the value is derived. ReadContingent provides Read access for any cells specified by this permission that are not derived from other cells.

    For example, where the value of the Profit cell is calculated from the value of the Sales cell minus the value of the Costs cell, a user can read the Profit cell only if cell access is set to Read (or Write) for both the Sales and Costs cells.

  • ReadWrite allows members of the role to read from and write to the object.

Administer

Indicates whether members of the role can administer the object.

Administer permission gives members of the role complete access to all objects contained in the object.

AllowBrowsing

Allows members of the role to browse the data in a mining model.

AllowDrillthrough

Gives members of the role permission to drill through from a mining model to the underlying data.

AllowedSet

The AllowedSet permission defines the members of an attribute that a member of the role can view. For example, if the allowed set in [Customer].[CountryRegion] is {Canada}, then the members of the role have access to all the provinces and cities of Canada.

For a parent-child hierarchy, the allowed members are those defined by the set plus the ascendants of the parent-child hierarchy that exist with those members. If a member of a parent-child hierarchy is not in an allowed set, its children — other than the data members — are not accessible to the role. The data members are still accessible because they belong to the key attribute of the dimension.

The default if no set is defined for the AllowedSet permission is the set of all the attribute members.

AllowPredict

Predict permission for a mining model gives members of the role permission to predict based on the mining model.

DefaultMember

The DefaultMember permission defines the default member of the dimension. The default member affects the datasets returned by queries on cubes that include the dimension. When the dimension is not displayed on an axis, by default the dataset is filtered (that is, sliced) using the default member.

DeniedSet

The DeniedSet permission defines the members of an attribute that a member of the role cannot view.

Process

Process permission for an object gives members of the role the permission to process the object. It also grants permission to process all child objects within the object unless this permission is explicitly denied on a child object. Process permission does not grant members of the role access to the data or metadata of the object.

ReadDefinition

Indicates whether members of the role can read the metadata that defines the permission object. This property setting is inherited by objects contained in the object.

VisualTotals

The VisualTotals permission for dimension data defines how data is aggregated for attributes. This is an MDX expression returning True or False. If VisualTotals is False, data is aggregated on all members of attributes of the dimension regardless of whether they are visible to members of the role. If VisualTotals is True, data is aggregated only for those members of the granularity attribute of the dimension to which the role has read access. For example, if Customer Name is the granularity attribute and VisualTotals is set to True for the City attribute, each city will be the aggregation of data for the customers to which the role has read access.

The default setting is False.

Permissions

The following table describes permissions available in an Analysis Services database, as well as the access rights managed by each permission.

Permission

Access Permissions

Database

Database access defines access to objects and data in an Analysis Services database.

Available access rights include:

  • Administerr

  • Process

  • ReadDefinition

Data source

Data source access defines access to data sources in an Analysis Services database.

Available access rights include:

  • Access

    (None or Read)

  • ReadDefinition

Cube

Created at the cube level when a database role is assigned to a cube, a cube role applies to only that cube. Defaults in a cube role are derived from the database role of the same name, but some of these defaults can be overridden in the cube role. A cube role contains additional options such as cell security that are not contained in a database role.

You can exercise great flexibility in granting both read and read/write access to portions of cubes. You can specify which dimension members and cube cells a role can view and update. For more information, see Dimension Security and Cell Security.

Available access rights include:

  • Access

    (None, Read, or ReadWrite)

  • LocalCube/DrillthroughAccess

    (None, Drillthrough/Drillthrough and Local Cube)

  • Process

Cell

Cell data access defines access to cells in a cube. There are three types of access to cells in a cube:

  • Read

  • ReadContingent

  • Read/Write

Cell security in a cube is defined for each type of cell access with an MDX expression that resolves to True or False for each cube cell. Any nonzero value in a numeric expression is evaluated as True while zero is evaluated as False. Access is allowed when an expression resolves to True and denied when an expression resolves to False.

Available access rights include:

  • Access

    (None, Read, ReadContingent, or Read/Write)

Dimension

Dimension access properties define access to the database dimensions in a database irrespective of their participation in cubes. Dimension access allows users that are members of a role to browse a dimension in client applications. Cube dimension permissions can also be specified that override the database access permissions for a role when a dimension is accessed in a particular cube.

Available access rights include:

  • Access

    (Read or Read/Write)

  • Process

  • ReadDefinition

Attribute

Dimension data access controls which dimension attributes can be accessed by members of a role. Allowing or denying access to an attribute defines access to levels in the dimension hierarchies based on that attribute. If a role is denied access to an attribute, then it is denied access all levels derived from the attribute.

If denying access to an attribute creates a hole in a hierarchy, then the entire hierarchy is invalidated and is no longer accessible to members of the role. For example, in the hierarchy CountryRegion-State-City-Name, the levels State and Name are not contiguous levels in the hierarchy. Denying access to the City attribute therefore leaves a hole and invalidates the hierarchy. In contrast, denying access to the CountryRegion attribute would create no hole and leave the valid hierarchy State-City-Name of contiguous levels. Similarly, denying access to the Name attribute retains the valid hierarchy CountryRegion-State-City.

When you allow members of a role access to an attribute, you can allow or deny access to selected members of the attribute.

Available access rights include:

  • AllowedSet

  • DefaultMember1

  • DeniedSet

  • VisualTotals

Mining Structure

Mining structure access determines permissions to mining structures and mining models and their data.

Available access rights include:

  • Access

    (None or Read)

  • Process

  • ReadDefinition

Mining Model

Mining structure access determines permissions to mining structures and mining models and their data.

Available access rights include:

  • Access

    (None, Read, or Read/Write)

  • Browse

  • Drill Through

  • ReadDefinition

1 The DefaultMember access right defines the default member of the dimension. For more information, see Define a Default Member.

Permissions and Inheritance

When an object contains other objects (such as cubes or dimensions in a database) the Administer, Process and ReadDefinition permissions on the parent object are inherited by the child objects.

Permission

Inheritance

Administer

Members of the Analysis Services server role have permission to administer a server, therefore they also have full access to all the objects on the server. Members of an Analysis Services database role granted permission to administer a database have full access to all the objects in the database.

Process

By default, the Process setting on an object applies to any child object. This property can also be set on a child object to override the permission inherited from the parent object.

  • If a user is permitted to process a cube but not permitted to process a dimension in the cube, then the user can successfully process the cube only if the dimension is already processed.

  • When a user processes a database only those cubes and dimension in the database which the user is permitted to process are processed.

ReadDefinition

By default, the ReadDefinition property setting on an object is inherited by any child objects. This property can also be set on a child object to override the permission inherited from the parent object.

Multiple Roles and Permissions

A user can belong to more than one role in an Analysis Services database. Permissions across multiple roles are additive. If a role provides access to an object, then a member of that role has access to the object regardless of whether or not that member is explicitly denied access to the object in another role.