Instant SQL Server Analysis Services 2012 Cube Security

In order to fulfill the criteria we need the following prerequisites and steps:

In order to create security it is essential to create a Windows user and group that will enable the remaining recipes in this book to proceed. Here we will browse through the Windows Server 2008 R2 operating system options.

Once the prerequisites mentioned in the preceding Getting ready section are completed, follow these steps:

The preceding steps will be the starter steps to design and create security roles in an OLAP database.

The steps to create a specific security role within the analytical database area needs elaboration, as it is different from the Server role and the Database role from the relational database world. Similarly, security roles also play a very important role in the SSAS world. To manage security for Analysis Services objects and data, a role can be associated to the SIDs of Windows operating system users and groups that have specific access rights and permissions defined for the objects managed by SSAS instance.

The Analysis Services instance property pages from SSMS will show (and contain) a subset of properties (by default) that are likely to be modified. In order to obtain a full set of properties, click on the Show Advanced (All) Properties checkbox at the bottom of the server properties page. Similarly, a full set of properties can also be found in the msmdsrv.ini file.

To explain the topic further, a role is a containing object for a collection of users that are classified as members. In this recipe we will overview the steps in creating security roles within Analysis Services database.

The prerequisite for this recipe can be obtained from the Getting ready section in the Designing and creating security roles in an OLAP Database recipe.

Once the above prerequisites are completed follow the steps below:

As you may have observed that the preceding steps confirm the creation of the Reader privileged role, for the sake of multiple recipes specific to Security Roles let us create two additional roles that will be different to each other on the AS database.

Follow these steps for creating two additional roles that will be different to each other on the AS database

The preceding steps enable us to create the necessary security roles that are essential to manage security in Analysis Services objects and data.

The steps to create a specific security role within analytical databases needs elaboration, as it is different from the Server role and the Database role from the relational database world. Similarly, security roles also play very important roles in the SSAS world.

To manage security for Analysis Services objects and data, a role can be associated to the SIDs of Windows operating system users and groups that have specific access rights and permissions defined for the objects managed by SSAS instance.

The two different types of permissions that can be granted to a user are classified as Administrative Security and Data Security. Again such a level of permissions varies from the object of an Analysis Services Database. For instance a user can be granted to execute Local Cube read permissions on the Data Security level and Read Definition on the Administrative Security level.

Security is managed by using roles and permissions. Roles are groups of users and users can be referred to as members. Such members can be added or removed from the roles.

Permissions that are specified by roles can use the objects for which that role has permissions, as they apply to all the members in a role who have equal permissions to the objects. Also, the permissions collection of the object has a single role assigned to it.

In this recipe we will overview the dimension access privileges. As an administrator your responsibility is to grant relevant read or write permissions to some or all the members of any dimension in the cubes to which the database role has access permissions.

The prerequisite for this recipe can be obtained from the Getting ready section in the Designing and creating security roles in an OLAP database recipe.

Once the prerequisites are completed follow the following steps:

In this recipe and the previous one (Creating security roles) we have performed the necessary steps to create security roles and granted required privileges for these roles.

To re-iterate what follows from the preceding steps, we have already created three roles on AdventureWorksDM database, named as DBIA_Reader, DBIA_Processor, and DBIA_User.

This means by default only authenticated users who have rights can establish the connection to the Analysis Services instance. The access privileges are determined by the rights that are assigned on instance level and database level. This process will work directly from the Windows operating system or through the membership in the Windows role.

Multiple privileges such as read and/or process can be granted to each role within the AS database. A database role can specify whether the associated members have permission to view or update members in specified database dimensions.

Similarly, on a cube level that is based on a single-database dimension will have multiple cube dimensions defined. The permissions that are specified for the database dimensions apply to all the cube dimensions unless these permissions are overridden for one or more of the cube dimensions.

By default, members with at least read access to an attribute member have read access to all cube cells related to the attribute member. You can limit cell access to specific cells. We will overview this topic under the Securing data at the cell level (Intermediate) recipe.

As we have cooked through a few recipes from the basics to construct the administer OLAP security management, the recipes associated in this security essentials will cruise through dimension to cell level within an OLAP database.

