Introduction to Security in Analysis Services
January 22, 2010
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.
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:
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:
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.
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:
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.
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.