Introduction to Security in Analysis Services

Join BI Architect Bill Pearson in this introduction to Security in Analysis Services 2008. Here we explore security concepts and implementation, and look forward to hands-on practice with security in other articles of this subseries.

This article introduces security in Analysis Services. Here, we will introduce security and discuss the basic concepts and considerations surrounding the implementation of security in Analysis Services.

Note: For more information about my MSSQL Server Analysis Services column in general, see the section entitled “About the MSSQL Server Analysis Services Series” that follows the conclusion of this article.

Introduction

Virtually any of us that have worked with Microsoft, as well as other, applications and operating systems have encountered the type of role-based security that we find in Microsoft SQL Server Analysis Services. In this article, we will introduce a subseries that focuses upon Analysis Services security in general, the member articles of which will provide hands-on exposure to the implementation of security within our cubes.

In this introductory article, we will explore general concepts surrounding security, including:

An overview of Role-based Security in Analysis Services;
The two general types of Roles in Analysis Services;
A discussion of Role and Role Member Objects, focusing upon:

The Server Role
The Database Role

Access Rights and Permissions (including Permissions Inheritance and Permissions Objects)

Introducing Role-Based Security in Analysis Services

Roles are used in Analysis Services to manage security for Analysis Services objects and data. In basic terms, a role associates the security identifiers (SIDs) of Microsoft Windows users and groups that have specific access rights and permissions defined for objects managed by an instance of Analysis Services.

The Two General Types of Roles in Analysis Services

Two general types of roles are provided in Analysis Services:

The Server Role: a fixed role that provides administrator access to an instance of Analysis Services.
Database roles: roles defined by administrators to control access to objects and data for non-administrator users.

Security in Analysis Services is managed by using roles and permissions. Roles are groups of users. Users, also called members, can be added or removed from roles. Permissions for objects are specified by roles, and all members in a role can use the objects for which the role has permissions. All members in a role have equal permissions to the objects. Permissions are particular to objects. Each object has a permissions collection with the permissions granted on that object and different sets of permissions can be granted on an object. Each permission, from the permissions collection of the object, has a single role assigned to it.

Role and Role Member Objects

A Role is a containing object for a collection of users (or members). A Role definition establishes the associated membership of the users in Analysis Services. Because permissions are assigned by Role, a user must be a member of a Role before the user has access to any object.

A Role object is composed of the following parameters:

Name
ID
Members

The members' parameter is composed of a collection of strings. Each member contains the user name in the familiar form of "domain\username". Name is a string that contains the name of the role. ID is a string that contains the unique identifier of the role. The relevant section of a script for a Database Admin role (we’ll discuss these later) I set up in my local environment can be seen in Illustration 1.

Illustration 1: Script of an Example Admin Role

Server Role

The Server Role in Analysis Services security defines administrative access for Windows users and groups to an instance of Analysis Services. Members of this role have access to all Analysis Services databases and objects on an instance of Analysis Services, and can:

Perform server-level administrative functions within SQL Server Management Studio or Business Intelligence Development Studio, including creating databases and setting server-level properties.
Perform administrative functions programmatically with Analysis Management Objects (AMO).
Maintain Analysis Services Database Roles.
Start traces (other than for processing events, which can be performed by a Database Role with Process access).

Every instance of Analysis Services has a Server role that defines which users can administer that instance. The name and ID of this role is Administrators, and unlike Database roles (we discuss these in the next section), the Server role cannot be deleted, nor can permissions be added or removed. In other words, a user either is or is not an administrator for an instance of Analysis Services, depending on whether he or she is included in the Server role for that instance of Analysis Services.

Database Roles

A Database Role in Analysis Services defines user access to objects and data in an Analysis Services database. A Database Role is created as a separate object in an Analysis Services database, and applies only to the database in which that Role is created. Windows users and groups are included in the Role by an administrator, who also defines permissions within the Role.

The Permissions of a Role may allow members to access and administer the database, in addition to the objects and data within the database. Each Permission has one or more access rights associated with it, which in turn give the Permission finer control over access to a particular object in the database.

For more information on Permissions, see the corresponding subsections that follow.

Access Rights and Permissions

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 us the capability to combine Permissions and Access Rights for complex security models in business intelligence applications.

Access Rights

