Cognos - Column level security

[Rajaraman Kalpati]

Hi Friends,

I need your help to understand how to set column level security.

Let me share you the scenario

Ex. Suppose there are two groups CSM and Non_CSM. If the user from CSM group logs in he should be able to see projects from Column A in database. If the user logs in from Non_CSM group he should be able to project from Column B in database.

And if the user is admin he should be able to see both.



Thanks
Rajaraman

[RichardP]

Hi Friends,

I need your help to understand how to set column level security.

Let me share you the scenario

Ex. Suppose there are two groups CSM and Non_CSM. If the user from CSM group logs in he should be able to see projects from Column A in database. If the user logs in from Non_CSM group he should be able to project from Column B in database.

And if the user is admin he should be able to see both.



Thanks
Rajaraman

This Post has some similar info that will be of help..
http://www.cognoise.com/index.php?topic=7501.0

which discusses two approaches
1. Creating a new expression and using #CSVIdentityNameList()#) to see if a User is a member of the security group
that can see the data value or if they are not - they see a masked value ie.  "******"

As for the example
if ('Security Group' in #CSVIdentityNameList()#)
then ([Column Data Item])
else ('****')

or

2. Using Object Level security to HIDE the column outright from each security group.

Some PROS and CONS of each
Option 1 -
PRO  - any reports that include the column which a user does NOT have rights to see - will NOT get an error
they will just get the masked value...  so you can safely develop reports knowing they can be run by all users.
CON
the #CSVIdentityNameList()#) function returns the complete list of Cognos roles a user is a member of
so your SQL is quite large...

Option 2
Since this will DENY access to the column - if a report includes a column the user does not have access to
they will get an error.

In our environment we use LDAP and make use of a an LDAP property to implement security similar to
option 1.  The benefit is that we are only comparing against one value instead of a string containing
ALL the Cognos Roles a user is a member of with the #CSVIdentityNameList()#) function
However, this also means an extra maint task for your LDAP Administrator 

Example

NOTE: 
For this example assume:
LDAP CSMAccess Property value of '01' means users can access Column A
LDAP CSMAccess Property value of '02' means users can access Column B
LDAP CSMAccess Property value of '03' means users can access both columns

Name :  COLUMN_A_PROJECT
CASE
    WHEN #sq($account.parameters.CSMAccess)# IN ('01', '03')  THEN [COLUMN_A_PROJECT]   
    ELSE '****"
END

Name :  COLUMN_B_PROJECT
CASE
    WHEN #sq($account.parameters.CSMAccess)# IN ('02', '03')  THEN [COLUMN_B_PROJECT]   
    ELSE '****"
END

Hope this helps