By default the best practices (and Microsoft) recommend to use Windows authentication based security. In some occasions this may not be possible by design or due to some architectural restrictions where the integrated security feature is used. Irrespective of either authentication methods it is highly recommended to use account credentials to access the data on database.

An authentication and access privilege brings up an impersonation topic that works seamlessly from the database level down to the cell level. In this book the recipes will highlight the role-based security from dimension to cell level and database to cube level. Also, covered are the features surrounding how to restrict access to dimensions and define default members (full and partial).

The following recipes will go through the security aspects on dimension level, how we can specify whether its members have permissions to view or update dimension members in a specific database dimension. For instance, a database role is not granted permissions to view or update a particular dimension. In this case some or all of the dimension's members of the database have no permission to view the dimensions of any of its members.

Similarly to the cells level a database role can specify whether its members have read, read contingent, or process permission on some or all of the cells within a cube. On the contrary, if a database role is granted permission to view members of a dimension, cell-level security can be used to limit these cell members from the dimension that the database role members can view.

In managing role-based security the two recipes Managing role-based security – dimension to cell level (Advanced) and Managing role based security – database to cube level (Advanced) are inter-related. In order to accomplish these tasks we will perform the steps as a two-fold process. Initially we will work upon database to cube level based security that will enable us to perform dimension to cell level steps.

Once the above prerequisites are completed follow these steps:

The first steps in managing role-based security access to cube level is essential that will in turn enable the process to define cell based access on the Analysis Services database.

The preceding set of steps will grant necessary access privileges to the database roles. However, it is essential to highlight the associated action within the process; the Drillthrough option at the Local Cube/Drillthrough Access column is classified as an action. This can be termed as an end user-initiated operation upon a selected cube or portion of a cube. As per the settings or code actions, an operation can start another application with the selected item as a parameter or retrieve information about the selected item, which in turn deviates the security policies.

As per the Microsoft documentation the actions are differentiated as three types:

The Drillthrough action is also used within a mining model, which is termed as when a user browses through the model, they can retrieve detailed information about the cases that were used to create the model. In relevance to this, necessary permissions and the structure must have already been processed.

By default the database role, which grants read or write permissions to a cube and all the associated role members, will have relevant access to all cube cells unless permissions are specifically restricted on specific cells.

In order to accomplish such cell-based privileges, or if you want to grant write permission on specific cells in the cube to database role members, you can refer to the following recipe.

Once the prerequisites are completed, perform the following steps:

As we talked through previous recipes in SSAS, once a database role is created and granted with read or process permissions to a cube, then role members will have access to view all the cell data.

In order to restrict such an action and access to specific cells, we must specifically restrict cell access. Within these cells, certain calculations can be derived using measures, which represent columns that contain data (numeric) that can be aggregated. By default a measure is mapped to a column in a fact table.

The previous steps define the simple process of granting access within a cube for cell data; in additional to these actions a certain level of access can be granted to specific members of a database role.

The flexibility of security provision, by allowing access to cells for specific members in some dimensions and denying access to cells for specific members, is possible as per the previous steps. By default all the members associated in that database role will have relevant privileges on all the cube cells within that cube, if no members are entered. The option within the Allow reading of cell content contingent on cell security box inherits such actions of denials. For certain members you can create an MDX expression that allows or denies access to any possible combination of cells.

In certain cases, the permissions that are granted on specific cells cannot exceed the permissions associated to a database role on the cube. In this case the permissions that were set on cube level will be used. For instance, a database role has read/write permissions on a cell, but that same role has only read permission on the cube; in that case, the cell data permission will be set to read only.

Although a database role may have access to cell data, that role will not have access to dimension data unless the role has been given read or read/write permissions to the dimension data separately. Access to cube data can restrict access to dimension attributes to which the database role has access, but cannot extend access to dimension attributes to which the database role does not have access.

The recipe steps are bifurcated as basic and advanced; this will enable us to grant database role access to a member in the dimension:

The preceding steps complete the initial process in granting partial access restrictions on selective dimensions. Now let us continue with the steps to give a database role access as full permissions to a member in a dimension:

The previous steps are self-explanatory on granting necessary access restrictions on dimensions and defining default members.

