How to author Report Studio reports without write access to User Defined SQL

[vickiAtCognoise]

Hello,

To fulfill security requirements a group of users need to use Report Studio as follows:
- Author RS reports without the ability to include User Defined SQL
- Run existing RS reports which may or may not contain User Defined SQL

Can you please tell us how to configure Cognos to achieve this?
We have setup another group which meets the second criteria only, the first one is proving to be more challenging.

Thank-you,
Vicki

[bdbits]

I think what you want is in Cognos Administration under Capabilities. Click Report Studio to show the "sub-capabilities". User-defined SQL is one of them, click the dropdown and choose "Set Properties". Switch to the permissions tab. I do not have time to test this right now, so you may have to experiment a bit. I believe "Execute" will let you run the SQL and "Write" will let you save it in a report. Not sure if you'd need Read and/or Traverse.

[vickiAtCognoise]

Hi all,

For those of you who are interested, here is how I've gotten this to work.

This involves what is in essence a custom role used in conjunction with report Run as the Owner, Capabilities Only.
Rather than creating a custom role, I've taken an existing role and added limited Report Studio to it.
In practice I'll do this with a new custom role, i.e. from scratch, rather than changing an existing one to avoid confusion with licencing.

Vicki



1 Make Cognos group a Member of the Cognos Licensing Role: Cognos role BI Advanced Business Author
      -Licencing Role is Member of base Cognos Roles:  Query Users, Analysis Users
      - Neither base role includes Predefined Capabilities for Report Studio or Report Studio UDS

2 Administration, Security, User Interface Profiles, Set Properties (upper right hand icon)
Add Cognos Group as Member with Grant of Traverse

3 Administration, Security, User Interface Profiles, Professional, Set Properties
Add Cognos Group as Member with Grant of Execute and Traverse

4 Administration, Security, Capabilites, Report Studio, Set Properties 
Add Cognos Group as member with Grant of Execute and Traverse

Cumulative effect of changes:
Provides first and second desired Report Studio characteristics:
Can launch Report Studio and author and save a report without UDS
When attempting to include an SQL query while editing a report you get the error `You do not have permission to create or edit SQL/MDX.``
      - A save of such a report does not retain the SQL object
Does NOT provide third desired Report Studio characteristic:
When trying to run a previously saved report with UDS you get the error ``DPR-ERR-2082 An error has occurred. Please contact your administrator.
"The complete error has been loggged by CAF with SecurieErorID:2014-02-14-13:19:33.289-/4"

Note that further testing where combinations of Permissions for the Report Studio User Defined SQL capability were added to the cumulative changes did not improve on desired results but lessened them.
This was primarily because UDS Execute permission is required as part of the scenario to enable running of previously saved reports containing UDS.
The Execute permission implicitly grants the Write permission which enables the inclusion and editing of UDS.
These changes were reversed and are not part of the final cumulative ones


5 Working as own user who already has full Report Studio access  (BI Administrator licence role, base role System Administrator) :
My Preferences, Personal, Credentials, add Cognos group
Authored and saved a report containing UDS (making self the report Owner by default)
Set the report Properties, Report, Run as the owner and Capabilities only checked
Cognos group member can now run the report

Cumulative effect of changes:
Provides all three desired Report Studio characteristics:
Can launch Report Studio and author and save a report without UDS
When attempting to include an SQL query while editing a report you get the error `You do not have permission to create or edit SQL/MDX.``
      - A save of such a report does not retain the SQL object
Can run the report containing UDS