​I have a fact table that has an institution key and source institution key.  The institution key needs security and the source institution key doesn't.

I have applied security for the table that is joined to the institution key.  If I add another copy of the institution table is the security that was applied going to apply to it also?  I am kind thinking it will.

One way to get around this that I can think of is to not join the source institution key to another institution table.  But then I can't get to information like the institution name or institution type. 

In Framework manager, make 2 queries of the database table and apply security to one and not the other.

Only other way would be to create a view in the database call source institution that is based on Institution and not apply the security there.

Any other ideas?


------------------------------
Jo Marie Sellner
------------------------------

#CognosAnalyticswithWatson

Does the Institution key need row level security or can you define more details on what the rules are?

------------------------------
NORBERT Bracke
------------------------------

I am trying to take an overall approach to this because we be adding additional data module subject areas and I would like to be able to reuse the security.   I created a core set of tables into a common data modules that others then can just add to other subject data modules without having to recreate plus it will make it easier to maintain.  We need to be able to separate the subject areas into separate data modules to security them to different user functional area.  The separate data modules then allows us to apply security to the over data module instead of having to figure out in one big data module which tables need to be secured inside the module to what security.

Currently the subject area data module has 2 facts,

• first has an institution_key and is joined to the Institution_key of the Dim_institution. 
• second has an institution_key but it also has a source_institution_key.  I have joined the institution_key to the institution_key on the Dim_istitution.  So I am trying to figure out how to handle the Source_institution_key

I applied the security to Dim_institution because I can see the Institution field value. 

I did see there is a an expression editor but I can't see how I could use the Institution_key from the fact to lookup the value in Dim_institution so I can set it against the fact instead.  Even if I could that would mean each new fct added whether to this data module or another subject data module would have to security setup again.  Instead of once and forget it until we add a new institution.

I attached how screen shot of the security I setup on the dim.


------------------------------
Jo Marie Sellner
------------------------------

Original Message:
Sent: Fri August 23, 2019 08:10 AM
From: NORBERT Bracke
Subject: Data Module question about table that needs security and doesn't

Does the Institution key need row level security or can you define more details on what the rules are?

------------------------------
NORBERT Bracke
------------------------------

First thing you should realize is that security filters are saved with the data source not the data module so they would be recognized for any data module that references that table.
Also security is still being developed to make it easier to implement and what you are looking for may be in a future release but I can not confirm that specifically as the features are not published.
You are correct in that a filter can not reference a column from a different table, perhaps you need to create a view for that purpose. A DM view would work as well as database views.

------------------------------
NORBERT Bracke
------------------------------

Original Message:
Sent: Fri August 23, 2019 10:45 AM
From: Jo Marie Sellner
Subject: Data Module question about table that needs security and doesn't

I am trying to take an overall approach to this because we be adding additional data module subject areas and I would like to be able to reuse the security.   I created a core set of tables into a common data modules that others then can just add to other subject data modules without having to recreate plus it will make it easier to maintain.  We need to be able to separate the subject areas into separate data modules to security them to different user functional area.  The separate data modules then allows us to apply security to the over data module instead of having to figure out in one big data module which tables need to be secured inside the module to what security.

Currently the subject area data module has 2 facts,

• first has an institution_key and is joined to the Institution_key of the Dim_institution. 
• second has an institution_key but it also has a source_institution_key.  I have joined the institution_key to the institution_key on the Dim_istitution.  So I am trying to figure out how to handle the Source_institution_key

I applied the security to Dim_institution because I can see the Institution field value. 

I did see there is a an expression editor but I can't see how I could use the Institution_key from the fact to lookup the value in Dim_institution so I can set it against the fact instead.  Even if I could that would mean each new fct added whether to this data module or another subject data module would have to security setup again.  Instead of once and forget it until we add a new institution.

I attached how screen shot of the security I setup on the dim.


------------------------------
Jo Marie Sellner
------------------------------


Original Message:
Sent: Fri August 23, 2019 08:10 AM
From: NORBERT Bracke
Subject: Data Module question about table that needs security and doesn't

Does the Institution key need row level security or can you define more details on what the rules are?

------------------------------
NORBERT Bracke

​Yes, I know the security is applied at the data server and once applied it comes along any time you include the file.  Based on that, if I create a view based on the table won't the security also be applied to that view?

------------------------------
Jo Marie Sellner
------------------------------

