Ironside Tech Tip: 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 filter strategies 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 filter strategies work best for our data and why?
There are three philosophies and approaches to this:
- 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.
- 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.
- 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. It’s important to consider the filter strategies available to you and choose what works best for your organization.
Security topics are a key component of all Ironside’s Cognos training classes. To learn more about implementing security in Cognos, see our upcoming public courses.
