Configuring Framework Manager Row Level Security Against Ldap

Tip or Technique Configuring Framework Manager Row Level Security against LDAP Product(s): IBM Cognos ReportNet, IBM Cognos 8 Area of Interest: Security

Configuring Framework Manager Row Level Security against LDAP

2

Copyright Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to the information contained in this document will be documented in subsequent editions. This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos. Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBM and the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries, or both. All other names are trademarks or registered trademarks of their respective companies. Information about Cognos products can be found at www.cognos.com This document is maintained by the Best Practices, Product and Technology team. You can send comments, suggestions, and additions to [email protected] .

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

3

Contents 1.

INTRODUCTION ............................................................................................ 4

1.1

PRE-REQUISITES ...................................................................................................4

2.

CONFIGURING LDAP FOR THE SECURITY EXAMPLE .................................... 4

3.

MODIFYING THE FRAMEWORK MANAGER MODEL....................................... 6

3.1 3.2 3.3 3.4

OPEN THE GO SALES DATA WAREHOUSE MODEL ............................................................6 CREATE THE PARAMETER MAP ...................................................................................6 APPLY THE SECURITY MAP AND SESSION PARAMETER .......................................................7 CONFIRM THE RESULT BY LOGGING ON AS DIFFERENT USERS. ............................................ 10

4.

CSV.............................................................................................................. 11

4.1

CSVIDENTITYNAME AND CSVIDENTITYNAMELIST......................................................... 11

5.

IBM COGNOS SESSION VARIABLES............................................................ 12

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

4

1 INTRODUCTION We will add a security filter to a Query Subject to limit the user’s view of the data. 1.1

Pre-requisites • Configure an LDAP • Add users to directory server • Configure IBM Cognos Configuration for the LDAP Server

2 Configuring LDAP for the security example

1

Open that instance of the directory server and Import the users from the LDIF file named addusers.ldif using the Import Database Option

2

Browse for the addusers.ldif file. This completes the configuration of the directory server with 7 users

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

3

5

To configure IBM Cognos 8 to use that directory newly configured directory server. Open Configuration Manager and add a) Authentication provider named LDAP b) Namespace named LDAP c) Host and port number, the host name of the directory server and the port it is running on, for example wotttcs-tayloclp:389 d) Base Distinguished Name like dc=ent, dc=ad, dc=cognos,dc=com e) User lookup of (uid=${userID}) f) Bind user DN and password – cn=Directory Manager and the password from the directory server Note: steps (a) and (b) must be LDAP for the script and (c) must be (uid=${userID}) all others may vary based on the directory server creation

4

Save the configuration and restart the server

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

6

3 Modifying the Framework Manager Model 3.1

Open the Go Sales Data Warehouse Model In this example we are going to modify the Employee detail fact Query Subject to add security filters. This Query Subject contains sensitive employee data (Salary, Vacation Days, Sick Days etc). We are going to restrict the user who is signed on to see only the data applicable to his/her employee record. Using Framework Manager open the Go_Data_Warehouse Model

3.2

Create the Parameter Map The Staff_Code to uniquely identify each user; however the LDAP user name does not match the name in the Staff Query Subject. To solve this issue we will first create a Parameter map Using the Project Viewer, locate the Parameter Map Folder and select the Create Parameter Map option from the context menu

Using the wizard name the Parameter Map Security_Map and select the option to “Manually enter the parameter keys, and/or import them from a file” Click the import button and select security_map.csv Contents of the file AOrozco,4051 ARodriguez,4082 AWalter,4091 ALastman,4034 AMcCormick,4033 AWilcox,4030 BScott,4036 Click the finished button to save the security map.

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

3.3

7

Apply the Security Map and Session Parameter Using the Project Viewer, locate the Employee detail fact Query Subject and select the Edit option from the context menu.

The definition of the Employee detail Fact Query Subject will be displayed

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

Click the Filters tab and then click the

8

button to add a new filter

Create the following Filter expression using Model tab to insert the name of the query item ([Fact data].[Employee detail fact].[Staff key] and the Parameters tab to add #$Security_Map{ $account.personalInfo.userName}#

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

9

The completed filter expression should look as follows [Fact data].[Employee detail fact].[Staff key] = #$Security_Map{ $account.personalInfo.userName}# Hint to see all Session values select the Session Parameters from the Project Menu; it will display the following dialog box with the option to override the values

The completed SQL will look as follows

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

3.4

10

Confirm the result by logging on as different users. To confirm the security filter works correctly log on to the FM Model and test the Employee detail fact Query Subject using different users. List of Users AOrozco ARodriguez AWalter ALastman AMcCormick AWilcox BScott

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

11

Simple test of the query subject with the filter applied

For best results add the Staff_Name from the Staff_Dimension to the Employee detail fact Query Subject this will validate the user name matches the value in the Staff dimension.

4 CSV 4.1

CSVIdentityName and CSVIdentityNameList

CSVIdentityName

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

12

Use the identity information of the current authenticated user to lookup values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas. Syntax CSVIdentityName ( $parameter_map_name [ , separator_string ] ) Example #CSVIdentityName ( $security_clearance_level_map )# Result: 'level_500' , 'level_501' , 'level_700'

CSVIdentityNameList Returns the pieces of the user's identity (account name, group names, role names) as

a list of strings. The unique list of values is returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas. Syntax CSVIdentityNameList ( [ separator_string ] ) Example #CSVIdentityNameList ( )# Result: 'Everyone' , 'Report Administrators' , 'Query User'

5 IBM Cognos Session Variables Modify the Employee detail fact Query subject and add the following syntax to the select statement # sq(CSVIdentityNameList( )) # as List, Note: sq; single quote function must be added because the value returned is a string and the ‘as’ must be used to alias the name (in this example the column name will be aliased as the name List) To confirm the CSVIdentityNameList function works correctly log on to the FM Model using different users and test the Employee detail fact Query Subject

IBM Cognos Proprietary Information

Configuring Framework Manager Row Level Security against LDAP

List of Users AOrozco ARodriguez AWalter ALastman AMcCormick AWilcox BScott

Note the roles, username and authentication provider name used

IBM Cognos Proprietary Information

13