Unique Security Requirement for Implementation - Restrict SPI from Admin

[FranklyOneZero]

We have a unique requirement for our potential implementation of Cognos we are currently evaluating.  Our pilot was centered around 10.2.2 but is now centered around 11.  We need to be able to restrict access to sensitive personal information (columns in our DB2 tables) to the administrators of the system.  The DB2 database itself is encrypted but we need to be able to prevent the sysadmin from executing a Report Studio report, or a Query Studio query and seeing these SPI fields unencrypted.  Is it possible to accomplish this within the FM model, the Cognos admin panel, or otherwise, within a shared, hosted environment?   Do we need a dedicated server in order to accomplish this?  ???

[MFGF]

We have a unique requirement for our potential implementation of Cognos we are currently evaluating.  Our pilot was centered around 10.2.2 but is now centered around 11.  We need to be able to restrict access to sensitive personal information (columns in our DB2 tables) to the administrators of the system.  The DB2 database itself is encrypted but we need to be able to prevent the sysadmin from executing a Report Studio report, or a Query Studio query and seeing these SPI fields unencrypted.  Is it possible to accomplish this within the FM model, the Cognos admin panel, or otherwise, within a shared, hosted environment?   Do we need a dedicated server in order to accomplish this?  ???

Hi,

For any "normal" user you can add security in FM to prevent them from seeing specific items/query subjects etc. However, if the people in question are members of the System Administrators role in Cognos, they are in essence super-users, and they can see and do everything, regardless of security rules.

Cheers!

MF.

[FranklyOneZero]

Thank you.

[simplebi_guy]

This is just database connection security no?  You don't need separate databases, just use your database accounts.
All the cognos tools (fm, reports, datasets) are going to use the permissions of the database account you use in the datasource to connect to the database, so create an account for the datasource that cannot access those fields/tables in your database and use that for your building your datasource.  You won't be able to report on them but the cognos admins won't be able to access them either.
You can even create a separate datasource and fm model for your cleared administrators so only they can report off that data, or any similar combination you could otherwise do with database security.

[MFGF]

This is just database connection security no?  You don't need separate databases, just use your database accounts.
All the cognos tools (fm, reports, datasets) are going to use the permissions of the database account you use in the datasource to connect to the database, so create an account for the datasource that cannot access those fields/tables in your database and use that for your building your datasource.  You won't be able to report on them but the cognos admins won't be able to access them either.
You can even create a separate datasource and fm model for your cleared administrators so only they can report off that data, or any similar combination you could otherwise do with database security.

The problem with that is that nobody would be able to report on those data items at all - not even the HR people who I'm guessing should have access to them. As soon as you add a second data source that *does* have privileges to see the data, the Cognos system administrators would be able to use it - there's no way to prevent them from being able to do so.

The only solution I can see is a separate Cognos instance these users do not have access to at all.

MF.

[simplebi_guy]

Well yes, someone would need to build the reports etc,  but once they are built (say against masked dev instance) you can lock it down by implementing SSO or leave the datasource authentication blank (userid or password or both if you have different accounts to use) so that the users get prompted when accessing them.  you can also split out admin privs in cognos to limit access to the content in cognos so that only cleared admins can access that content to maintain it.  You can customize all the roles, including most admin tasks (by role or by tenant) so that you don't have everyone using the system admin role.  bottom line is that the cognos admins have no special access outside cognos so limit data access it at that point of contact.   You shouldn't need a second instance since its an enterprise class tool.

[FranklyOneZero]

MFGF, thoughts on simplebi_guy's last comment about leaving the u/p blank in the Cognos data source connection details, then the user would need to enter the u/p when executing a report?  I'm guessing that implementing SSO would remove the u/p entry requirement for the user?

[bdbits]

I am kind of just restating what has been said, but maybe saying it a different way will help make it clearer.

The original question referred to restricting permissions for a "sysadmin". I - and maybe MFGF - assumed this meant someone with Cognos administrator permissions. You can create separate roles with access to various administrative tasks, but the built-in administrator role itself is like root on Unix or administrator on Windows. It has access to everything in Cognos. And at least one person does need to have these Cognos credentials in any given instance. But access to all things Cognos does not mean they can override permissions to the database, but the package/report developer does need the database permissions to create the package/reports.

So you would need another Cognos instance and a database with the same schema and filled with dummy values in the personal columns. Someone with full database-level access to this dev database would then create packages and reports on the non-production Cognos instance, but they are only going to see test data. Then you transfer the package and reports to production Cognos which has a connection set up to the production database, where the package/report developers do not have access to the "personal details" columns (or maybe even Cognos itself). The people who need to see the data are given permissions at the database level, and can run reports that show the data or create their own reports. Whether they see a prompt or not depends on configuration of the data connection, e.g. using SSO or supplying a user name and password.

I hope that does not muddy things up, but rather makes it clearer.

[FranklyOneZero]

That does help, bdbits.  Many times if a concept is rammed into my head in at least a few different ways, then eventually it sinks in, so thank you.   :P

Our challenge with this approach is that the system administrator is offshore in this multi-tenant cloud environment and is the admin of DEV, Test, and PROD.  It sounds like we need a dedicated instance, or at least an onshore system admin, to resolve this requirement. 

Also it just clicked that we would need to provision each user to not only Cognos but also the database, as opposed to going through a Cognos service account to access the database.  I don't think that would fly either.

Thanks all!

[simplebi_guy]

you can do it by role also so you manage the database connection account used in your framework manager model (and even show and hide tables from the model developers if necessary).  that way you do not need to provision so many user accounts in the database, just groups of users.  ideally you want as few groups as possible.  Its not simple but it can be done with some planning.