Hi Kiran,
> Cognos behaves in a weird fashion when same user is part of different roles / groups with different capabilities.
If the same user is in two Roles with overlapping rights, the DENY always wins.
Is that what you find weird?
>In the solution you explained, will the author be still able to open/edit previously built report (may be by another author) that have user defined sql in it?
Run: yes
Edit: yes ... except the UDS part - which is what we wanted to achieve.
This is why we cloned "Role 1" and put "Deny UDS" on the package on that "Role 2", remove user from "Role 1" and add them to "Role 2". You might not want to do this manually, as it involves a lot of back and forth. BUT, if you have an automation in place, this is easy to handle and straight forward imho.
Results in:
* User can execute reports with UDS
* User can edit reports and get's error msg, when trying to edit a UDS (on a "secured package")
The err-msg is not nice ... better would be to not show the SQL icon right away. But that's the way it is.
Until last week, I did not know that packages can have Capabilities defined.
Always thought it's only the functions/studios and so, that can have capabilities defined.
To illustrate that a bit more here is part of the XML config that is pushed into the Cognos installation (using
CoCoMa)[...]
<permission>
<target>/content/folder[@name='_Templates_']/package[@name='PACKAGE_WITHOUT_ACCESS_TO_UDS']</target>
<read>true</read>
<write>true</write>
<execute>true</execute>
<setPolicy>false</setPolicy>
<traverse>true</traverse>
<members>
<role>Role1</role>
<role>Role2</role>
</members>
<policies>
<!--
Adaptive Analytics => canUseAdaptiveAnalytics
Administration
┣ Adaptive Analytics Administration => canUseAdaptiveAnalyticsAdministration
┣ Metric Studio Administration => canUseMetricsManagerAdministration
┗ Planning Administration => canUsePlanningAdministration
Allow generation of reports in CSV format. => canGenerateCSVOutput
Allow generation of reports in PDF format. => canGeneratePDFOutput
Allow generation of reports in Excel format. => canGenerateXLSOutput
Allow generation of reports in XML format. => canGenerateXMLOutput
Analysis Studio => canUseAnalysisStudio
EVStudio => canUseEV
Event Studio => canUseEventStudio
Glossary => canUseGlossary
Lineage => canUseLineage
Metric Studio => canUseMetricStudio
┗ Edit View => canUseMetricStudioEditView
Planning Contributor => canUsePlanningContributor
PowerPlay Studio => canUsePowerPlay
Query Studio => canUseQueryStudio
┗ Advanced => canUseQueryStudioAdvancedMode
Report Studio => canUseReportStudio
┣ Allow External Data => canUseExternalData
┣ Bursting => canUseBursting
┣ HTML Items in Report => canUseHTML
┗ User Defined SQL => canUseUserDefinedSQL
Specification Execution => canUseSpecifications
Watch Rules => canUseConditionalSubscriptions
-->
<policy>
<group>Everyone</group>
<capabilities>
<capability access="grant">canUseAdaptiveAnalytics</capability>
<capability access="grant">canUseAdaptiveAnalyticsAdministration</capability>
<capability access="grant">canUseMetricsManagerAdministration</capability>
<capability access="grant">canUsePlanningAdministration</capability>
<capability access="grant">canUseAnalysisStudio</capability>
<capability access="grant">canUseEV</capability>
<capability access="grant">canUseEventStudio</capability>
<capability access="grant">canUseGlossary</capability>
<capability access="grant">canUseLineage</capability>
<capability access="grant">canUseMetricStudio</capability>
<capability access="grant">canUseMetricStudioEditView</capability>
<capability access="grant">canUsePlanningContributor</capability>
<capability access="grant">canUsePowerPlay</capability>
<capability access="grant">canUseQueryStudio</capability>
<capability access="grant">canUseQueryStudioAdvancedMode</capability>
<capability access="grant">canUseReportStudio</capability>
<capability access="grant">canUseBursting</capability>
<capability access="grant">canUseHTML</capability>
<capability access="grant">canUseUserDefinedSQL</capability>
<capability access="grant">canUseSpecifications</capability>
<capability access="grant">canUseConditionalSubscriptions</capability>
</capabilities>
</policy>
<policy>
<role>Role2</role>
<capabilities>
<capability access="deny">canUseUserDefinedSQL</capability>
</capabilities>
</policy>
</policies>
</permission>
Edit: 09.05.2022 - after further testing
* We deny access to /content for users, which makes it impossible for them to see packages in /
* We then enable access on per package basis
* For Report Authors, we copy package to a visible folder and set UDS=DENY on each package
* Results in two packages: 1 - invisible, but executable with UDS ... 2 - visible and usable for ReportStudio but without UDS
Drawback:
* If a user knows the path of a "package with UDS allowed", they can edit XML-specs of a report to use that package and ... here you go, now they are able to use UDS without further restrictions
Hope that helps.
------------------------------
Ralf Roeber
https://linkedin.com/in/ralf-roeber/------------------------------
#CognosAnalyticswithWatson