Keeping Network ACLs Alive Across Oracle APEX Upgrades

[admin]

When you patch or upgrade Oracle APEX, Oracle creates a new internal schema whose name embeds the APEX version, e.g.

APEX release Internal schema
22.2 APEX_220200
23.1 APEX_230100
24.1 (future) APEX_240100

If you granted network‑access privileges to the old schema (via XML ACLs in 11 g or unified ACLs in 12 c+), they do not follow the new schema. After the upgrade, calls from APEX—UTL_HTTP, APEX_WEB_SERVICE, REST data sources—will raise ORA-24247: network access denied by access control list.

Below are two approaches to make the change painless.

1  Create an ACL with a Fixed Principal (works, but must be redone every upgrade)

Code: Select all

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl(
    acl         => 'apex_acl.xml',
    description => 'ACL for APEX',
    principal   => 'APEX_230100',
    is_grant    => TRUE,
    privilege   => 'connect'
  );

  DBMS_NETWORK_ACL_ADMIN.assign_acl(
    acl  => 'apex_acl.xml',
    host => '*'
  );
END;
/

Pros: Simple, explicit.

Cons: After every APEX upgrade you must replace the principal with the new schema name and run it again.

2  Append an ACE Dynamically (recommended)

The package constant APEX_APPLICATION.G_FLOW_SCHEMA_OWNER always returns the current APEX schema owner, no matter how many times APEX is upgraded. Using it keeps your script upgrade‑proof:

Code: Select all

DECLARE
  v_owner VARCHAR2(100);
BEGIN
  v_owner := apex_application.g_flow_schema_owner;

  DBMS_NETWORK_ACL_ADMIN.append_host_ace(
    host => '*',
    ace  => xs$ace_type(
      privilege_list => xs$name_list('connect'),
      principal_name => v_owner,
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

How it works

v_owner is resolved at runtime after an upgrade it automatically returns the new schema (e.g. APEX_240100).

Code: Select all

APPEND_HOST_ACE adds or updates the ACE in the unified ACL that governs host => '*'.

If no ACL exists yet for '*', Oracle 12 c+ silently creates one before appending the ACE.

3  Verifying Your ACLs After an Upgrade

Run these catalog views to confirm your privileges survived:

Code: Select all

SELECT host, acl FROM dba_host_acls;

SELECT acl, principal, privilege
FROM   dba_network_acl_privileges
WHERE  principal = apex_application.g_flow_schema_owner;
If the second query returns at least one row with privilege CONNECT, APEX can reach external hosts.

4  Suggested Upgrade Checklist
Before patching, save your ACL definitions for reference:
SELECT * FROM dba_network_acl_privileges;

Perform the APEX upgrade.

Immediately after, run the dynamic ACE script (section 2).

Verify with the catalog queries in section 3.

Commit (the PL/SQL blocks above commit automatically unless you wrapped them in a larger transaction).

Takeaways

• Hard‑coding schema names ties your ACLs to a single APEX version.
• APEX_APPLICATION.G_FLOW_SCHEMA_OWNER makes the privilege assignment self‑healing.
• In Oracle 12c and later, APPEND_HOST_ACE is all you need—the database creates or reuses the correct ACL for you.