The access restrictions on the dimensions can be controlled by using the database roles for the relevant members within that dimension. By using a database role, we can choose whether the members have permission to view or update dimension members within that database dimension. Further within a particular dimension to which a specific database role has been granted rights as FULL, that role can be granted permission to view or update specific dimension members only instead of all dimension members.

Let us talk about higher level permissions, such as a database role with permissions to access a cube. In such cases, the access permissions to dimensions in that cube are inherited from the permissions that are set on the dimension within a particular cube. The default structure of access restrictions is controlled from the database role that has access to all the members of all dimension attributes in a cube to which they have relevant access level.

The permissions are controlled by two properties, which are set to AllowedSet and DeniedSet. We can set the members in any of these attributes to which the role members have access rather than all of the members of the attributed hierarchy.

The AllowedSet property uses MDX that will determine which attribute members can be viewed by the database role. The set can include no members (the default), all members, or some attribute members. In case we allow access to an attribute and do not define any members of the allowed set, access to all members is granted. As a best practice, it is essential to outline specific attribute members added after the allowed set is defined.

Similarly, the DeniedSet property contains only a specific set of attribute members. In such cases, the database role is denied access only to those specific members. The definition of a denied set will affect the accessibility of attributed members that are added after the denied set is defined.

The following prerequisite is essential for our recipe to continue the recipe:

The following steps are continued from the previous recipe, but I believe it is necessary to reiterate them from the beginning. Hence, this recipe's steps are listed as follows:

Now let us continue with the Cell Data options:

This completes the steps to secure the data at the cell level using a defined role in the Analysis Services database.

There are a few guidelines and some contextual information that will help us understand how we can best secure the data in a cell.

Nevertheless, whether the database role has read, read-contingent, or read/write permissions to the cell data, we need to ensure that we are granting permissions to derived cells correctly. By default, a derived cell obtains the relevant data from the other cells. So, the appropriate database role has the required permissions to the derived cell but not to the cells from which the derived cell obtain its values.

Irrespective of the database role, whether the members have read or write permissions on some or all the cells within a cube, the members of the database role have no permissions to view any cube data. Once the denied permissions on certain dimensions are effective, the cell level security cannot expand the rights of the database role members to include cell members from that dimension.

The blank expression within the relevant box will have no effect in spite of clicking on Enable read/write permissions. How this is effective is discussed in the following recipe.

Perform the following steps:

Now let us continue with the Cell Data options:

This completes the steps to prevent access to certain cell data using a defined role in the Analysis Services database.

As we discussed in the previous recipe, irrespective of a database role with read or read-contingent permission to the cell data, there are certain steps that we have followed in this recipe to restrict the access to certain cell data and measures.

From the steps mentioned, we have selected the [Dim Product] measure for the read-contingent permission and the [Dim Customer] measure for the read/write permissions. In this case, the DBIA_Processor role has permissions for the cells of the product measure assigned as read-contingent that will allow reading of cell content contingent on cell security. This identifies the cells to which the database role has allocated permissions as per the steps. These measures are visible to the database role; however, the role does not have permissions to write on cells in product measure.

Just a note on how read-contingent permission works; with any cell that is specified as read-contingent, to which a database role has been assigned, this permission is only viewable if one of the following conditions is met:

The access permission for a specific database role cannot exceed the permissions that are granted to a database role on the entire cube. Also, access to certain cell databases does not give access to the dimension data. Access is granted to the dimension attributes to which the database role has access, but it cannot extend access to the dimension attributes to which the database role does not have access.

As we have seen in the previous steps, the MDX is necessary to allow that database role to contain relevant permissions. If the default allowed set is empty, the corresponding database role will be checked against permissions and no data will be allowed from the cell due to the empty set.

Perform the following steps:

As we have observed, with most recipes a database role provides all the necessary steps to enable the cube access within that Analysis Services (AS) instance.

Irrespective of the default settings for a database role, the privileges can be assigned to any cube (such as virtual or linked cubes) in the AS database. The recipe steps will grant the specified end users or groups in the database role access to the cube.

By default, the database role will not specify any restriction for the end users in the cube and a certain database role can view all members in the associated cube.

By design the end users in a cube role will be able to view all cells in the associated cube.

