The key part of keeping up the cube security is referred to in the previous recipe, Managing role-based security – dimension to cell level (Advanced).
However, the important aspects on how to restrict access on certain dimensions within the cube are essential as well. In this recipe let us look at the access restrictions and define the default members with necessary permissions. The default members in a database role can also be restricted or be granted access to the dimensions.
The key part of accessing the cube is managed from the dimension to cell level. As a cube is represented as multidimensional structure that contains data used for analytical purposes, the dimensions will always provide a necessary structure for the data presentation, and for the databases roles we need, to secure the necessary grants and permissions.
As the recipe heading specifies, defining default members (full and partial) means granting necessary access to the all dimensions (full) or few dimensions (partial). Since SQL Server 2005, the database roles have the necessary permissions to read or write to the dimensions in a cube. However, with this recipe we will dive into defining access restrictions to a specific set of attribute members for each dimension elements to which role members have necessary permission access (either granted or denied attributes).
Note
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.
The recipe steps are bifurcated as basic and advanced; this will enable us to grant database role access to a member in the dimension:
Start the SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services instance.
Expand the
Databasesfolder.Choose the AdventureWorksDM database (created within the Getting ready section previously mentioned) and expand the
Rolesfolder.Right-click on the role (here I have selected the DBIA_Reader role) to choose Role Properties.
Click on Dimension Custom Data Access within the Role Properties page, choose the selective dimension in the dimension list (as shown in the following screenshot):
The preceding step will enable the key ingredient for our recipe: setting the IsAllowed property to True.
To cross-check, you can select the Advanced tab that presents the list of dimensions in the Allowed member set window (see the following screenshot):
Here is the trick, to grant access for all the members you can select the Select all members radio button.
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:
Let us continue on the same SQL Server Management Studio and the selected Analysis Services instance.
Choose the AdventureWorksDM database (created within the Getting ready section previously mentioned) and expand the
Rolesfolder.Right-click on the role (here I have selected the DBIA_Processor role) to choose Role Properties.
Click on Dimension Custom Data Access within the Role Properties page, choose the Select all members radio button, which will allow access to all members currently in the dimension (as shown in the following screenshot).
The reason behind selective members, shown in the screenshot, is to show how to deny a certain member set access to this particular database role (DBIA_Processor).
Once the defined steps are followed, click on OK to complete the process.
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.
As we have seen with access permissions on the dimension level, more to do with cell data is covered in the Securing data at the cell level (Intermediate) recipe.
Managing security surrounding an Analysis Services instance is quite possible by granting relevant permissions to the user roles and database roles. The security group can handle the users, members, and hierarchies in order to access the data from the cube.
Security management on the instance level and cube layers was covered in the previous recipe in the book; now we will dive into cell data specifics to manage the overall security efficiently.
In the series of recipes in this chapter we will highlight the importance of tools and tricks of the trade that will help to secure the data warehouse platform by providing necessary security to cube data. Not only the tools and tricks, we will also cook up recipes that drill down to the layers where they are essential when it comes to delivering diversified data for end users.
These recipes will also demonstrate the essential tools that can help to accomplish security management:
How we can secure data at the cell level
How we can prevent a process from reading/accessing certain cell data
Managing user access privileges on cubes