Wednesday 30 January 2008

Dynamic Security in ReportNet & Cognos8

There are three methods of setting up dynamic security, all of which are done in Framework Manager.

Row level/Data Level Security
This is the simplest form of security and is achieved by using a fixed filter for each user class or group of user classes.
1. Select the query subject - right click and select 'Specify security filters'
2. Click the 'Add Groups' button.
3. Select the user classes you require from the LDAP/AD/NTLM namespace. (For example 'Japan users')
4. Click in the box below 'Filter' and select 'Create/Edit Embedded'. This will open a filter definition window.
5. Enter a filter definition and click OK. For example Country_Code ='JP'
6. Click OK again to exit the set up window.

The security filter will become active once the package the query subject belongs to is published. At runtime when any users with the applicable user class uses a report based on the query subject the query results will be filtered using the filter definition you specified. (In my example all user in the user class 'Japan users' will only have the ability to view data with a country code of 'JP').

Rather than setting up this security many times, try to set it up on a common or conformed dimension query subject.

Dynamic Filtering (CSVIdentityNameList)

This security set up is similar to the first option but is more dynamic in that the filter definition does not have to be altered every time the user class structure is changed. This option is also useful where a large number of user classes are used.

The security method can be used against table held entries or against a calculated query item and matches the user class in the LDAP to an entry in the table (or query item).

1. Select the model query subject that you wish to set up the security on.
2. Right click and select 'Edit definition'