When the cube is set with write-enables, and also the role has been granted read/write access to the cube, the end users will be able to update the cube cells. In order to specify certain privileges for each cell, the security process will set relevant steps to define the access.

The security administration comprises many layers to handle, and at the same time continues industry best practices without any compromise. In these following recipes, we will highlight a definitive reference for important aspects of cube security processes by mirroring a quick reference on how best SQL Server 2012 BI features can help to accomplish goals.

Perform the following steps:

Now let us work on the database level security settings that will dictate the data source security information.

The relevant Analysis Services instance properties for the security page are used to specify the Windows users and groups included as members of the server administrator role for that instance.

The appropriate options at this level are to add or remove the NT Users and Groups, in order to enable them as server administrators. This means that the specified groups and users will have the highest privilege to perform any administrative action on the instance level. This will help to perform server-level administrative functions using the SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) that includes the database's creation or setting server level properties, and can be used for the programmatically administrative functions using Analysis Management Objects (AMO).

This server role cannot be deleted, permissions cannot be added or removed, and the specified user on this role is not an administrator for the instance of Analysis Services.

However, when it comes to the database level, the database properties dialog box will help us set the properties of a database. The data source impersonation information is the key factor to set for a relevant user to have the required privilege on a database-wide basis. This action specifies the default user credentials used to connect to data sources.

The impersonation options here will be specific to a Windows username (with password) service account, using credentials of the current user used to connect to the SSAS instance or default.

The database will be created as a separate object within the AS database and will only be applicable to the database in which that role is created. The permissions for this role may allow members to access or administer the database, in addition to to default access to objects and data.

By default, each permission has one or more access rights associated within that database, which in turn gives the permission finer control over access to that specified object in the database.

Perform the following steps:

We have completed the recipe steps for the data source now we will work out the cubes access steps. Start the process from step 4 from the previous steps:

The steps carried out in this recipe take effect immediately without having to restart any services on the Analysis Services instance.

The data source name is applied from the database name, though we have an opportunity to change the name, which is not important at this point. On the screen, you will see the options to select the access level for the selected data source in this role.

The two options None and Read are self-explanatory. Using None will assure that users will be unable to access information from the data source. The Read option will enable users to read the information from that data source. The Read Definition option will help us to grant users to those who are in this role permission, for them to read metadata of the data source.

Now coming to the cubes permission, as we have seen the user must have the Full Control access for the specified SSAS instance, for any changes to take place for the role. The Access column will select the access level for the selected cube in this role.

The available options for this Access column levels are as follows:

The Local Cube/Drillthrough Access column will enable the role to select the access level for the local cube or drillthrough functionality with the selected cube in this role.

The three options for this column are:

Finally, the Process column will grant users who are in this role the permission to process the selected cube.

Perform the following steps:

The server property configuration is divided into multiple references. They are divided into multiple sections, and the following table explains these relevant SSAS configuration properties. (Source: Microsoft SQL Server Documentation).

The following topics explain the various Analysis Services configuration properties:

Perform the following steps:

As we have followed the necessary steps to secure the BI platform, this section will explain the key points about why it is essential.

The best option to secure the cube data is to encrypt the filesystem used to store cubes. The SSAS service is executed using the msmdsrv.exe executable file that runs in the operating system. Every instance of SSAS is independent of each other on the same Windows server, having its own configuration settings stored under the msmdsrv.ini file. Any configuration changes for each instance, such as permissions, ports, file storage, server mode properties, and startup accounts, are different and can be configured separately.

The storage and memory architecture has been enhanced and redefined in the 2012 version.

Microsoft documentation states that:

The server mode can be set up during the installation, and in order to run multiple modes on the same server we can install SSAS as multiple instances. As a general rule, most administrative tasks you must perform do not vary by mode. As an Analysis Services system administrator, you can use the same procedures and scripts to manage any Analysis Services instance on your network regardless of how it was installed.

If you have configured Analysis Services to communicate over HTTP, the communication can be secured using the SSL protocol. However, be aware that you may have to acquire a certificate to use SSL encryption over public networks. Also, note that SSL encryption normally uses port 443, and not port 80, to communicate. This difference may require changes in the firewall configuration.

Using the HTTP protocol also allows you to run secured lines to parties outside the corporate network—for example, in an extranet setup.