Clarity Oracle user CONTEXT permissions starting 16.3.3

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

After running the checkinstall in the precheck-results.html we seen that the DB User should have the grants of CREATE ANY CONTEXT and DROP ANY CONTEXT privileges. What is the requirement for this? 

Environment

• Clarity 16.3.3 and higher
• Clarity 16.2.3 (Patch 5)
• Oracle Only

Cause

New requirement for Oracle databases to allow for the Clarity user to use additional permissions

• In Oracle, the permissions have to be granted explicitly.
• In PostgreSQL and MSSQL Clarity also supports, the same functionality change was applied and did not require additional permissions due to how the database vendors designed the specific permissions grants

Resolution

• Starting 16.3.3, Clarity is now requiring the below permissions to be granted by DBA:

• • Connect to the Oracle database with sys as SYSDBA and execute the following SQL statement:
    • GRANT CREATE ANY CONTEXT, DROP ANY CONTEXT TO <Clarity_database_user>;
  • Still connected as SYSDBA, execute the following SQL statement:
    • GRANT EXECUTE ON SYS.DBMS_SESSION TO <Clarity_database_user>;

• The permissions are needed to address performance issues with sessions and audited attributes, where Clarity is relying on triggers instead of using context, example case XOG integration is hanging when high volume of CMN_SESSIONS Impersonation query runs
  • This would affect customers leveraging audited attributes by adding overhead to the queries
• The functionality is created to improve database performance with regards to sessions use, and may also be leveraged at a later time in other new improvements where a database trigger is used for audits
• Note these are required permissions, prerequisite for Clarity PPM and they cannot be revoked after upgrade or at a later time

Additional Information

This is documented in Broadcom Documentation - Change and Impact Guide