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.


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.

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.

 

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 :

In SQL server management studio cube browser, we Specify the security context;

Then we navigate Dimensions and data;

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