How to implement row level security in framework manager (all options)

Check list of all posts

In order to get data retrieved based on security, two sets of information are needed to be identified:

• What dimensions are requested to be used to control data retrieval?

• Which user, user groups or roles are used to login?

• What relationship should be built between security related dimensions and user related information?

Regarding security related dimensions:  In order to make sure that security will take in effect, we need to connect this security dimension with fact tables. There are a few situations as follows:

1. Add security filter to security dimension, then add security dimension dummy column into data section, where dimension doesn’t display on report.
2. Add security filter on fact table indirectly via Security dimension. This is not very preferred solution, but it solves security issue, regardless of where data model is used in report, query and analysis studio when use fact table.

3. Add security filter both dimension and fact table.  Solve all security issues when using report, query and analysis studio for dimension and fact tables.

Regarding user related information:  There are a few situations as follows:

1. Single user information with Session parameters, please note that many account related information can be retrieved based on the authentication provider.   

$account.defaultName, $account.Personinfo.email

2. Advanced user information with CSVIdentityNameList , The macro function optionally accepts a list separator as a parameter and then returns a separator-delimited list

CSVIdentityNameList

3. Derived user information with CSVIdentityName, This macro function retrieves account, group, and role information for the current user. It returns a string of comma-separated values from the parameter map in single quotation marks

Regarding relationship between security related dimensions and user related information:  defined in framework manager, either use define data security or add security filer


Eight different options are listed below

Specify data security in framework manager

This is the simplest form of security and is achieved by specifying data security for security related dimensions


If a user has multiple roles, the security filters belonging to these roles are joined together with ORs. If a role is based on another role, the security filters are joined together with ANDs.

Usually, rather than setting up this security many times, try to set it up on a common or conformed dimension query subject.

It is good approach for small size company, as active directory can be customized to satisfy Cognos security. In case when there is any new requirement, active directory can be enhanced to accommodate it.

 

Dynamically Setup data security in framework manager with parameter map

The major advantage of the approach above is to specify filters in framework manager whenever there are all users or groups to be added. It is too maintenance intensive and always involves framework changes. The idea is to replace it with a parameter map that returns the security expression based on the currently logged in user. The security map between individual user and security expression can be saved in database table, and then any changes can be done in database without touching framework manager. 

The detail implementation steps are listed below 

• Create a table to build relationship between user and security expression 

• Expose this query for parameter map based on query from this table 

• Add security expression to security dimension in framework manager: #$SecurtyMap{$account.personalInfo.userName}#

 

Use simple parameter map between user and security column

For the sake of easy explanation, use the product as security dimension, and product line as security column. The idea is to build which user can see what product line(s)

1. Build a relationship between users and product line(s) as parameter map as SecurityUserMap

2. Add filter to product dimension as below

