Row level security on relational datasource in Cognos Framework Manager

If you are looking into setting up security for a relational database Framework Manager model, you are after the CSVIdentityNameList function; it is the most automated way to get things working without much future maintenance.

CSVIdentityNameList macro function returns all user related credentials in one comma sepparated list. It basically is a one long string consisting of all user classes the user is assigned to; this could be a Cognos user group, it could be a group from Series 7 user class, a Active Directory group or anything else related to the account- all is dumped into one big string.

So how do you make use of this? Well, lets say you want the user to see only one country, so you create a user class Country_GB and assign the user to it.

In framework manager you will need to apply a filter on the table that contains country codes. This might be a sepparate table or even better if you have it right on your facts. Does not matter as long as any item from the table is used in the report.

Modify and put this (the parts in [] should represent a real item in your FM model) into your filter:
'Country_'+[model].[table_facts].[Country] in (#CSVIdentityNameList()#)

What this does is creates a concatenated string of 'Country_' and the country code of the row that is being read. Then it is compared to the user account details in CSVIdentifyNameList and either filtered out or not.

And here you go- completely automatic filtering of data! Now all you need to do is add your users to appropriate user groups and never touch the FM model again!

Have fun ;)
M

UPDATE: check out a new post on optimizing this security method to make it smoother, cleaner and faster!