User Level Security

[Sak]

Hi All,

I have got a requirement where i need to implement security to based on user access details.
Detail Requirement:
I have few reports which has already been developed.
Right now its functionality is users can see all the data populated in a Prompt page (saw like, i have a prompt where the values are Mcdonald - NJ, McDonald - NY, McDonald - CT etc...).
So now all the users who are using these report can see all the list of drop down values and its appropriate report results.
Now, what the user looking for is, if users from NJ logs runs the report, only the prompt value should display McDonald - NJ, like vise for all the state users. Also few hign end users can see all the values in the drop down.

Note: The report is conected via a common portal and from there it is connected to Cognos Connection. Once they click cognos reports in the portal, it pop up with the user id and password of the respective users. And then only it'll display the Cognos Connection page.

I'm using Cognos 8.4 and the database i'm using is SQL Server 2008 and also Oracle 10g.

Any help would be much appreciated.

[bdbits]

You can do this with security filters in the Framework Manager model. There is a couple of different ways to apply this, which you can find explained pretty well in the FM user guide. Having them in the model and thus the package(s) is the way to go, as the package is the gateway to the data and so will filter data no matter how they are trying to access it.

[HalfBloodPrince]

To specify row level security:
1. Click the query subject with which you want to work, and from the Action
menu click Specify Data Security.
2. To add new users:
a. Click Add Groups.
b. In the Select Users and Groups window, add users, groups, or roles.
c. In the Select Users and Groups window, click OK.
3. If you want to base the group on an existing group, click the Based On
column.
4. If you want to add a filter to a group, in the Filter column, click either
Create/Edit Embedded filter or Insert from Model. These options allow you
to either select an existing filter from your model to use or define the
expression for a new filter.

[twlarsen]

Depending on the number of groups/users you have, you might set up a security lookup table.  For instance, you could have UserID and StateID.  This table would link to your State/Store Dimension.  If a user had more than one StateID, they would just have multiple records in the table.  You then create a data filter that says something like current user = user_id to only bring back the correct state/store dimension records.

I prefer to use the table as it's easier for me to modify then going into Cognos Data Security.  If your company already uses a security setup, you can just have it populate this table.  It can also double as a burst table if you need to burst the reports.

Todd