[gosales].[Products].[Product line] in (#$SecurityUserMap{$account.defaultName}# )

It is good approach for small amount of users who access Cognos.

 

Use advanced parameter map between user and security related information

It is the similar idea as above. However, it will resolve the issue of user amount in some degree, as it is about building user role and groups. Based on user guide,  If you want row-level security based on UserClass values stored in your data source, implement a parameter map that maps the values in the data source to the corresponding roles and groups based on the user you are logged on as.   Parameters map Roles_Groups is setup between Key (Role or Group) and Value (User Classes)

Security filters is added to security dimension as
Security_column in (#CSVIdentityName(%Roles_Groups)#)

However, this solution present some challenges when there are too many groups and the relationship between user and groups are complicated.

 

Use database table to control user access permission based on user group

This approach is much more flexible as all relationships are controlled by user and a large number of user classes are used, without asking active directory to change. This security set up is similar to last few options but is more dynamic because of following reasons:

1. User access table can be very big, and
2. #CSVIdentityNameList()#   provide more flexibility, with or conditions, such as

('Administrators' in (#CSVIdentityNameList()# ))

OR

('authors’) in (#CSVIdentityNameList()# ))

OR

(Security_column in (#CSVIdentityNameList()# ))

3. In addition, you can use Marco to do some logic as well, such as code snippet below

upper([Database View].[T_BOOK_USER_ACCESS].[ACF2_ID])  

=

#

sq(

substitute ('@ACF2_ID_ALL','ALL',

substitute ('M3BI_FO@ACF2_ID_ALL',$account.personalInfo.userName,

substitute ('''','',

substitute ('''','',

substitute ('''''','',

substitute ('''''','',

csv ( grep('M3BI_FO' , array ( split ( ',',CSVIdentityNameList() ))) )

) ) ) )

+'@ACF2_ID_ALL'

)))

the sample above is a relative complicated macro script, which could be very useful for the future reference, as many functions are used here, such as grep, split, array, CSVIdentityNameList(),substitute, csv and $account.personalInfo.userName

The idea is to let Cognos macro in framework manager to execute the logic before sending to database. Then the dynamic generated Cognos SQL statement in database becomes much clearer with better performance.  The big challenge is that the power of Cogos macro functions is relatively limited.

 

Embedded data security with fact table at database level

This option is good for following situations:
1) No any security should be applied for dimensions, meaning that all users are allowed to see al dimensions.
2) The security logic is complicated, saved into one or more tables.

The solution is to build logic into fact table, and therefore data will be automatically filtered out.

The detail implementation steps are listed below
• make fact table associated with security tables, usually using correlated query
Select * from (
 Select * from Fact_Table
) F
where 'Yes'
= ( Expression as READ_ACCESS
 from
  Security_tableA,
  Security_tableB,
 where
  F.SomeId = Security_tableA.SomeId
  and Security_tableA.SomeId= Security_tableB.SomeId
  and Security_tableB.UserId = #$[ParametersMapX]{$account.personalInfo.userName}#
 )

• Expose this query for parameter map based on query from a table where the realtionship between user name and userId

 

Use database connection in Cognos

In case when database row level security exists, Cognos can pass through sign on to access database.
There are three level of data source connection:

1. Level 1: Content store data source
2. Level 2: database connection
3. Level 3: Sign on

What to do is to go to third level, Sign on level, where each user will have a sign on defined.

Use multi-values LDAP attributes

This idea is described in detail from link Leveraging multi-valued LDAP attributes as session Parameters
Attributes of a users account are read from the authentication source and mapped to session parameters. For LDAP namespaces it is possible to specify additional “user defined” attributes which can be exposed as session parameters

#csv(@account.parameters.department)#

Leverage ORACLE Data Security

This idea is described in detail from link Leveraging ORACLE Data Security features with IBM Cognos 8 BI

Multiple user groups based on sales alignment

Sales alignment is to align different user groups to sales. It is very often that sales are assigned to a sale rep, product specialist and territory manager. Each user group will need to see their own data. There are two different ways to design security:

1. Design each groups as separated dimensions, such as sale rep dimension, product specialist dimension and territory manager dimension, and then put data security on each dimension.
2. Design a sales alignment, where we assign sale rep, product specialist and territory manager to a sale.

Due to nature of business, sales alignment is used in lot of times. This document is intended to describe how to handle Cognos data security multiple user groups based on sales alignment.

Data mart scheme behind the scene

Three tables are involved in data security. There is Sales alignment key created / updated in sales when loading data into DWH. It can be interpreted into diagram below

For given a user, sales can be indentified based on sales alignment and users table. The main challenge is to get right column to join according to user group.

Implementation

Create User query subject

Create user query subject but with filter below

#sq($account.personalInfo.userName)# = [Data Layer].[User].[User ID]

Based on user login user 1, you will get user group 1, only single record will be retrieved.

Create a security mapping (or Parameters map) based on User query subject

Key: User Group

Value: User ID

By giving User group, User ID will be retrieved.

Create active directory group based on user group

All users will be added to each active directory group.

Apply data security for sales alignment

Only one filter will be valid.  The solution is to get right column to join according to user group.

Using Cognos framework manager parameter maps for data-level security for unbalanced ragged hierarchy