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)
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.
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.
Database and cube level security can be maintained in either SSDT or Management Studio (if you are using a SSAS Project, which helps with deployment and version control, remember each deployment will overwrite the current security unless you use the deployment. My preference is to use SSDT, although the screens are very similar in either Management Studio or SSDT. In order to create a new role, simply right click on Roles and Select New Roles.
As shown on the below screen print, step 1 is to define the Role Name in the properties windows; use a name which is both descriptive and meaningful. If the properties window is not visible, select View > Properties (or hit F4). Next, fill in the Role Description as needed. As important as the name is, the three other check boxes below the role description play a vital role in defining database level access to the members of this role. Each check box is described next:
- Full control: This permission grants full access to this particular SSAS database. Members with this permission have similar access rights as the server role noted above. However, these permissions apply to this database only, and not to all the databases within the SSAS instance. Full control grants members access to add other users and to process and maintain databases, cubes, and dimensions.
- Process database: This permission allows a member to process this database and its related cubes and dimensions.
- Read definitions: This permission allows role members to read the database metadata. Granting this access, though, does not provide access to read the metadata of other objects within the database such as cube meta data or dimension metadata. This lower level meta data access will be discussed later in the article.
Often, none of these check boxes are checked for regular users of the cube.
Switching to the members tab allows the administrator to add specific users to the role. Membership can be added either by typing the individual users or by using the windows Advanced > Find option. Again, you can add active directory groups, local windows groups, domain users, or local users; however, SQL users cannot be used! The Add members processes is illustrated in the following screen print. Of course for manageability, using groups is recommended.
Moving on to the Data Source tab, intuitively, it would seem that a user would need access to the data source. However, granting access to the data source actually grants permissions to the underlying data sources of the SSAS project or database.
Switching to the Cubes tab finally provides methods which will grant read data access to role members.
- None: Role members are not able to access this particular cube.
- Read: Users can read data from the cube, but not write data back to the cube.
- Read/Write: Users can read data from cube and write data back to the cube.
- Role members are able to process this particular cube.
Remember, if access is not specifically granted to the cube, end users will not even see the cube from their client applications.
The Cell Data tab affords administrators the ability to granularly set read, read-contingent, and read/write permissions to role members. Once any of these options are enabled by selecting the appropriate check box, the administrator must enter a MDX expression which defines the cells which are available or restricted for the role members.
The Dimension tab assigns privileges to role members at the dimension level. First, two options exists at the dimensions level, either Read or Read and Write. Roles whose access is set to none will not even see the dimension marked as such. Furthermore, roles can be granted Read Definition access which allows for the role members to read the metadata concerning the dimension. Similar to the Cubes Access Rights, granting Process rights to a role, allows the members to process that particular dimension. The next tab to the right of the dimension tab is the Dimensions data tab; moving from dimension security at the dimension level, the dimension data tab allows the administrator to restrict or deny access to certain attribute values.
One caveat in the use of Dimension Data security; total rows will display the total for all values in the dimension even those that restricted which in turn could allow end users to determine the value of the restricted attributes.
Implementing SSAS Security is as important as every other methods of restricting organizational data. AS a DBA/ DWA, we are ultimately responsible for providing not only the correct and appropriate data (all very quickly), but we also must provide such data using a method and path which appropriately protects access to a SSAS database with such data. Therefore, SSAS allows us to setup the following security points:
- who administers the database
- who processes the databases, cubes, and dimensions
- who can access the meta data about the cubes and dimensions
- who can read data from the SSAS database
- including drill through
- dimension level and dimension data security