Introduction to Security in Analysis Services

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.

Script of an Example Admin Role

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles