How to setup column level security

[Tsunami]

I know how to limit rows based on the user but not the columns.  We have a package that has employee info and I want to restrict the salary/rate columns for certain groups that don't need to see it.  Any info is greatly appreciated.  Thanks.

[Tsunami]

UPDATE:  Ok, I've found where I can set object security.  I simply add the appropriate user group and selected 'Deny Access' for the salary information.  However, when I tried to run query studio w/ my test user, none of the columns from that package showed up.   ???

[rlp]

We have been doing this by creating a custom data item in the FM model using code similar to this:

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

We hide the original column to force all access through the new data item.

The advantage to using this over regular Object Security is that all users can run reports that reference the protected data items.  My report developers just use the new column and don't have to worry about it being accessible to one group and not accessible to another.

[Tsunami]

Should this be applied to the object in the Database layer?

[josepherwin]

No modification should be performed in your database layer. Object security can be applied in either Metadata Layer (or Intermediate Layer) and Presentation Layer.

If it is columns that you wish to restrict, then you need to add the Object Security TO THE DESIRED COLUMN in Metadata Layer.

However, if it is the actual Fact or Dimension table (or even the whole Namespace) then you'd do it in Presentation Layer.

E.g

You dont want any user from Group A to be able to see employee First Name

Employee Dimension
   First Name --> Highlight the First Name, then click on Object Security, then select DENY for Group A
   Last Name

Once you do that, it will normally open a warning MsgBox telling you that the security from the parent object have been overridden.

Hope that makes sense

[Tsunami]

Yeah, that makes sense.  But for some reason, it's not working properly.  If I allow access to everything, then select 'deny' on one query subject, nothing shows up except for the namespaces in the user's query studio.

Btw, what's the reason for not applying security in the database layer?

[josepherwin]

Oh, i see your issue.

If you add a group to the Object Security and you click Deny in one of the query subjects, in the parent object you need to add the same group in the Object Security and select Allow. This should resolve ur issue.

The reason for not applying security in DB layer is that your Database Layer should mimic whatever your source is (in other words, it should all be a simple database query subjects). Keep your DB layer as clean as possible.

All of your modification (filters, securities, etc) should then be performed in your Metadata Layer and/or in Presentation layer.

This is just one of the Cognos best practices in designing FM

[Tsunami]

Thanks a lot.  I got it working now.  Also, thanks for the explaination about the DB layer.  I had forgotten the reason behind it.