data security

[cognosengine]

table org: org_id, org_name
table emp: emp_id, emp_name

bridge table emp_org: emp_id, org_id, start_Date, end_date gives info about

which emp has organizations with emp_id,org_id as the fields in this table.

fact table related to org table by org_id
no relation between fact and emp table
emp_org table is related to emp table through emp_id and organization table through org_id

requirement is applying data security(using filters/param maps) such that
every emp when logged on should see only his org_names when used in a report

with org_names, emP_names, measures from fact etc

[rockytopmark]

Assumption:  You session parameters include a value that is synonymous with either the emp_id or emp_name

Can be done in Model (if you have Ad Hoc Report and/or Query Studio authoring)
or in Reports themselves using a report or model filter, if no Ad Hoc.

In the model, add a filter to the Fact table's Query Subject, that interrogates the emp_id session variable, and filters on it, returning all the employee's org_ids, for use with an IN clause:

fact_table.org_id IN (Select org_id from emp_org where emp_id = #sq($account.parameters.emp_id)#)

You may want to change the Fact table's query subject to be treated "as a view" in the option settings dialog, in order to enforce this filter condition 100% of the time.


If you are filtering in the Reports, then use a fairly similar approach there, but understand your security is at the outermost layer and is not at the best place, from a security viewpoint.

Just one of a probable many ways this can be done...

[cognos8.2]

Yes, session parameter can be used in macro filter for implementin data level security, I too have used in my project.

[cognosengine]

rocky and cognos 8.2,
Could you please explain this in a step by step way because, I tried using the session paramater and it is still showing all the org's for any employee login. We are using Active DIrectory for security and login names are generally lastname and firstletter of firstname and using the login's, the employee's should be able to see only their org's. The bridge table berween employee and organization doesnot have employee name and rather has an emp_id and org_id.Employee table has emp_id, emp_name.Organization table has org_id. Organization and employee tables are related through the bridge table and only organization is related to fact.
Thanks in Advance.

[nlnreddy]

I am struggling with the same thing.
In our case we have a security table(application level) that has UserID and list of companies that userid has access.  This security works on the application side (NOT a cognos application)

I am trying to leverage the security model for reporting, so that when a user runs a report she/he should only be viewing the data for the companies they have access. sounds very straight forward and I am sure I am missing basics here.

balance (fact table)
com_usrlist (security table)
also created a param map with information from security table.

Detailed explanation is greatly appreciated or please guide to the resource.

Thank You
N L N Reddy
8178714803

[rockytopmark]

Create an INNER JOIN relationship between the Fact and the Security Table.  The security table should have a common dimension to the fact.

Now create a Filter that can be done at the Data Level security of the Fact or as a model filter to be used in the reports, that will return only the Dimension IDs that the logged in user has access to.

Based on the resultset of the Security Table, your Facts resultset will be limited to only rows where the Fact's dimension ID is IN the Security table's results.

hth