Blog Pro de Jean-Baptiste HEREN

Notes d'un consultant Freelance en Informatique

To content | To menu | To search

Row Level security in MSAS

Security implementation in Analysis Services

In some cases, companies want to give different level of access to strategic data, depending on user’s profiles. Analysis services gives us ability to secure cubes data by building Roles containing security rules, mainly by filtering dimensions data.
For example, if you have a cube giving Turnover amounts per salesperson, salesmen should see only the data corresponding to their sales area only. Also, one manager may want to see data for all areas he’s responsible for.
This document describes how we can achieve security on the sales Area axis for the sales domain. Notice taht in this example, we are using Cognos as reporting tool. This addidional layer has no impact on the way we setup MSAS & Active directory, results will be the same with Microsoft Reporting services

Use Case

As an abstract, here is a typical use case, after setup of row level security in MSAS and Active directory sign on in Cognos.

Security Definition architecture

To apply row level security, we will have to define User profiles & associate them with Security Roles in Sql Analysis services, using Active directory for Binding.

To apply Security on Dimensions in MSAS Cubes, we want to rely directly on active directory. This way we will be able to add new users without changing the security definitions in Analysis services:
-         We create one active directory group for each MSAS Security role
-         Each MSAS Role has only one AD Group as member.
-         New users just have to get the right AD Groups to get the right access to MSAS Cubes & dimensions.

[image:Static model of Security Elements]
Figure 1 - Static model of Security Elements

We will work on dimensions data security with order defined as Deny, Allow. In other words, if a dimensions is secured : default is deny and we specify allowed elements. This way, we will be able to combine multiple roles givig access to different elements.

One role in Analysis services can manage access to any element:
-         connexion
-         cubes
-         cell data
-         dimensions
-         dimension data (the one we are working on)
Here we want to manage security on dimensions attributes, so that users can only access to data filtered using allowed dimension’s attributes. Notice that we can apply security on each level of one dimension.

We have one “Sales Area” Dimension with three levels, on witch we want to create security roles.

Figure 2 - Static model of Sales Area Dimension

One Global user Role

We will change the way Cognos connects to MSAS. Instead of always connecting using Cognos Service credentials, we want Cognos to connect using user’s credentials.

Then we have to create a generic user role in each SSAS Database, so that all users can access to cubes, even if they don’t own a secured group. This role must at least set database & cubes read access. All Cognos users will have to be member of this role, this can be done using a Cognousers group defined in Active directory.

All Cognos Users groups will need a basic read access, at least.

[image:user.role Cubes]

Specific user Roles (with security on data)

Those roles will limit access to specified Dimensions Elements.

For Example:
-         Sales_Asia.role will grant access to the Sales area attribute Member ASIA.

-         France.role will grant access to the Country attribute Member FRANCE.

If we want the cube to recalculate aggregations and totals regarding the applied security, we will have to select the “Enable visual Total” option on advanced Tab.
[image:sales_france.role advanced tab]

MSAS Roles validation

The “Grant based” Security allows a user to cumulate access from multiple roles.

For Example, one user with ASIA & FRANCE groups will get both access. We can validate this using the cube browser’s testing tools.

In the Sql Server Management studio, we can browse cubes using specified profile or roles :

[image:security Context Roles]

In SQL server management studio cube browser, we Specify the security context;
[image:security Context]

Then we navigate Dimensions and data;
[image:Navigate Cube]

We can see that both Asia and France Roles associated members are allowed.

Jean-Baptiste Heren

Author: Jean-Baptiste Heren

Stay in touch with the latest news and subscribe to the RSS Feed about this category

Comments are closed

no attachment

You Might Also Like


Cognos Report : Display a different measure in row for the aggregated column

Following tip is when you use a Relational Datamodel (DMR). In a Cognos Report crosstab, If You need to Display different measure in the aggregated column, you can do it using a simple if-then-else...

Continue reading

Cognos 8 - convert a member unique name from one hierarchy to another

If you need to build any report or Event studio Query using the same data from different packages; and you want to prompt just once. You can convert the member unique name from one hierarchy to...

Continue reading