Drill Through to Sensitive Data

[jv_oz]

Hi all,

I have a report built over a Powerplay cube.  The users now would like the ability to drill to detail from the data warehouse via the FM package. That is all ok.  The data they are drilling to is sensitive at the granular level and thus restricted, whilst the aggregated data in the cube is de-personalised an therefore approved for wider distribution.

As far as I can see there are three options:
1. Add the drill through to the primary report and put the target report in a secured folder in Cognos Connection so that when a non allowed person tries to drill they get an error because they cannot see the target report.
2. Create a second version of the primary report, add the drill through into it and then secure both through CC.
3. Add data level security in FM so if an excluded person tries to drill through they get the target report with nothing in it.

Can anyone recommend any other options or solutions to this problem?

Thanks

John

[cognos810]

Hello jv_oz,
How about conditionally rendering the drill through link based on user's authorization?

-Cognos810

[bdbits]

Bear in mind that if you put data security in the model (your #3), it will apply everywhere that data is used. This might be acceptable, even desirable, but is a consideration.

In terms of user experience, I do not like users to think they have an option to do something (like drill-through) but then it does not appear to work, or worse generates an error. So of your options, I prefer #2.

I think conditional rendering as cognos810 suggested is better and probably easier for maintenance.

[jv_oz]

Thanks for the advice cognos810 and bdbits about conditionally rendering the drill through link based on user's authorization.  Can you please give me some rough steps as to how I would do this?  Thanks in advance.  John

[cognos810]

Lets say you have a list in your report.
Create a data item and name it [Memberships] as an example, in the query that the list references with the expression in it as:
#sq(CSVIdentityNameList())#
This will give you a list of all the group/roles/namespaces a user is a member of.
Unlock your list column and insert the same data item that you want condition drill through on twice, next to each other. Define your drill through on one of the data item and leave the other alone. (Alternatively, you can use two columns with the same data item in it). Make sure to add [Memberships] data item to the "properties" property of the list.
Lets say you have a group named "DRILL_THROUGH_NOT_ALLOWED" that is used to restrict sensitive data access.
Create a String Variable with expression,
CASE
WHEN [Your_Query_name].[Memberships] contains 'DRILL_THROUGH_NOT_ALLOWED'
THEN 'HIDE_DT'
ELSE 'SHOW_DT'
END
Add 'HIDE_DT' and 'SHOW_DT' as the values of the variable.

Next, go back to your List and apply rendering to the data items. Render the data item with NO drill through definition for 'HIDE' and apply rendering of the data item with the Drill through definition on it for 'SHOW'. If you have used separate columns, apply rendering to the columns instead.

Hope you got the idea :).

Let me know if it works for you.

-Cognos810

[navissar]

The common thing to all the suggestions here is that security is done on the front end, at report or Cognos Connection level.
This will work, superficially, but is wrong for several reasons:
1. It's hackable, or it leaves room for users to mistakenly expose data. For instance, CSVIdentitiynamelist brings names of roles and active directory groups the user is a member of, and even if you have a "Cannot see DT) active directory group, doesn't mean someone won't create a role with the same name...
Another example: A user who doesn't fully understand what they're doing is copying a restricted report from a restricted folder to which he is authorized, to another location where it becomes public. By the time you realise he did that, data had already leaked.
2. It sometimes adds overhead on your queries. If you have to check the CSVIdentitiyNameList every time you run a report, or if you use framework security which means you add a where clause to you queries, you make everyone suffer worse performance.

Data security should be done in the database. Almost all major DB vendors can be connected to Cognos with SSO enabled, and you can set up security over tables, columns, whatever you need. You can also encrypt/hash the data (As we sometimes do with credit card numbers) to allow the report to run without exposing sensitive data. The one rule of information security is - always do it as close to source as possible.

[cognos810]

Nimrod,
Absolutely agree with your point that the security should be implemented as close to the data source as possible via SSO mechanisms. That makes the security global too and is not limited to just the cognos application. Meaning, any application that a user has to access the data from the source will be filtered as per his/her privileges.
What I do not agree with is someone being able to create a role with the same name. Why would one be able to, if s/he is not a member of the Directory Administrators/System Administrators/any other group/role used to secure these functionalities. The only potential hole I have seen there is use of SQL objects in the reports. If you do not allow anyone to query the data source via the SQL objects and your administration is secure, there is no reason to worry about it.

-Cognos810

[navissar]

I assume there could be a directory administrator who is allowed to set up permissions in Cognos, but not allowed to see some data. I actually WAS a system admin with no access to customers' credit card data. Theoretically, if we had done security using CSVIdentityNameList, I would have been able to create a role ("CC_Auth"), add myself to it - and see data I'm not supposed to see.

[bdbits]

I generally like using SSO for many things, and securing stuff with user credentials at as low a level as reasonable. That said, I have worked with multiple large companies (Fortune 1000, large utilities, government, etc.) over the decades and databases are usually not SSO. That is my experience, but maybe I just worked with different companies than the average person here on Cognoise.

For example, at my current workplace end users do not normally get any access to application or data warehouse databases using their own credentials. We have proxy/service accounts with whatever permissions are needed (Cognos generally read-only) for applications to work with the database, and these applications have their own security layer (usually AD-based). In my experience, this kind of setup is very very common, far more than using SSO for the database connection. So the only way you are going to get anything directly from the database is if you somehow obtained a service account logon, in which case there are much bigger problems.

I often embed any required security filtering in the FM model, based on Active Directory group memberships if possible (or indirectly through Cognos groups with AD group members). Same thing with cubes, using custom views. Because they are part of the package/cube, the users are not going to get around it just by manipulating report contents, and they can freely create their own reports. Also we do not give users permissions to create reports anywhere they see fit. They often have to save within certain folders, and the public areas are locked down to select individuals. So the likelihood of them exposing anything via Cognos public folders is very low. Naturally, they can export the report if you allow them the capability and publish it anywhere they like - e.g. Facebook - but this is a personnel problem.

The suggested attempted workarounds assume people with elevated permissions are going to be able to create AD groups or Cognos roles. Every place I have worked, those have been sysadmins (at least on production systems), or delegated people within a subgroup responsible for that subgroup's data. In other words, people who could already likely get around any SSO restrictions if they are so inclined. Otherwise, users never get permissions to set up groups or roles in any security context like Cognos or Active Directory. They have to make a formal request, and then the sysadmins (or delegated admins) set them up after validating the request as appropriate.

I do agree that putting security in the report alone is generally not good enough, if for no other reason than it practically precludes self-service reporting. But I do think using roles for rendering purposes is fine, in terms of creating a good user experience.

[jv_oz]

Ok. So thank you all for your input.  My apologies for the late reply, but I have been on leave overseas for the past week.

My take on this is that I need to put security into the FM package, either at the object or data level.  Then I use the now secure package to create the reports.  Can I then use the concept developed by @cognos810 to build the report so I end up with either no drill through (if you're not allowed) or drill through is enabled (if you do have permission to see the more detailed data)?

If I can, then what would I need to change in @cognos810's concept to make it work?

Thanks again guys!

John