SSAS Data Security (Dimension and Cell Security)

SSAS provides the way to secure analysis services database/cube data from unauthorized access. Analysis services provides secure access by creating object called "roles". After creation of role, user's windows login credential can be used to enroll into particular role because analysis services identifies user from their windows login credentials . You can protect your data in roles at two levels:

1) Dimension level

2) Cell level

If user has been assigned more than one role, analysis services loop through all assigned roles after login. Analysis services finds all permission level for the particular user and union all the permission levels.

If two roles has contradictory access for user then particular access will be allowed. Suppose role1 says Australia data access and role2 denies Australia data access then access to Australia data will be allowed.

Lets start the implementation details by stating problem statement. Problem Statement: User Amit is reseller department sales manager for Australia region. He should be able to view reseller sales related to Australia region. Note: Role can be created in SSMS as well as in BIDS but in my demo, i will use SSMS.

Dimension and Cell Level Security implementation solution:

1) Open SSMS and connect to "Adventure Works DW" database. Right click on "Roles".

2) It will open new wizard and wizard will have different pages to enter details. In general page, provide name in "Role name" area.(For example, Australia) SSAS provides us three level of database permission.

Full Control
Process database
Read definition

Please select appropriate option.

3) Membership: Add users for the new role.

user can be added by specifying domain and user name. It can be local or network user. 4) Data Sources: By default, access will be none. Data source access will give user access to read/write access to source data for cube. if you want to give only cube data access to user then no need to specify any access. 5) Cube:We are creating this role for giving access of "Adventure works" cube so give read access for only the same cube. Other cube in same database "Mined Customers" should have none in access column.

6) Cell: Cell security can be enabled in this page. Specify measure group you want to see for this role. In our example, Amit user should see only "Reseller Sales Amount" for Australia country. Put following code in "Allow reading of cube content":

([Geography].[Geography].[Country].&[Australia]
AND [Measures].Currentmember IS [Measures].[Reseller Sales Amount])

7) Dimensions: By default, user gets read access for all the dimension in database. You can additionally provide Read/Write access, read definition and process dimension access. 8) Dimension Data: This is most significant page in role creation wizard. There are two drop downs to select particular dimension and dimension attribute. Once you specify both then you can select particular set of members for the access. For our example, select dimension as "Geography" and attribute as "country" and then "Australia" as member.

Once you specify this in basic tab and change the tab to advance tab, you will see MDX on denied member set. Analysis services put all other non selected members in denied set.

{[Geography].[Country].&[Canada], [Geography].[Country].&[France], [Geography].[Country].&[Germany], [Geography].[Country].&[United Kingdom], [Geography].[Country].&[United States]}

9) Mining Structure: Access related to mining structures can be provided here. No need to specify anything for our example. 10) Click OK and new role "Australia" will be created.

So now you need to verify security access. This can be done by different applications like excel, SSMS, Pro clarity etc. Lets take excel and open new worksheet and make analysis services connection with user Amit's login credentials.

Select "Geography" dimension "Geography" hierarchy in row labels from "Adventure Works" cube.
Select "Reseller Sales Amount", "Internet Sales Amount", "Internet Tax Amount".

There are few more ways to provide dimension security which i would like to discuss in my coming posts.

Comments (4)

Anil said:

Humm,it's nice but why we cannot hide the dimension itself.Is there any solution for hiding the dimension itself in the upcoming SSAS version features by Microsoft ?.

Thanks,

Anil Maharjan

1/11/2011

Comment

sirfnet said:

Hi Anil,

We dont want to restrict complete dimension to user. We just want to give access/restrict of few members of the same dimension. For example, Australia country member for above article.

Hello amit ,

Well in this post i know that you are just hiding the member of particular dimension but in my case i need to hide the dimension itself for two different user with in a same cube.(i.e say for A user only 10 dimension access and for B user 15 dimension access ) i came to know we can hide the member's only not the dimension itself .

so anyone have idea about this topic .?..

Anil Mahajan.

1/13/2011

As far as my knowledge, we can not hide complete dimension from cube. At dimension level, we have only two access permission: read and read/write. I will try to find out workaround. Will update you if i get something.

Add a new comment

Name:*
Email:*
Website:

Type the characters you see in the image: *

SSAS Data Security (Dimension and Cell Security)

SSAS Data Security (Dimension and Cell Security)

Comments (4)

Most Recent Articles