Enabling Network Services in Oracle Database

6.5 Enabling Network Services in Oracle Database

You must enable network services in Oracle Database to send outbound mail, use Web services, or use PDF report printing in Oracle APEX.

When and Why Network Services Must be Enabled
Enabling network services enables support for sending outbound mail in Oracle APEXAPEX, use of Web services in APEX, and PDF report printing with BI Publisher.
Granting Connect Privileges in Oracle Database 12c or Later
Procedures CREATE_ACL, ASSIGN_ACL, ADD_PRIVILEGE and CHECK_PRIVILEGE in DBMS_NETWORK_ACL_ADMIN are deprecated in Oracle Database 12c. Oracle recommends to use APPEND_HOST_ACE.
Troubleshooting an Invalid ACL Error
Learn how to identify any invalid ACL error by running the query.

Parent topic: Installing and Configuring Oracle APEX and Oracle REST Data Services

6.5.1 When and Why Network Services Must be Enabled

Enabling network services enables support for sending outbound mail in Oracle APEXAPEX, use of Web services in APEX, and PDF report printing with BI Publisher.

By default, the ability to interact with network services is disabled in Oracle Database 11g Release 2 or later. Therefore, if you are running Oracle APEX with Oracle Database 11g Release 2 or later, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_210200 database user. Failing to grant these privileges results in issues with:

Sending outbound mail in Oracle APEX.

Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
Consuming web services from APEX.
Making outbound LDAP calls from APEX.
PDF report printing with BI Publisher.

Note:

When upgrading APEX on a database 12c or newer, based on the configuration of the old APEX version the upgrade automatically configures Network Services.

Tip:

To run the examples described in this section, the compatible initialization parameter of the database must be set to at least 11.1.0.0.0. By default an 11g or 12c database will already have the parameter set properly, but a database upgraded to 11g or 12c from a prior version may not. For information about changing database initialization parameters, see Specifying the Database Compatibility Level in Oracle Multitenant Administrator's Guide.

6.5.2 Granting Connect Privileges in Oracle Database 12c or Later

Procedures CREATE_ACL, ASSIGN_ACL, ADD_PRIVILEGE and CHECK_PRIVILEGE in DBMS_NETWORK_ACL_ADMIN are deprecated in Oracle Database 12c. Oracle recommends to use APPEND_HOST_ACE.

The following example demonstrates how to grant connect privileges to any host for the APEX_210200 database user. This example assumes you connected to the database where Oracle APEX is installed as SYS specifying the SYSDBA role.

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

The following example demonstrates how to provide less privileged access to local network resources. This example enables access to servers on the local host only, such as email and report servers.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'localhost',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_210200',
                           principal_type => xs_acl.ptype_db));
END;
/

Parent topic: Enabling Network Services in Oracle Database

6.5.3 Troubleshooting an Invalid ACL Error

Learn how to identify any invalid ACL error by running the query.

If you receive an ORA-44416: Invalid ACL error after running the previous script, use the following query to identify the invalid ACL:

REM Show the dangling references to dropped users in the ACL that is assigned
REM to '*'.

SELECT ACL, PRINCIPAL
  FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
       NACL.ACLID = ACE.ACLID AND
       NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

Next, run the following code to fix the ACL:

DECLARE
  ACL_ID   RAW(16);
  CNT      NUMBER;
BEGIN
  -- Look for the object ID of the ACL currently assigned to '*'
  SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- If just some users referenced in the ACL are invalid, remove just those
  -- users in the ACL. Otherwise, drop the ACL completely.
  SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE
   WHERE ACLID = ACL_ID AND
         EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

  IF (CNT > 0) THEN

    FOR R IN (SELECT PRINCIPAL FROM XDS_ACE
               WHERE ACLID = ACL_ID AND
                     NOT EXISTS (SELECT NULL FROM ALL_USERS
                                  WHERE USERNAME = PRINCIPAL)) LOOP
      UPDATE XDB.XDB$ACL
         SET OBJECT_VALUE =
               DELETEXML(OBJECT_VALUE,
                         '/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]')
       WHERE OBJECT_ID = ACL_ID;
    END LOOP;

  ELSE
    DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID;
  END IF;

END;
/

REM commit the changes.

COMMIT;

Once the ACL has been fixed, you must run the first script in this section to apply the ACL to the APEX_210200 user.

Parent topic: Enabling Network Services in Oracle Database