How is data-driven security done properly?

[hespora]

Hi there,


first off: This is more of an academic interest for me. I am merely a report author, I do not have access to framework manager or any other admin tools.
Now, what is happening in my org is that we employ data-driven security. As in, access to specific parts of a package are handled by AD groups, but in addition, once in a particular namespace, User A should only see a specific market, User B should only see specific customers, User C should only see specific products, and so forth. I do not know how the technology behind that works, but I can observe that it does. So far so good.

What I was able to observe is that this solution is done on the database, fully transparent to the user. i.e., when I create any report and look at the generated SQL, there's a join in there with a where statement: "WHERE 'SEC_MV'.'USERID' = '[my username]'". Now what that means is our data-driven security can easily be circumvented by anyone with access to report studio - just convert the query to an SQL object, then edit that SQL object to use a different user name, and voila, I can see the results a different user should see. Drat.

Why I'm asking this is I've reported this to our Cognos admins, and their reaction was "well, we're just gonna remove access to SQL objects" - every now and again, I do need to use manually created SQL objects in my reports, and so going this route seems like overkill to me, but I know too little about cognos security to argue against it. What I'm looking for is basically "if you were to implement security in this or that way instead, SQL objects are not an issue and can be left alone for anyone to use."

[MFGF]

Hi there,


first off: This is more of an academic interest for me. I am merely a report author, I do not have access to framework manager or any other admin tools.
Now, what is happening in my org is that we employ data-driven security. As in, access to specific parts of a package are handled by AD groups, but in addition, once in a particular namespace, User A should only see a specific market, User B should only see specific customers, User C should only see specific products, and so forth. I do not know how the technology behind that works, but I can observe that it does. So far so good.

What I was able to observe is that this solution is done on the database, fully transparent to the user. i.e., when I create any report and look at the generated SQL, there's a join in there with a where statement: "WHERE 'SEC_MV'.'USERID' = '[my username]'". Now what that means is our data-driven security can easily be circumvented by anyone with access to report studio - just convert the query to an SQL object, then edit that SQL object to use a different user name, and voila, I can see the results a different user should see. Drat.

Why I'm asking this is I've reported this to our Cognos admins, and their reaction was "well, we're just gonna remove access to SQL objects" - every now and again, I do need to use manually created SQL objects in my reports, and so going this route seems like overkill to me, but I know too little about cognos security to argue against it. What I'm looking for is basically "if you were to implement security in this or that way instead, SQL objects are not an issue and can be left alone for anyone to use."

Hi,

For data security in FM, you define user/group/role based filters and apply these to query subjects, so that when the user/group/role accesses data via that query subject, the filter is applied. This manifests itself as a WHERE clause in the generated SQL - exactly as you are seeing.

The obvious loophole here is that any user who has access to SQL in Report Studio can easily circumvent the filters that are added - again, just as you observe. For this reason, the out-of-the-box capability settings for Cognos deny access to SQL for all but Administrator roles.

One of the things that a security audit often checks is whether the defined security can be bypassed, and in your case it can. I'm actually very surprised that SQL access has been granted at all, as you are using security filters in FM. I heard a few years ago someone got fired for doing that :)

The options as I see it to close the loophole are either:

1. Remove SQL access in Report Studio, or
2. Move the security filters from FM to the database, based on database login, and have Cognos connect to the database using the user's individual database signon rather than a generic database signon for all.

Of the above, I'm pretty sure your admins will go with option 1, as this is the quick, easy solution for them. Option 2 involves a major redesign of your database or databases to implement.

Cheers!

MF.

[hespora]

Hi MF,


sadly, that is entirely not the answer I was looking for, but if that's the way it is, then so be it. Thank you very much!
All I can hope for then is for us to quickly move to C11, as data modules are a better solution to achieve what I'm currently doing via manual SQL (managing external data is not enabled on our C10 platform).