3. Click on the filter button/tab (to add a new filter)
4. Add the required query item to the filter expression.
5. Add the CSVIdentify function to the expression. The end result should look like the following sample:
([Presentation Layer].[Countries].[Country_Code] in (#CSVIdentityNameList()# ))
6. Add any additional filters using a 'OR' to ensure that Administrators and report developers are not filtered:
('Administrators' in (#CSVIdentityNameList()# ))
OR
('Report Administrators') in (#CSVIdentityNameList()# ))
OR
([Business Layer].[Countries].[Country_Code] in (#CSVIdentityNameList()# ))

7.Click OK twice to enable the filter.

The security filter will become active once the package the query subject belongs to is published. At runtime the filter will activate when the query subject is used. The filter works because the CSVIdentifyNameList function lists all userclasses that the runtime user is a member of and then filters against the calculation or table entry. Again - this method is best used where large volumes of user classes are used such as account numbers.

Burst recipient (CAM ID)
This method of security only hides burst report output within Cognos connection but it is useful when users of many different classes access the same folder to view report output. It should be noted that user should only have read, traverse access to the folder. The report burst should be done by a more senior account such as directory admin or report admin.

1. Within Framework Manager add a calculated query item to the query subject that either contains a list of the user classes or where the user class can be calculated.
2. Use a static case statement to define the calculation (an example is shown below):

CASE ([Business Layer].[Countries].[Country_Code] )
WHEN 'JP' THEN ('CAMID("EP Series7:r:authid=3771403238")')
WHEN 'MA' THEN ('CAMID("EP Series7:r:authid=3771403548")')
WHEN 'NL' THEN ('CAMID("EP Series7:r:authid=3049983260")')
WHEN 'NZ' THEN ('CAMID("EP Series7:r:authid=197856540")')
WHEN 'PO' THEN ('CAMID("EP Series7:r:authid=2680884508")')
WHEN 'RO' THEN ('CAMID("EP Series7:r:authid=3872066844")')
WHEN 'SE' THEN ('CAMID("EP Series7:r:authid=4123725084")')
WHEN 'SI' THEN ('CAMID("EP Series7:r:authid=13307164")')
WHEN 'ZA' THEN ('CAMID("EP Series7:r:authid=2999651612")')
ELSE (NULL)
END


3. Use the recipient user class CAM ID as the CASE statment result for the user class or calculation. (This can be found in Cognos Connection by selecting the properties of the user class from the directory.)
4. Publish the package that includes the altered query subject.
5. Within the report add the new query item that uses the CASE statement.
6. Change the burst options of the report so that the burst recipient is based upon the new query item. (The report should be burst so that the report output is logically linked to the userclass selected ie. Japan users can only see the Japan result of the burst report). Make sure that the report bursts to a directory entry.
7. Run the report and burst it.

As the administrator/directory administrator you should be able to check that the report burst correctly and that all report outputs have been created.
Check the security filter works by logging in to Cognos Connection as a user with access to a userclass listed in the CASE statement. When you select show multiple outputs you should only see the output pertaining to the userclass.


Labels:

14 Comments:

At 3 September 2008 at 04:02 , Blogger Shyjuk said...

We are planning to move from oracle to teradata and intend to implement the security based on userclass. The Teradata had business views(BV) for different userclass like sales, finance....Is it possible to capture the userid and have cognos choose to run the business views based on user class. like sales user class users..can have access to Sales_BV and when a finance user class user logon the business views to run should be Finance _BV...I would need your idea on this..and appreciate your help

 
At 3 September 2008 at 08:06 , Blogger Phil Thompson said...

Hi Shyjuk, thanks for the question. It's a good one and a problem I haven't encountered before. A user's userclass can be captured because it is an environment variable.
I would approach the problem in this way: Create a PL/SQL procedure which runs one of the business views depending on a parameter. This paramter should be fed to the PL/SQL by adding the procedure into Framework Manager. Once in Framework Manager you can specify that the parameter is populated by the user class environment variable. Remeber that all of the userclasses that the user has membership will be listed so you will need to handle this in your PL/SQL procedure.
best regards Phil

 
At 3 September 2008 at 14:28 , Blogger Shyjuk said...

Phil, thanks a ton for that quick reply..Is this gonna take hit on the performance of the report ? If so what do you think would be the bottleneck ?

 
At 3 September 2008 at 14:32 , Blogger Phil Thompson said...

Hi Shyjuk,
the report performance will be slower but only because it will be waiting slightly longer for a reply from the db.(The db has to decide in the procedure which business view to run). If you are using Teradata I suspect you will hardly notice any difference at all.
Phil

 
At 29 September 2009 at 22:40 , Blogger Willy Billy said...

hi Phil, we want to control row level access more dynamically using #CSVIdentifyNameList()# macro. I wrote a condition like this.

IF ('STG Solution Stack Trusted Viewers' IN (#CSVIdentityNameList()# ) ) THEN ( [Metadata Layer].[Applications].[PIPELINE_GA] ) ELSE ( [Metadata Layer].[Applications].[PIPELINE_GA] = 'GA' )

but seems not to work.. after IF its showing a red underline. :-))

 
At 30 September 2009 at 09:53 , Blogger Phil Thompson said...

Hi Joeseph,

You appear to be trying to use the static values 'GA' and 'STG Solution Stack Viewers' in the dynamic function CSVIdentity namelist. If these are valid user groups in the namespace you should set up a new query item (UserGroup) in your query subject that defines the user group:

UserGroup:
IF (Metadata Layer].[Applications].[PIPELINE_GA] = 'GA' ) THEN ('GA') ELSE ('STG Solution Stack Trusted Viewers')

Your filter would then be:
[UserGroup] in (#CSVIdentityNameList()# )

I hope I have understood the problem you faced in a way that helps.

best regards

Phil

 
At 7 November 2009 at 21:52 , Blogger Unknown said...

Hi Phil

Great article.

Question:

I would like to burst based on a hierarchy. Example:

VP 1
Director 1
Dept 1
Dept 2
Dept 3
Director 2
Dept 1
Dept 2
Dept 3
VP 2
Director 1
Dept 1
Dept 2
Dept 3
Director 2
Dept 1
Dept 2
Dept 3

My cognos security rollup is identical to the hierarchy which is in a table.

How would I setup the Busrt table to accomplish bursting considering this rollup structure?

Thanks very much,
Andy.

 
At 9 November 2009 at 08:16 , Blogger Phil Thompson said...

Hi Andy,

to do this you need Report Studio to produce a list in the same structure that you listed in your question (or by running a burst at each level). By the look of your list you have three columns in your source. You need these all in one column. You can do this by creating a union of the columns:
select
VIP
from table
group by vip
union
select
director
from table
group by director
union
select
dept
from table
group by director

The union can be done in the report using three queries, as a sql query or as a query subject in Framework Manager using a query set.

best regards

Phil

 
At 9 November 2009 at 08:20 , Blogger Phil Thompson said...

Sorry - SQL should read:

select
VIP
from table
group by vip
union
select
director
from table
group by director
union
select
dept
from table
group by dept

 
At 11 November 2009 at 17:28 , Blogger Unknown said...

Thanks for your help!

Andy.

 
At 12 January 2013 at 12:49 , Blogger Unknown said...

This comment has been removed by the author.

 
At 12 January 2013 at 12:50 , Blogger Unknown said...

Superb, brilliant weblog structure! I like your blog post Dynamic Security in ReportNet & Cognos8 and method of writing,

ukdissertation.co.uk

 
At 1 July 2016 at 15:06 , Blogger Unknown said...

Secure data sharing makes it possible for IT to provide the anywhere, any-device data access and collaboration people need, while meeting the organization’s requirements for security, manageability, and compliance.
data room services

 
At 21 March 2017 at 07:26 , Blogger zion said...

The security filter will become active once the package the query subject belongs to is published. At runtime when any users with the applicable user class uses a report based on the query subject the query results will be filtered using the filter definition.
Outsourced investment research

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home