The set of Access Rights available for Permissions associated with objects in an Analysis Services database is described in Table 1 below.

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 Net `Profit` cell is calculated from the value of the `Sales` cell minus the value of the `Costs` cell, a user can read the `Net 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.
ApplyDenied	Determines whether members of the associated attribute that exist with other explicitly denied members cannot be viewed.
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.
IsAllowed	Determines whether access to any member of the attribute is permitted regardless of the settings for a level based on the attribute. If the IsAllowed property is False for the granularity attribute on a dimension, setting VisualTotals on a dimension attribute results in null values for all of its members. For unary operators, when VisualTotals is False, each member is a rollup of all its children. If VisualTotals is set to True, each member is a rollup of permitted children. The default for this property setting is True.
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.

Table 1: The Access Rights Available for Permissions Associated with Analysis Services Database Objects (Source: MSSQL Server 2008 Books Online)

Permissions

Table 2 describes the permissions available in an Analysis Services database, as well as the access rights managed by each permission.

Permission	Access Rights
Database	Database access defines access to objects and data in an Analysis Services database. Available access rights include: Administer 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
flexibility in granting both Read and Read/Write access to portions of cubes. We can specify which dimension members and cube cells a role can view and update. 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 an Analysis Services 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 upon 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 DefaultMember (NOTE: As we mentioned earlier, the DefaultMember access right defines the default member of the dimension.) DeniedSet VisualTotals
Mining Structure	Mining structure access determines permissions surrounding 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

Table 2: Permissions Available in an Analysis Services Database, together with the Access Rights Managed by each Permission (Source: MSSQL Server 2008 Books Online)

Permissions and Inheritance

When an object contains other objects (such as cubes or dimensions in an Analysis Services database) the Administer, Process and ReadDefinition permissions on the parent object are inherited by the child objects. Details of these permissions’ inheritance appear in Table 3.

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

Table 3: Permissions Inheritance Details in Analysis Services (Source: MSSQL Server 2008 Books Online)

In considering permissions as a part of the design, development and maintenance of an Analysis Services solution, it is important to realize that 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.

Permission Objects

Permissions are associated with an object (cube, dimension, others) for a particular role. Permissions specify what operations the member of that role can perform on that object. The objects that can be associated with permissions are as follows:

Database
DataSource
Dimension
Cube
MiningStructure
MiningModel

Possible actions enabled by permissions are presented in Table 4.

Action

Values

Explanation

Process

True or False

Default=False

If true, members can process the object and any object that is contained in the object.

(Process permissions do not apply to mining models. MiningModel permissions are always inherited from MiningStructure.)

ReadDefinition

None, Basic, or Allowed

Default=None

Specifies whether members can read the data definition (ASSL) associated with the object.

If Allowed, members can read the ASSL associated with the object.

Basic and Allowed are inherited by objects that are contained in the object. Allowed overrides Basic and None.

Allowed is required for DISCOVER_XML_METADATA on an object. Basic is required to create linked objects and local cubes.

Read

None or Allowed

Default=None

(Except for DimensionPermission, where default=Allowed)

Specifies whether members have Read access to schema rowsets and data content.

Allowed gives Read access on a database, which allows a user to discover a database.

Allowed on a cube gives Read access in schema rowsets and access to cube content (unless constrained by CellPermission and CubeDimensionPermission).

Allowed on a dimension grants Read permission on all attributes in the dimension (unless constrained by CubeDimensionPermission). Read permission is used for static inheritance to the CubeDimensionPermission only. None on a dimension hides the dimension and gives access to the default member only for aggregatable attributes; an error is raised if the dimension contains a non-aggregatable attribute.

Allowed on a MiningModelPermission grants permissions to see objects in schema rowsets and to perform predict joins.

Note Allowed is required to read or write to any object in the database.

Write

None or Allowed

Default=None

Specifies whether members have Write access to data of the parent object.

Access applies to Dimension, Cube, and MiningModel subclasses. It does not apply to database MiningStructure subclasses, which generates a validation error.

Allowed on a Dimension grants Write permission on all attributes in the dimension.

Allowed on a Cube grants Write permission on the cells of the cube for partitions defined as Type=Writeback.

Allowed on a MiningModel grants permission to modify model content.

Allowed on a MiningStructure has no specific meaning in Analysis Services.

Note: Write cannot be set to Allowed unless Read is also set to Allowed.

Administer

Note: Only available in Database permissions.

True or false

Default=False

Specifies whether members can administer a database.

True grants members access to all objects in a database.

A member can have Administer permissions for a specific database, but not for others.

Table 4: Possible Actions Enabled by Permissions (Source: MSSQL Server 2008 Books Online)

We will examine many of the properties, and the associated settings, that we use in establishing and maintaining security in Analysis Services in other articles of this subseries, where we will gain hands-on exposure to working with role-based security (and related subjects) in a practical environment.

Conclusion

In this article, we introduced general concepts surrounding security in Analysis Services 2008. We noted that our introduction to security is intended to serve as a lead-in to more detailed exploration of various concepts surrounding security in other, independent articles of this subseries that examine the implementation and maintenance of security, as well as hands-on sessions focused upon various tasks surrounding security.

After exploring general concepts surrounding security, we performed a brief overview of Role-based security in Analysis Services. We next examined the two general types of Roles in Analysis Services. We then discussed Role and Role Member Objects, focusing upon the Server Role the Database Role. Finally, we explored Access Rights and Permissions, including Permissions Inheritance and Permissions Objects.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The monthly column is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube.

» See All Articles by Columnist William E. Pearson, III