Cognos Column Level Security[Solved]

[Cognosuser2019]

Hi All,

We have a requirement to implement column level security in cognos reports. Suppose there are 4 secured columns (e.g SSN, birthdate etc). It should be visible to only certain set of users. Remaining users should see blank values. There can be cases where a user can see SSN but  is not authorized to view birthdate.
Can anyone suggest the best way to do it? There are several business units and roles. I am trying to avoid creating groups for each set of users and do it table driven.

I came across below article (page 36) but i am not sure how to implement it in FM. It would be great if someone can help with this.
http://www.smu.edu/BusinessFinance/OIT/Events/~/media/Site/BusinessFinance/OIT/Cognos/Application%20of%20data-level%20security%20in%20Framework%20Manager%20-%20Presentation.ashx

Thanks,
Reshma

[cognostechie]

Hi Reshma -

This can be achieved in Framework Manager with only 2 steps. The idea is to create a mapping which can check the group the user belongs to and then hide the values of column if the user is not authorized to see it. You will find the Cognos security groups in the Cognos Administration section.

As an example, the name of the user is 'John Allowed' and he is a member of 'Executives' and he is allowed to see the SSN but another user whose name is 'John Notallowed' is a member of the group 'Field_Staff' is not allowed to see the SSN values.

1> Create a Parameter Map in FM. Select the method ('Manually enter the parameter keys...'). Name the parameter map 'SSN_Access'
2> Create two rows in the Parameter Map, one for each group. In the Key section, type the name of the cognos security group exactly as it appears in Cognos and
      in the value section, type the level of access the user has. In this case the two rows would look like this:

      Key                        Value
      ------------------------------------------

      Executives              Full_Access
      Field_Staff              No_Access




3> Open the Query subject in the Business Layer and put this expression in the SSN Column:


      Case #sq(CSVIdentityName(%SSN_Access))#

              When '''Full_Access''' then [Database View].[Employees].[SSN]                      <---    Point this to the column for SSN
              When '''No_Access''' then '' 

              Else ''

      End

Basically, this will check the user who is running the report and it will get the value 'Full_Access' or 'No_Access' depending on which group he/she belongs to.

Hope it helps

[bdbits]

Alternatively, if you open your FM model and select a column, you can choose menu item Actions > Specify Object Security, with options to Allow and Deny to users or groups from your authentication namespaces. Since I have not used this and do not have time to test right now, I am not sure what happens if a report that included that column was opened by someone who was denied access. They might get an error instead of blank values. It may be worth a try.

I am not sure how doing it from a table is better than using groups defined in your authentication source (e.g. Active Directory). You will have to represent the business units and roles in one place or the other, if the security needs differ.

[Cognosuser2019]

cognostechie & bdbits - Thank you so much for your inputs. I will try it out.

I was trying to avoid creating user groups as we have close to 10,000 users and several business units and thier sub divisions. So it will be difficult to maintain the several groups.  I was trying to find if we can use the users login id and do a lookup in table (using parameter map or something). The table will have user login ids and a yes or No indicator columns for each secured columns like shown below.

Table
Col1    SSN  Birth Date
User 1 Y        N
User 2 N       N
User 3 Y        Y
User 4 N       Y

Paramerter Map (SSN_Access)
User1 Y
User1 N

Is there a way to write the case statement so that instead of looking for the group it looks for the user id..and checks the Key value ('Y' or 'N'). And if for that user indicator is Y we will provide access to the column value else show blank.

Case #$account.defaultName# <<<I am not sure about this>>
when 'Y' then
[Database Layer].[ADDR_DIM].[SSN]
else
""
End

[cognostechie]

Reshma - I must say you gave me a brilliant idea ! Thanks !

Here is your case statement. I tested it with the same table structure as you mentioned.

Once you have created a parameter map pointing to the query subject which is using that security table then use this
in the Business layer of the SSN field.

Case # sq( $SSN_Access {  $account.defaultName } ) #
  When 'Y' then [Database Layer].[ADDR_DIM].[SSN]
Else ''
End

That being said, if you have 10000 users then you must be having Cognos groups already defined otherwise everybody in the company will have full access to everything. However making it work with user names is better in a way because the security table can be updated from the Employee dimension so it can be automated.
Let me know how it goes.

[Cognosuser2019]

Cool :) It worked....This is exactly what i was looking for...But somehow i was strggling with the syntax....

Security is not defined in the current system and all reports are accessible to all. Only the secured columns are not accessible to all. We are working on replacing the existing reporting system with Cognos....and are in a process of defining the security...

once again thanks a lot... This is awesome:)