6.6 Enabling Network Services in Oracle Database
Database administrators must enable network services in Oracle Database to send outbound mail, invoke web services, or use template-based PDF report printing with external print servers in Oracle APEX.
To learn more, see Enabling Network Services in Oracle Database in Oracle APEX Installation Guide.
Note:
Enabling network services does not apply to APEX instances running on Oracle Autonomous Database. APEX can communicate with external endpoints over the internet without additional configuration.- When and Why Network Services Must be Enabled
Enabling network services enables support for sending outbound mail in Oracle APEX, using REST Services, REST Enabled SQL, or other web services, and using a remote server for report printing. - Granting Connect Privileges
- Troubleshooting an Invalid ACL Error
Learn how to identify any invalid ACL error by running the query.
6.6.1 When and Why Network Services Must be Enabled
Enabling network services enables support for sending outbound mail in Oracle APEX, using REST Services, REST Enabled SQL, or other web services, and using a remote server for report printing.
By default, the ability to interact with network services is disabled in Oracle Database. Therefore, you must use the DBMS_NETWORK_ACL_ADMIN
package to grant network connect privileges to the database user that owns the APEX schema (APEX_240100
). 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 REST services and other web services from APEX.
- Making outbound LDAP calls from APEX.
- Using a remote print server for report printing.
The granted network connect privileges apply to the entire APEX instance and enable all applications in all workspaces to perform outbound network calls. You do not need to grant network connect privileges to individual workspace schemas unless applications also use native database PL/SQL API such as UTL_HTTP
and UTL_SMTP
.
Note:
When upgrading APEX, the upgrade automatically configures Network Services based on the configuration of the previous APEX version.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, the parameter is set properly, but a database upgraded from a version prior to 11g may require an update. For information about changing database initialization parameters, see Specifying the Database Compatibility Level in Oracle Multitenant
Administrator's Guide.
Parent topic: Enabling Network Services in Oracle Database
6.6.2 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the the database user that owns the APEX schema (APEX_240100
). 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_APPLICATION.g_flow_schema_owner,
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 only to servers running on the same database host (localhost
), such as email and report printing servers.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'localhost',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => APEX_APPLICATION.g_flow_schema_owner,
principal_type => xs_acl.ptype_db));
END;
/
Parent topic: Enabling Network Services in Oracle Database
6.6.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_240100
user.
Parent topic: Enabling Network Services in Oracle Database