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.