Instant SQL Server Analysis Services 2012 Cube Security [Instant] — Save 50%
Analyze and secure cubes in the SQL Server 2012 development environment in no time using this hands-on guide with this book and ebook
In this article by Satya SK Jayanty, author of the book, Instant SQL Server Analysis Services 2012 Cube Security, explains that a bit of background on leaf members and non-leaf members is necessary to know how the data is managed at cell level.
The cell value in a cube can be obtained in multiple ways; it can be directly retrieved from the fact table of the cube. The identification of a cell value and its members is leaf members that have no child members or hierarchy that reference a single record in a dimension table.
Further on this cell can be identified by using non-leaf members, members that have one or more child members. The cell value is derived (typically) from the aggregation of child members.
(For more resources related to this topic, see here.)
The following prerequisite is essential for our recipe to continue the recipe:
- SQL Server 2012 Management Studio (SSMS).
- The AdventureWorks2012 database. We can obtain the necessary database files and database product samples from SQL Server Database Product Samples landing page (http://msftdbprodsamples.codeplex.com/releases/view/55330).
These sample databases cannot be installed on any version of SQL Server other than SQL Server 2012 RTM or higher.
- Ensure you install the databases to your specified 2012 version instance.
- For this article I have created a new OLAP database using the AdventureWorksDM.xmlafile.
Also, ensure that the user who is granting permissions is a member of Analysis Services server role or member of Analysis Services database role that has Administrator permissions.
How to do it...
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:
- Start the SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services instance.
- Expand the Databases folder.
- Choose the AdventureWorksDM database (created within the Getting ready section as previously mentioned) and expand the Roles folder.
If you are reading this recipe directly without the previous recipes, you can create the necessary roles as per the Creating security roles(Intermediate) recipe.
- Right-click on the role (here I have selected the DBIA_Processor role) to choose Role Properties.
- Click on Cell Data on the Select a page option to present a relevant permissions list.
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 appropriate Access and Local Cube/Drillthrough options by choosing the Cubes option on the left-hand side on 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 present 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 permissions option and then click on the Edit MDX button. You will be presented with the MDX Builder screen. Then, choose the presented Metadata measure value to grant this permission.
- Similarly, for the Enable read-contingent permissions option, follow the previous step.
- Finally, click on the Enable read/write permissions option.
- As a final check, either we can click on the Check button or the OK button, which will check whether valid syntax is parsed from the MDX expressions previously mentioned.
- If there are any syntax errors, you can fix them by choosing the relevant Edit MDX button to correct.
This completes the steps to secure the data at the cell level using a defined role in the Analysis Services database.
How it works...
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.
Many databases insufficiently implement security through row- and column-level restrictions. Column-level security is only sufficient when the data schema is static, well known, and aligned with security concerns. Row-level security breaks down when a single record conveys multiple levels of information. The ability to control access at the cell level based on security labels, intrinsically within the relational engine, is an unprecedented capability. It has the potential to markedly improve the management of sensitive information in many sectors, and to enhance the ability to leverage data quickly and flexibly for operational needs. This article showed us just how to secure the data at the cell level.
Resources for Article:
- Getting Started with Microsoft SQL Server 2008 R2 [Article]
- Microsoft SQL Server 2008 High Availability: Installing Database Mirroring [Article]
- SQL Server and PowerShell Basic Tasks [Article]
About the Author :
Satya (SQLMaster) SK Jayanty is a subject matter expert (Technical &Data Architectand DBA)having more than 21 years of experience in the IT field, which includes a wide range of industries: the Stock Exchange, insurance, telecommunications, financial,retail,and manufacturing sectors, among others. He has been the Microsoft Most Valuable Professional(MVP) for Architecture – SQL Server since the year 2006.
He has also authored the book Microsoft SQL Server 2008 R2 Administration Cookbook (May 2011),Packt Publishing. He has co-authored the book MVP Deep Dives Volume II – SQL Server (October 2011),Manning Publications.
Satya has technically reviewed and provided forewords for three books related to High Availability and Disaster Recovery. And has also been the Item Writer and Technical Reviewer for SQL Server 2008, 2008 R2 and 2012 Microsoft ITPRO and DEV certification exams.
Satya is a regular speaker and SME volunteer at major technology conferences such as Microsoft Tech-Ed (Europe, India, and North America), SQL PASS (Europe and North America),and SQL Bits – the UK and Scottish Area SQL Server user group based in Scotland.
He works as a Director, and Principal Architect, at D B I A Solutions Limited, Database Business Intelligence Architecture Solutions Europe Limited.