Data Level Security in Cubes Using ParameterMaps

Data Level Security in Cubes Using Parameter Maps

In this article I will look at a real-world example of data level security filtering.

Cognos does provide some role based security filtering capability.
I think the problem with roles is that there is usually an attribute associated with the role... and that on the other hand is not supported out-of-the-box.

Consider the following example, to undertsand what I mean by "attribute associated with the role"

Suppose we have the usual GO-Sales kind of data. We have a Sales measure and one of our dimensions is Branch.
We have two roles: CEO and Branch_Manager
Our requirement is that CEO can see everything but a Branch_Manager can only see Sales by his or her own branch.

This is the point where role-based security goes week.

Somehow we need to store which Branch a Branch_Manager is allowed to see.
It's not sufficient to know that a user has Branch_Manager role... we need to know (and handle) a BranchID associated with that role.
This is what I call the "attribute associated with the role" - in our example the BranchID.

Usually this implies we need to store this somewhere, eg in the database or maybe in a directory server like LDAP.

To implement data level security I find the best option is to use Slicers all over the place where cubes are referenced - in queries used by crosstabs and charts on the reports, in queries used to populate prompts on the filter pages.
(Keep in mind, I firmly believe that lists should not be used with a dimensional datasource.)
The slicer will make sure that filtering is applied to the entire cube, regardless of the dimensions used for display.

To continue with the example we need to write a slicer that

• figure out who the current user is
• what role the current user has - is the user a Branch_Manager
• what attribute is associated with that role - value of the BranchID

So what tools are in or arsenal to tackle this?

Session Parameters

Session parameters provide a way to get an identifier of the current user.

e.g.

account.userInfo

Tamas Simon

Session parameters can be used in macro expressions. The syntax is to prefix them with a ``$'' (dollar sign)

e.g.
#sq($account.userInfo)#
will result in
'Tamas Simon'

The exact details depend on how Cognos is configured...
Bottom line is that in every Cognos installation there should be some sort of identifier of the current user and it's accessible to report expressions using session parameters.

You can check the session parameters in Framework Manager by selecting Project / Session Parameters... from the menu.

Parameter Maps

Parameter maps are similar to session parameters in that they can be accessed from macro expressions.
They are name-value pairs, or rather "key-value" pairs - as Cognos calls it.
They are defined in the Framework Model.
They can be based on a query, just select a column for Key and another for Value.

Syntax is similar to session parameters.
``$'' (dollar sign) infornt of the name
``{}'' (curly braces) surrounding the key
``''' (single quote) surrounding the key if it is a string literal

e.g.
#$pmUserAccessableBranches{'Tamas Simon'}#

Here are some important notes I'd like to make:

1., There is no way to include or exclude parameter maps from a package. In my understanding when you re-publish a pckage all parameter maps are published with it.

2., The query subject that the parameter map is based on has to be published in the package. Otherwise you won't get any error or warning messages but the parameter map will be empty, always returning its default value if you have defined any.
I remember reading that the query is executed everytime an expression references the parameter map - but I have not confirmed this.

string aggregate function

3., Keys are unique. This is tricky... to continue our example imagine that we want to be able to assign multiple branches to the same user.
In other words we want to control which branches a user is allowed to access (maybe only one, maybe more)
So in the parameter map we need to have a list of IDs associated with a Key, all within a single string (stored as the Value)

e.g.

database table

UserName	BranchID
Tamas Simon	B1
Tamas Simon	B2
Tamas Simon	B3

we would want the parameter map to look like

pmUserAccessableBranches

Key	Value
Tamas Simon	B1 B2 B3

This is tricky considering that we need to write a query to populate the parameter map... but it's doable.
Oracle for example does not provide a string aggregate function out-of-the-box but one can write his own.

select  UserName as Key,  stragg(BranchID) as Value from  UserAccessableBranches group by  UserName
You can find a string aggregate function e.g. here: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php#user_defined_aggregate_function

With these three tools: session parameters, parameter maps based on DB query and string aggregation we can solve our data level filtering challenge.

We end up with slicers something like this:
filter(  [Branches Dimension].[Branch],  roleValue('_businessKey', [Branches Dimension].[Branch])  in (   #csv(split(' ', $pmUserAccessableBranches{$account.userInfo}))#  ) )
from the inside out:
get the current user via session parameter: $account.userInfo
use this as a Key to look up the user accessable branches from the parameter map: $pmUserAccessableBranches{$account.userInfo}
massage the macro until it returns a comma separated string: #csv(split(' ', $pmUserAccessableBranches{$account.userInfo}))#
use the whole thing to filter branches... resulting in a slicer member set.

We could just generate a set() expression using the prompt...

set( #join(',', substitute('^', '[Branches Dimension].[Branch]->:[PC].[@MEMBER].[', substitute('$', ']', split(' ', $pmUserAccessableBranches{$account.userInfo}))))# )

This would work if the IDs are in synch with the cube. Otherwise we might get an error from Cognos, saying basically that we are refering to a member that does not exist. I find it safer to use the filtering expression.