The source is the key for any data, irrespective of cube or database. In this book the remaining areas within cube security are data sources and cube access. In this recipe we will go through the key aspects on how access control can affect the data sources and dimension data that are included in the cube.
Within the Analysis Services, data marts have multiple data sources and these types are not treated in the same way. When the cube is processed for query purposes, the key content of a right data type will help the results to return faster.
Cube processing also involves dimensions that will accept queries to the relational database in order to retrieve key information based on the queries. Keep in mind that not all the queries are simple SELECT queries or get a single dataset. There are multiple nested-complex queries. So, the data sources are always an important layer in the cube to set up the right level of data flow.
Here the permission is a key ingredient to access the data source, and it depends on the type of data that is being used and the security setup. As we have reiterated in the previous recipes, Analysis Services only uses Windows authentication to connect; it is one of the best ways to secure the data when OLAP is involved.
The account permissions for the SSAS service account will have the necessary access control on tables and views (virtual tables) with sufficient levels of read permissions. As we have worked out from the previous recipes, impersonal information is important to set up the right level of data access.
In this recipe we will look at the important steps used in order to secure a data source and right level of access on cubes.
Note
The prerequisite for this recipe can be obtained from the Getting ready section of the Designing and creating security roles in an OLAP database recipe.
Perform the following steps:
Start SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services (SSAS) instance.
Expand the
Databasesfolder.Choose the AdventureWorksDM database (created in the Getting ready section previously mentioned) and expand the
Rolesfolder.Right-click on the role (here I have selected the DBIA_Processor role, and later the DBIA_User role) to choose Role Properties.
Select Data Sources from the Select a page pane that will show the Adventure Works DW2012 information.
Click on the drop-down option on the Access column and select Read to enable the read access to the data source. Click on OK to bring about the new changes:
Note
For the DBIA_User role I would like to leave the default setting for Access as None under the Data Sources option.
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:
Right-click on the role (here I have selected the DBIA_Processor role, and later the DBIA_User role) to choose Role Properties.
Select Cubes from the Select a page pane that will show the cube-related permissions for this role on the Adventure Works DW2012 cube.
Select the Read/Write option from the Access column's drop-down list and the Drillthrough and Local Cube option under the Local Cube/Drillthrough Access column:
Click on OK to bring about the changes on the Cubes option, on the Role Properties page.
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:
None: This will disallow any users from accessing the information from the cube
Read: This will allow users to read the information
Read/Write: This will allow users to read and write information to the cube
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:
None: This will disallow any access to the information from the cube
Drillthrough: With this you will be able to use drillthrough functionality, but unable to create local cubes from the cube
Drillthrough and Local Cube: We can use this to apply the complete drillthrough functionality and create local cubes from the cube
Finally, the Process column will grant users who are in this role the permission to process the selected cube.