How to setup Cognos security?

[milmand]

The situation:
- A company has more then 10,000 users.
- Each user can only see his own data.
- All users use a set of 20 IWR reports.
- A new user may be added or the existing user may be changed at any time.
- In order to implement security on what basically is a User level, a user class was setup for each user. This was done because filters can only be applied to user classes in a catalog.
The problem:
Whenever a new user is added or the existing user is changed, that user must wait for the nightly process to update catalogs. It can not be done in real time because a new user class must be added to the catalog. This means shutting down IWR and changing and republishing .IMR files.
The question.
- How to set up security to get around this situation?
- The purpose is to avoid changing catalogs in order to add or edit user's data access privileges.
- The ultimate goal is to be able to refresh Cognos security with out impacting the user's ability to use reports.

Thanks in advance for your help.

Dima.

 

[griffindm]

Dima,

I don't know if you are using a catalog user-class object filter or not. If you are, you do not have to make any catalog changes other than adding the new user-class. Post back if you need more info on this. It basically means creating a row level filter where the string of the user-class matches a data column, such as employee id or something similar. This can be setup at a parent user class and will cascade down to lower level user classes.

That said, I have had luck just going into the published catalog on the web server and adding the user class there, and then manually adding the new user group via Access Manager. This could be done on the fly in IWR version 6. I'm not aware of anything that would preclude doing this in Series 7, unless there is a tie into Upfront that this might bypass.

Give it a whirl and let me know if it allows for more real-time changes (back up the catalog and lae first, just in case!).

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[milmand]

Dave,
I'm glad you answered my post. I saw lots of good answers from you in this forum.

To answer your question, yes, I am using catalog user class object to apply filters. Unfortunately, due to the way my company does business, I have to create a user class for each individual user - that's 10K user classes! All 10,000 users are using the same set of 20 reports. When a user runs a report, the data must be specific to that particular user. This means row-level security.
One of the ideas that I was exploring is to group all users into 3 user classes and remove catalog-level filters. Instead, I wanted to assign a unique database signon to each user in Access Manager. Unfortunately, I was told by our DBA that there is no way to implement row-level security based on UserID in the database.

I am doing all of this because I am trying to avoid having to update catalogs with new user classes whenever a new users are created. Remember, user=user class in my case!

Having said that, I doubght that I will be able to open a catalog, create new user class or change filters for existing one, and then save this catalog while there are open requests or reports running against this catalog.

On Monday, I will try to do this in the PUBLISHED=>APPS folder on the IWR server while a report is running against this catalog. If I can do this manually, chances are that the script will be able to do this as well.

This seems to me like a fairly typical thing to wan to do. I was surprized when Cognos support told me that they never had to resolve such an issue.

Anyway, I'll keep you posted on my progress.

In the mean time, thank you for helping out.

Dima.

 

[griffindm]

Dima,

You can script the addition of both user classes in the catalog and the addition of users and user groups in Access Manager (via the batch language). This could simplify the process, if you could create a table to stage the change requests (add and deletes) and then have the macro run against the table in a recurring schedule (polling) using a scheduler. Never had your extreme situation (10K users) but that hefty of an environment would mean it would provide much better payback for the investment to construct it.

Let me know if you want ideas on it, and how the attempt to modify the catalog in situ works.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[milmand]

Dave,
I am already using the script to update both, the catalog and the Access Manager/LDAP security.
The problem is that I can only execute this script at night, when there are no users runing reports.
If I run the script while there are reports running, the script fails because it is unable to change (update security only) the .CAT file while it is being locked.

Any ideas?

Thanks

Dima

 

[griffindm]

Dima,

I'm not sure on this one. In client-side piece, the first user to open that catalog as the Creator class effectively locks the catalog from furthter changes. It seems that IWR may lock the catalog up even when the report users are not running in Creator class. (or are any of the report users you mention running in a generic Creator-level user group?)

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[BennyYacaco]

Dima,

With regards to your security problem. I may have a work around to it. We are in the same situation right now, my client wants a row-level security on their reports. Let me share to you what I think might solve our problem.

First Step: you create a user table that contains basically 2 columns, the first is USER ID, the one that the user logs into the database and the seconds is the filter, for example CENTER CODE. If you want, you can also add another column which might contain the REPORT NAME.

Second Step: assuming that the table that you want to perform your row-level filtering has a column of CENTER CODE (same as the on in the user table), create a database VIEW joining this table with the user table that holds the definition of the all the users and what center codes theny can they retrieve.

Third Step: define the VIEW that you just created in the catalog, and in the User Profiles, edit the FILTER for that particular VIEW. You can have a filter like this ?USER_ID? = VIEW.USER_ID, what it would do is you can only retrieve the rows/data that have the same USER_ID defined in the user table and the user id that you used to log on to the database when you opened the report.

Hope This Helps.

-Benny Y.

 

[milmand]

Guys,
Thanks for helping me out on this one.
Here's what I will do: (hope it works)
In order to avoid creating new user classes and adding filters to them in the catalog, I will have only 3 major user classes.
I will create a security table in the DB with "UID", "REPORT" and "FILTER" columns.
I will implemnt security on report level by adding a filter to each report. This filter will compare UID of whoever is running the report to UID in the security table. The table will be joined appropriately.

I'll keep oyu posted.

Thanks.

 

[griffindm]

Dima,

If they can live with that level of granularity in the user groups it would certainly eliminate an administrative nightmare.

Good luck!

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com