User Defined SQL capability - Why or why not give it to users

[abarlas]

Why would you give user defined sql to users, why not.  By defaults Cognos does not give it to any users other than admin.  What's the impact of giving it to users?  There must be some good reasons.

[MFGF]

If the modeller has implemented data security in the Framework Manager model, this security manifests itself as predicates (where clauses) in the SQL generated by reports when they are authored. If you grant your authors the User Defined SQL capability, they could then go in and override the SQL predicate, and see data they are supposed to be prohibited from seeing.

Cheers!

MF.

[MMcBride]

Not - Most environments are setup with a Read only system account for Cognos, however some use a pre-defined system account that can have much more access than intended. Giving the users the ability to create their own SQL statement you cannot limit said statement to a Select only if you have given them more access.
This should be common sense, but My company just bought a large Claims System to replace our existing 7 different claims systems, With the claims system we purchased a pre-build Cognos Reporting model and reports, we also paid for the company to modify the reports to suit our company needs. The user ID used to create the Cognos connections was their DBAdm account with full drop, create on all systems. Giving someone access to this environment with SQL create priviledges could have wrecked havoc - We had some problems convincing this consulting company to stop using the DBADM account for everything and to have read only ID's set up for Cognos...

To - Special case joins, multipurpose fields. We can't model every scenerio feasible when the users are using a multipurpose field. Allowing them the ability to create their own SQL gives them the ability to leverage these fields in many different ways without having to redesign the model every time. Not a great reason but a real one to allow free hand SQL inside of Cognos.


Usually when a user asks for User Defined SQL it is simply because they don't know what they want and have not done the analysis to provide the FM Modeller enough information to build a good model.
I am of the opinion that I don't give out out Free Hand SQL access unless the user can prove to me the value, there are dozens of SQL Query tools on the market, one usually comes with the tool (Control Center or Data Studio for DB2, SQL Server Management Studio for SQL Server etc. etc.) I usually recommend the users go use one of these instead of risking the security and maintainability within my Cognos Environment.

Call it cinicism but most users who are requesting access to this are either to lazy layout the full requirements for the data properly to build a truely effective model or simply can't get access to another tool (usually because the DBA's say no) and getting the access in Cognos is their way around the "system"