Row Level security in MSAS
Security implementation in Analysis ServicesIn 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
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:
- cell data
- 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
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)
- 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
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
We can see that both Asia and France Roles associated members are allowed.
Article modifié le vendredi 13 novembre 2009, 12:06