Cognos has an extremely intricate security system. It is well suited for secure reports and confidential information. Some clients expose portions of their platform to the public, safe in the knowledge that users can only see what they’re allowed to see. However, all of this security can be for nothing through careless report design.

When building any secure reports, it is essential that security be handled in the back end. There have been countless reports built with JavaScript enforced security built on the prompt page, or with security handled with conditional blocks and parameters. Token prompts, while perfect for building dynamic reports, can create holes in which malicious users can attempt to retrieve data that would normally be hidden to them.

This document is not intended to be a guide to hacking Cognos reports. Indeed, many reports would not even require such drastic oversight. Proper usage of macros and filters will solve a majority of the potential problems. Nor does every report need such stringent security. Many times filters are simply used as a means of speeding up the report, or allowing users to narrow their focus.

In this post I will go over possible security flaws with the token prompt.

Consider the following request: A report which shows Employee, Manager, a prompt to choose product line, product type or name. This report will be available to all users to show sales ranking throughout the company.

In order to fulfill the prompt requirement, a token prompt is created with the following static values:

While the data item in the report would be a simple:
#prompt(‘Field’,’token’)#

When running the report, the user might see the following output:

But a malicious user may attempt to see each employee’s full address.

Nothing in the report was changed, and yet the user can now see confidential information. How was the user able to accomplish this?

Simply by changing the value option in the prompt with IE Developer Tools (each of the major browsers has a similar tool), the user can choose to see any item in the model. “But,” I hear you say, “I can set object level security inside Framework to deny that user group, I’ll even set it on the data layer!” That will certainly work well to prevent users from selecting items from the model, but there is still a work around.

Entering {“EMP_EMPLOYEE_DIM”.”ADDRESS1″ || ‘, ‘ || nvl(“EMP_EMPLOYEE_DIM”.”ADDRESS1″,’ ‘) || ‘, ‘ || “EMP_EMPLOYEE_DIM”.”PROV_STATE” || ‘, ‘ || “EMP_EMPLOYEE_DIM”.”POSTAL_ZONE”} into the value will work just as well, and because it doesn’t reference the data item path, Cognos won’t throw an error. The curly brackets there tell Cognos to execute the code as is. Note that this SQL doesn’t include the country. Cognos won’t attempt to build a join based on custom SQL like this, so including “Branch_region_dimension”.”COUNTRY_EN” would only result in an error.

Since adding custom SQL, like that concatenated string, works, would subqueries also work? In this example any attempt to use a subquery in the data item list would fail, as (at least in Oracle) subqueries are not supported in the Group By. But look what might happen when we remove the two measures from the list:

By adding {(SELECT rtrim (xmlagg (xmlelement (e, owner||’.’||table_name || ‘,’)).extract (‘//text()’), ‘,’) table_names FROM all_tables)} we can now see every single table available. Similar queries can be run to see the fields in each table. At this point the user has full read access to the database, limited entirely by whatever security is applied to the data source user.

The exploits are only possible because the data item contained only the token prompt. I’m not saying that token prompts should never be used; many of my reports are based on the functionality. Instead, use them wisely.

One workaround that I have found is a combination of a new table and a lookup. Build a new table called “TokenExpressions” with the fields “Key”, “Expression”, “Name”, “Report”, and “ReportGroup”. Import that into your framework. Create a parameter map (tokenLookup) based on that table with the TokenExpressions.Report+’-‘+TokenExpressions.Key as Key and TokenExpressions.Expression as Value. In the report, set up your value prompt with the source query as TokenExpressions.Key as Use and TokenExpressions.Name as Display. And finally, in the report queries, use the expression #’ReportName-‘+sq($tokenLookup{prompt(‘Field’,’integer’))}#. In addition to securing the report against unexpected inputs, you also make it significantly easier to add or remove options in the report.

The lesson learned is that using prompts as a security measure is inherently risky. Malicious users with access to web development tools (IE has them by default) may override the existing values and see data that isn’t meant for their eyes.

Finally, it is worth mentioning that using the curly brackets for subqueries can have legitimate uses. I have not found any other way to handle correlated subqueries, for instance. Oracle analytic functions can usually only be accessed through the curly brackets.