Suggestions on BI-TM1 integrated Security Model using SQL Database

[Ganesh Kedari]

Hello Experts ,
I am looking for suggestions to implement Security Model for an Implementation ,

Current Scenario:
Customer has Enterprise Active directory which is used for user Authentication based on Intranet user ids. For authorization, customer has SQL Database Tables which handles User Groups and Access Capabilities for existing application. This Database is mapped with Active directory using Employee Id.
This capability is required so that frequent changes in User access can be managed by Local IT team. IT team has an internal application which handles changes in user access roles.

IBM BI-TM1 Solution:
Customer wants same capabilities to be replicated to IBM Cognos BI-TM1 solution.
There will be no access restriction on BI Reports as all reports will be available to every authorized user in database. There are additional configuration required for enable and restrict certain capabilities like access to Cognos Workspace Advance, Report Save access and Exporting to Excel need to be set for set of users.
Data level security need to be handled at TM1 level where for each report, Data applicable to user role will be served in Report. As TM1 relies on BI to enable user groups these groups need to be available in BI Administration console.

Proposed Solutions:
1.   Active Directory Groups:
User Authentication and Access will be managed by User Groups defined in Active directory. IBM Cognos BI will read these groups and enable required capabilities as per requirements.
TM1 will import user roles identified by BI and set data security for all users in group.

Customer is not agree with this solution as there are extra overhead of managing these application specific user roles in Enterprise Active directory. Also SLA for Active directory user changes set by Enterprise IT team are not acceptable due to Process execution timelines.

2.   Cognos BI Administrator Groups :
User authentication will be handled by Active Directory and Access control is managed by IBM Cognos Administrator using Cognos Groups. These groups will be used for Data Security in TM1.

Customer is not agree with this approach as they will have extra overhead of managing 2 different security processed for change. They are looking for solution which can automatically sync between existing application and BI-TM1 solution for user access roles.
Also this will have overload of developing these groups in Cognos Administration and manage individual users transitions from one group to another.

As both solutions are ruled out by customer we need to have alternate solution to enable these requirements. Here are few solution approaches which can be used to fulfill these conditions