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:
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 (well 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