Original Message:
Sent: Fri August 23, 2019 01:07 PM
From: NORBERT Bracke
Subject: Data Module question about table that needs security and doesn't

First thing you should realize is that security filters are saved with the data source not the data module so they would be recognized for any data module that references that table.
Also security is still being developed to make it easier to implement and what you are looking for may be in a future release but I can not confirm that specifically as the features are not published.
You are correct in that a filter can not reference a column from a different table, perhaps you need to create a view for that purpose. A DM view would work as well as database views.

------------------------------
NORBERT Bracke
------------------------------


Original Message:
Sent: Fri August 23, 2019 10:45 AM
From: Jo Marie Sellner
Subject: Data Module question about table that needs security and doesn't

I am trying to take an overall approach to this because we be adding additional data module subject areas and I would like to be able to reuse the security.   I created a core set of tables into a common data modules that others then can just add to other subject data modules without having to recreate plus it will make it easier to maintain.  We need to be able to separate the subject areas into separate data modules to security them to different user functional area.  The separate data modules then allows us to apply security to the over data module instead of having to figure out in one big data module which tables need to be secured inside the module to what security.

Currently the subject area data module has 2 facts,

• first has an institution_key and is joined to the Institution_key of the Dim_institution. 
• second has an institution_key but it also has a source_institution_key.  I have joined the institution_key to the institution_key on the Dim_istitution.  So I am trying to figure out how to handle the Source_institution_key

I applied the security to Dim_institution because I can see the Institution field value. 

I did see there is a an expression editor but I can't see how I could use the Institution_key from the fact to lookup the value in Dim_institution so I can set it against the fact instead.  Even if I could that would mean each new fct added whether to this data module or another subject data module would have to security setup again.  Instead of once and forget it until we add a new institution.

I attached how screen shot of the security I setup on the dim.


------------------------------
Jo Marie Sellner


Original Message:
Sent: Fri August 23, 2019 08:10 AM
From: NORBERT Bracke
Subject: Data Module question about table that needs security and doesn't

Does the Institution key need row level security or can you define more details on what the rules are?

------------------------------
NORBERT Bracke

------Original Message------

​I have a fact table that has an institution key and source institution key.  The institution key needs security and the source institution key doesn't.

I have applied security for the table that is joined to the institution key.  If I add another copy of the institution table is the security that was applied going to apply to it also?  I am kind thinking it will.

One way to get around this that I can think of is to not join the source institution key to another institution table.  But then I can't get to information like the institution name or institution type. 

In Framework manager, make 2 queries of the database table and apply security to one and not the other.

Only other way would be to create a view in the database call source institution that is based on Institution and not apply the security there.

Any other ideas?


------------------------------
Jo Marie Sellner
------------------------------

#CognosAnalyticswithWatson

Hi Jo Marie,
Security is applied to the Data Source, thus when you create a security filter against this table it will always be applied. That is for direct references as well as views, joined views etc.
If a table needs to be used with both security turned on as well as not secured, then creating a view in the database is a good solution. Then secure only one of the two table objects in the Data Source using the Web-based modeling tool.

When you create a security filter it is possible to reference another table (from CA 11.1.3 and up)
For example securing the ORDER_DETAILS table with a filter :
ORDER_NUMBER IN (CASE WHEN ORDER_HEADER.ORDER_METHOD_CODE = 2 THEN ORDER_HEADER.ORDER_NUMBER END)
//Henk


------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Fri August 23, 2019 02:16 AM
From: Dmitriy Zats
Subject: Data Module question about table that needs security and doesn't

You are correct. Aliases don't carry over security from the parent objects. What security method have you implemented? Parameter Maps?

Dmitriy

Original Message------

​I have a fact table that has an institution key and source institution key.  The institution key needs security and the source institution key doesn't.

I have applied security for the table that is joined to the institution key.  If I add another copy of the institution table is the security that was applied going to apply to it also?  I am kind thinking it will.

One way to get around this that I can think of is to not join the source institution key to another institution table.  But then I can't get to information like the institution name or institution type. 

In Framework manager, make 2 queries of the database table and apply security to one and not the other.

Only other way would be to create a view in the database call source institution that is based on Institution and not apply the security there.

Any other ideas?


------------------------------
Jo Marie Sellner
------------------------------

#CognosAnalyticswithWatson