• SecurID Governance & Lifecycle 7.5.2 P07 (and later)

SQL/line#: /home/oracle/wildfly-24.0.1.Final/domain/servers/img-server-1/tmp/vfs/deployment/deployment99a9ca9d5e1f36c2/aveksa.war-13593c541ef9436/WEB-INF/database/migration/createReportViews.sql(1):
Start time [Wed Jun 07 19:51:53 EDT 2023]
begin Report_Views_Pkg.create_av_report_views; end;
java.sql.SQLException: ORA-04063: package body "AVUSER.UTILITIES_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.UTILITIES_PKG"
ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 708
ORA-06512: at line 1

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    ...
Caused by: Error : 4063, Position : 0, Sql = begin Report_Views_Pkg.create_av_report_views; end;, OriginalSql = begin Report_Views_Pkg.create_av_report_views; end;, 
Error Msg = ORA-04063: package body "AVUSER.UTILITIES_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "AVUSER.UTILITIES_PKG"
ORA-06512: at "AVUSER.REPORT_VIEWS_PKG", line 708
ORA-06512: at line 1

This is a known issue with some database deployment types where additional grants need to be provided for some RSA internal tables to facilitate feature enhancements in 7.5.2 P07.

This is a known issue in the following type of RSA Governance & Lifecycle deployment:

• Customer-Supplied Remote Oracle Database

This issue is not known to occur in the following types of RSA Governance & Lifecycle deployments:

• RSA-Provided Local Oracle Database
• RSA-Provided Remote Oracle Database
• RSA GL Cloud
• Customer-Supplied AWS RDS Oracle Database (where the "SecurID Governance & Lifecycle 7.5.2 AWS Installation and Clustering Guide" has been followed)
• Customer-Supplied Remote Oracle Databases installed configured as per the updated Database Setup and Management guide from 2023.

For Customer-supplied Oracle Databases, grants are normally applied manually during creation of the database using guidance in the G&L 7.5.2 Database Setup and Management Guide.  G&L 7.5.2 product documentation will be updated to reflect the additional grants required for 7.5.2 P07.

If you have installed or upgraded to 7.5.2 using the original product guides and when you patch to 7.5.2 P07 (or later), you are required to manually update the AVUSER schema and apply the additional grants.  This is a one time operation on the database.  The steps provided below to update the AVUSER schema can be followed PRIOR to applying/upgrading to patch P07, or AFTER you have encountered the error.

Update the AVUSER schema:

1.  Login as SYS user (or another user with SYSDBA privilege) in SQLPLUS (or another database tool like SQL Developer) and run the below script to grant Permissions on the below objects to AVUSER.

NOTE: If the schema name is other than the AVUSER, replace the value for v_username with the appropriate SCHEMA name.

DECLARE
v_username varchar2(100):= 'AVUSER';
BEGIN 
-- Create neede grants for 7.5.2P07
EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO ' || v_username || '';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.DBMS_LOCK TO ' || v_username || '';
-- Recompile the Utilities Package
EXECUTE IMMEDIATE 'ALTER PACKAGE AVUSER.Utilities_Pkg COMPILE PACKAGE';
EXECUTE IMMEDIATE 'ALTER PACKAGE AVUSER.Utilities_Pkg COMPILE BODY';
END;
/ 

SELECT owner, table_name FROM table_privileges WHERE grantee = 'AVUSER' and table_name in ('DBA_HIST_DATABASE_INSTANCE', 'DBA_HIST_SNAPSHOT','DBMS_WORKLOAD_REPOSITORY','DBMS_LOCK') ORDER BY owner, table_name;

select * from all_objects where owner='AVUSER' and status<>'VALID' and object_name ='UTILITIES_PKG';

4.  If you have not applied the patch yet you may now apply 7.5.2 P07 (or later).  If you attempted to apply the patch and it failed with the error, apply the patch 7.5.2 P07 (or later) again using the original procedure.

If you suspect you have encountered this issue or are susceptible to it, the following scripts can be run to validate your system requires remediation to successfully apply the patch P07 on your SecurID Governance & Lifecycle 7.5.2.

Run the following select queries as SYS user (or another Oracle user with SYSDBA privilege) in SQLPLUS (or another database tool like SQL Developer):

NOTE:  If your SecurID Governance & Lifecyle database schema is configured with a user other than the default AVUSER, please replace 'AVUSER' in the command with the appropriate user.
 

SELECT owner, table_name FROM table_privileges WHERE grantee = 'AVUSER' and table_name in ('DBA_HIST_DATABASE_INSTANCE', 'DBA_HIST_SNAPSHOT','DBMS_WORKLOAD_REPOSITORY','DBMS_LOCK') ORDER BY owner, table_name;

• If the result for the above command matches with the output below, your SecurID Governance & Lifecycle deployment is NOT affected by the issue in this article.

• If the result for the above command is empty or does not match with the above good output, your SecurID Governance & Lifecycle deployment is AFFECTED by this issue.
• In addition to occurring during patching, since this is a problem with the schema of the database, this issue also can occur in certain types of upgrade scenarios that bypass the installer scripts.  In each case the issue may be resolved by applying the work around to the problem 7.5.2 instance before upgrading.
  • WebLogic and WebSphere installations.
  • Upgrade by installing 8.0.0 version and referencing 7.5.2 database with this issue.