When upgrading Oracle REST Data Services (ORDS) to version 23.1 or later, it completes with warnings as following
WARNING: Procedure APEX_ADMIN could not be added to allow list. The function or procedure is a common object. You must execute the following as a common user: begin APEX_240100.wwv_flow_listener.sync_ords_gateway_allow_list; end; WARNING: ORDS migrated 0 entry points from APEX to ORDS PL/SQL Procedure Gateway Allow List.
Now accessing APEX will return 404 error as following
Not Found HTTP Status Code: 404 Request ID: EHFC-h-DNoA9A0Qo-_W Request Timestamp: 2024-12-17T13:20:38.684973Z Error Code: ORDS-2201 The procedure named apex could not be accessed, it may not be declared, or the user
executing this request may not have been granted execute privilege on the procedure, or a
function specified by security.requestValidationFunction configuration property has prevented
access. Check the spelling of the procedure, check that the execute privilege has been
granted to the caller and check the configured security.requestValidationFunction function. If
using the PL/SQL Gateway Procedure Allow List, check that the procedure has been allowed
via ords_admin.add_plsql_gateway_procedure.
CAUSE
Since pluggable database was introduced in Oracle database from 12.1, Oracle supplied users (such as SYS, SYSTEM, DBSNMP, etc) are set to YES for new colume COMMON (added from 12.1) of view DBA_USERS no matter whether the database is CDB or non-CDB.
However, if the database was upgraded from 11g or earlier to 12.1, the upgrade may not set COMMON properly because of bug
25117045 - 12C UPGRADE DOES NOT SET THE COMMON-USER FLAG FOR ORACLE-SUPPLIED USERS
For example, I have a database which upgrade history as following
SQL> select version,comments from dba_registry_history where action='UPGRADE' order by action_time; VERSION COMMENTS _____________ _________________________________________ 10.2.0.3.0 Upgraded from 9.2.0.6.0 10.2.0.4.0 Upgraded from 10.2.0.3.0 10.2.0.5.0 Upgraded from 10.2.0.4.0 11.2.0.3.0 Upgraded from 10.2.0.5.0 12.1.0.2.0 Upgraded from 11.2.0.3.0 12.2.0.1.0 Upgraded from 12.1.0.2.0 12.2.0.1.0 Upgraded from 12.1.0.2.0 19.0.0.0.0 Upgraded from 12.2.0.1.0 to 19.7.0.0.0
The COMMON of Oracle supplied users as following
SQL> select username,common from dba_users where oracle_maintained='Y';
USERNAME COMMON
_____________ _________
SYSTEM NO
SYS NO
SYSDG YES
SYSKM YES
SYSBACKUP YES
SYSRAC YES
DBSNMP NO
... ...
FLOWS_FILES YES
APEX_PUBLIC_ROUTER YES
APEX_240100 YESIt shows that, SYS is not common user, but APEX_240100 is common user. The procedure APEX_ADMIN is owned by COMMON user APEX_240100, which cannot be processed by local (non-common) user SYS. Therefore, ORDS upgrade returned the warnings.
WORKAROUND
The APEX 404 error can be fixed as a workaround by changing the pool.xml of ORDS databse pool.
Remove following line from the file
<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
Add following ling to the file
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
Restart ORDS application.
SOLUTION
Since the root cause is the COMMON of SYS user, we can update the COMMON to YES for Oracle supplied user (including SYS, SYSTEM). Then re-run the ORDS upgrade.
To update COMMON, following the steps,
1. Stop applications which are accessing database
If this is a RAC database, shutdown all instances except one. The remaining steps will be performed on the running instance. Ensure that no application sessions are connected. Optionally, put the system into restricted session mode so that non-DBA connections cannot be made (alter system enable restricted session).
2. Backup the database
Make a backup of the database, or create a backup of table user$ with CREATE TABLE SELECT FROM
connect / as sysdba create table sys.backup_of_user$ as select * from sys.user$; create table sys.backup_of_user$_updated as select * from sys.user$ where bitand(spare1, 256) = 256;
3. Update the data dictionary using following commands
connect / as sysdba update user$ set spare1 = spare1 - bitand(spare1, 128) + 128 where bitand(spare1, 256) = 256; commit;
4. Ensure that stale user$ information is removed from the cache
If possible, shutdown and restart the instance. If that is not possible, try to flush the shared pool three times with commands
alter system flush shared_pool;
If RAC, start all other instances. If the system was previously put into restricted session mode, that can now be disabled (alter system disable restricted session).
5. Verify that the users (SYS, SYSTEM, etc) are now common users
SQL> select username,common from dba_users where oracle_maintained='Y';
USERNAME COMMON
_____________ _________
SYSTEM YES
SYS YES
SYSDG YES
SYSKM YES
SYSBACKUP YES
SYSRAC YES
DBSNMP YES
... ...
FLOWS_FILES YES
APEX_PUBLIC_ROUTER YES
APEX_240100 YES