I need help from an expert in 'Data Security'

[mgibson]

Hi,

We are creating a Framework Manager model that we wish to apply row level security to - which Cognos refers to as 'Data Security'.

But I'm looking for some advice for how to deal with 2 specific difficulties we have identified.

1. The instructions for setting up Data Security state that all applicable users need to be placed within individual groups, and that filters are applied to these groups. My concern is that if a user is mistakenly given access to this package, but is not placed within one of these groups, then no filters are applied, and they have unrestricted access to the data. Can anyone recommend a method of dealing with this risk? Is there a way we can ensure people can see nothing unless they are placed in a group, rather than everything?

2. The Data security is applied to individual Query Subjects, so if a query is created from any of the other Query Subjects, then the filter is not applied - until something is selected from that Query Subject. Can the data Security be applied to the whole package to ensure the filters are applied regardless of what Query Subject they select? If not, is the only option to place these filters on all Query Subjects? How would you suggest we handle this?

Your help is much appreciated.

Regards
Mick

[JGirl]

Hi,

Answers for your questions.

1.  Why not create a dummy filter for something that will never exist in the data, eg. [Account Code] = 'DummyValue never to be found in data set', and then apply this to a generic group that all users would belong to, eg, Everyone or Consumers.  This way everyone gets at least one filter which should return nothing if no other user class/filter match is found.

2.  I've had this problem myself.  In most cases the security structure is based on one of your conformed dimensions (eg. DIM_A table) and you need this to be applied to each of the related fact tables (eg.FACT_B table) .  The way around this is to create a 'security join' between your dim & your fact (eg. DIM_A.key = FACT_B.key) early in your project.  Then in the next layer of your project in your fact table (the one you want secured by the dimension ie. secure Fact B by Dim A) bring in the key from the secured dimension rather than the fact (so your Fact B definition has DIM_A.key instead of FACT_B.key).  Then change the SQL settings (done from the options tab from memory) of the fact query subject to use 'as view' SQL (forces the join) rather than 'Minimised' (only uses what the query requires).

Hope this helps.

J

[mgibson]

Thanks JGirl,

I have a couple of followup questions for you.

1. Wouldn't adding a dummy filter that returns nothing also mean that nothing will be returned when and if the second filter is applied?  I would assume the 2 separate filters would be combined via an 'AND' operator, thereby returning the same result?

2. I suppose this would work if I was only concerned with the fact table, but I'd like the security to be applied to all of the dimensions and the fact, so that if the user were to select data just from one of the dimensions, then the data is filtered also.

I know I could do this by applying the filter to all Query Subjects (Dims and Fact), but it seems terribly inefficient.

Any further thoughts would be greatly appreciated.

Regards
Mick

[JGirl]

Hi,

1.  I think the security filters are applied using an in() statement, or OR logic.  Why? Because if a user belongs to more than one group (so needs 2 filters applied), it wouldnt make sense to filter (dataitem = ABC AND dataitem = CDE) as this would return nothing from the database. 

If you have the dummy filter and others (eg. you belong to 'Everyone', 'Northen Region' and 'Southern Region' groups, then it filters to look for the dummy value, 'Northern Region' and 'Southern Region' and since the dummy value returns nothing, you will only get the 'Northern Region' and 'Southern Region' results.  If you only have the dummy filter, you get nothing since the value doesnt exist in the data.  Last time I tested it this approach worked for me in FM 8.2.

2.  You need to pre-join all the tables you want to secure to the secured table first (so if DIM_A is your secured one, and you want to use it to secure FACT_B, DIM_C, DIM_D, DIM_E etc, you'd need to join your secured DIM_A to FACT_B, DIM_A to DIM_C, DIM_A to DIM_D and DIM_A to DIM_E in one 'layer' of your model, then do the rest of the steps from the point in my earlier post (ie. bring in the column from the secured table in the next layer and then change the SQL setting).

Hope this helps.
J

[mgibson]

Hi JGirl,

I've worked through the first point and it seems to work very well.  I've currently got a case in with Cognos regarding this, and it will be interesting to see what advice they will give me.  I'm half expecting them to tell me it's not possible - when you've clearly demonstrated that it is.

On the second point, because we are dealing with HR data, I'm wishing to apply the security to most dimensions.  As Data Security allows you to utilise Model Filters, I will create a single one of those and apply it to all the relevant Query Subjects, and see how that goes.

Thanks for your help.
Mick

[larsonr]

When you say row level security, Cognos does not have a very efficient way to handle this.  Really you need to build it into your database in such a way that by passing their username they can only get the records that are associated with them.  Cognos is very good at handling package, and column level security by using their roles to basically do a show or hide all on content.

I would think about putting your data into the database to be handled by row level security.  Create a matrix table of usernames and keys that filter your data, pass the session parameter to them in framework and you're on your way.

We did this in several applications at my old environment, and they are doing the same thing at my new environment.  It takes the risks out of accidental access.  It does mean applying the filter to all tables that need it, but that's the price you pay when you need security to be a top priority. 

Keep us posted on the solutions you work with.

[mgibson]

Thanks Larsonr,

we've actually come to this conclusion ourselves - even the advice from Cognos was to NOT implement row-level security using Cognos, but use the DB.  It has too many flaws, and it's easy for some users to get around.

Which begs the question - Why did they bother?!?!?!?!?!?!?!  The Account manager and Pre-Sales guy were misleading with their information when we were evaluating Cognos 3 years ago.

We've successfully tested a mechanism where we use the security info recorded in the application DB.

Thanks!

[Lee Drake]

I am trying to implement this.  When I set the query subjects to "As View", I get crossjoin errors when I select objects from 2 different ones in one query.  With the exception of some lookup tables, security needs to be applied to all tables in the database, facts and dimensions.  Any other methods or hints are welcome.