Monday, June 14, 2010

Using Parameter Maps to implement Data Masking

Here's a real world example of using Parameter Maps to implement data masking as well as disadvantages of the below solution.

Requirement: Data is stored in 2 different schemas with the same table names. One schema contains Secured Data and the other Unsecured or masked data.

Users have access to either Secured Data or Unsecured Data. This information is stored in a security table. Users need to be dynamically pointed to either of the schemas depending on their security level clearance.


Step 1:The security table contains the following columns - UsrID, HasSecurityClearanceFlag.

Step 2:In FM create a data source query Subject called SecurityLevel based on the above table.

Step 3:Create a Parameter map called Usr_Access based on the above Query Subject with key set to UsrID and value set to HasSecurityClearanceFlag.

Step 4:Create a model Query Subject called SchemaName with columns - UsrID, HasSecurityClearanceFlag, Schema.

Schema -
case #$Usr_Access{$account.defaultName}# when 1 then 'Schema1'
when 2 then 'Schema2'
else 'DefaultSchema' end

Step 5:Create another Parameter Map called Schema based on the above Model Query Subject with key set to UsrID and value set to Schema.

Step 6:In Query Subjects that need to be dynamically re-pointed to the schemas modify the SQL as below:

Select * from #$Schema{$account.defaultName}#.TableName


Modifying the Data Source Query Subjects results in Metadata Callbacks and this is a cause of concern.

I am still trying to find a viable solution wherein there are no callbacks and yet we can achieve the above security requirement.

Any suggestions?


DK said...

I am working on Cognos Report studio and I have got a requirement in which we are having two database schemas for Development and Test Environment. but the underlying tables are same in both the schemas. Now the requirement is to have the Schema name as a variable in the reports...
please help me with this..
P.S: u can post the reply to my mail..

Zephyr said...

Deepthi, What's your DB? You could create multiple data source connections under a single Data Source object and be prompted at run time for which connection to use.

You can look this up on support site. But let me know if it helps you if I post an article on this.

Philip said...

Hi there, I know this is an old thread but thought I'd try anyway. I need to implement this solution in Cognos 10.1 and it's giving me some grief.

I have a parameter map which is returning the correct values. When I adjust the SQL as per your blog and test the macro it returns the correct value but when I try to test the query subject itself it gives me the following error:

This query contains an error and can not be executed.
It is recommended that you view the query feedback on the "Query Information" tab.
QE-DEF-0406 Unknown function 'CSVIdentityName' in macro expression.

No other information is available and I can't locate anything helpful on the web.

Please help!!!

Anonymous said...

I have a question on the parameter mapping.I currently have a prompt page with date and id.I want my report to be data driven.I chose to have date and id from a table in framework manager.Unfortunately they are standalone table.I want to replace the prompt page with parameter mapping.Any advice?

Anonymous said...

Hope this will help

Radha Sai said...

Nice post. Keep updating Cognos TM1 online training Hyderabad