By default, if the database role has granted permissions on a cube, the role members have access to view all cell data. In order to limit the cell data access within the cube or dimension, we must explicitly restrict cell access.
For the specific dimension members, it is possible to limit the access; to grant relevant access we can use MDX to define a range of cells with the required permissions such as read, read-contingent, or read/write.
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.
Perform the following steps:
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_Processor role) to choose Role Properties.
Click on Cell Data in the Select a page option to be presented with a relevant permissions list.
Note
In some cases, if you have observed that there is no option available in the Cube drop-down list in the Cell Data option, ensure you check that the relevant cube is set with the appropriate Access and Local Cube/Drillthrough options by choosing the Cubes option on the left-hand side under Select a page.
Refer to the following screenshot:
Now let us continue with the Cell Data options:
Click on Cell Data in the Select a page option to be presented with a relevant permissions list.
Select the appropriate cube from the drop-down list; here I have selected the Adventure Works DW2012 cube.
Choose the Enable read-contingent permissions option and then click on the Edit MDX button. You will be presented with the MDX Builder screen. Then, choose the presented Metadata to grant this permission.
Similarly, for the Enable read-contingent permissions and the Enable read/write permissions options, perform the previous step:
Note
As shown in the following screenshot, choose the [Dim Product] measure for read-contingent permissions and the [Dim Customer] measure for the read/write permissions option.
As a final check, either we can click on the Check button or the OK button, which will check whether the valid syntax is parsed from the previous MDX expressions.
If there are any syntax errors, you can fix them by choosing the relevant Edit MDX button to correct.
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 read-contingent permissions are not derived from other cells
The cells with read-contingent permissions are derived from the other cells; however, the database role has read permission on all the cells from which the cell was derived
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.