Where to Apply Filter Strategies: In the Database, in the Package, or in the Report?

Guarding the security of and defining access to data is often a necessary business requirement. Not all users can or should have access to a total data set, so filters need to be applied to restrict what appears to different users running reports. As Cognos developers, the first question we should ask when faced with this requirement is what is the best way to filter the data and why?

There are three philosophies and approaches to this:

1. Use row-level security in the database with single sign-on or a password challenge such that the credentials of individual users are executing the query to the data source, thereby limiting that user’s access to certain rows pre-determined by the DBA.
2. Apply the filter in the package so that only certain data elements pre-determined by the Metadata Modeler in Framework Manager will appear to different users accessing that package.
3. Build the filter into individual reports so report authors can allow or deny certain data elements on an as-needed basis in Report Studio.

 Low-Level Security Method

High level Government organizations like the NSA, CIA, Pentagon, etc. commonly use the row-level security method. This approach has the following advantages and disadvantages:

Pluses:

• It assures 100% compliance to pre-defined security levels
• Access to the Database can be Account/Password Protected
• The Database log will show who accessed what data and when

Minuses:

• Special circumstances around accessing data cannot be overridden
• Coordination is required between the LDAP and Cognos administrators, and additional programming development by the DBA is required to set up and maintain this kind of security
• Security for Cognos group access/denial cannot be implemented

Package Filtering

Package filtering is similar to the row-level security in that it restricts data requirements, but it also has a few differences as you can see below:

Pluses:

• It assures 100% compliance to pre-defined security levels
• Security can be applied entirely by the Metadata Modeler in Framework Manager
• Security applied for Cognos group accessanddenial CAN be implemented
• No external administrators need be involved

Minuses:

• Special circumstances around accessing data cannot be overridden
• Who and what was accessed in the Database is not logged

Individual Report Filtering             

The third technique can accomplish many of the same as Techniques #1 and #2 with the following features, benefits, and pitfalls:

Pluses:

• Security can be set on a report by report basis
• Security applied based on Cognos group access/denial can be implemented
• No external administrators need to be involved
• Reports can be password protected
• A high degree of control is possible over allowing or denying data containers or Data Items
• Security can be done entirely by the report author in Report Studio
• Special circumstances around access to data can be highly customized

Minuses:

• Report by report security settings are time consuming and labor intensive
• Authors must be skilled in query coding and functions and must understand how to use session parameters
• The Cognos and/or LDAP security administrator may need to establish groups or roles to enable certain types of security scenarios
• Who and what was accessed in the database is not logged

SUMMARY                                

Security needs vary by business line and type, as well as by organizational requirements. What you need to implement in your environment will be partially or wholly influenced by these.