You create an APEX workspace, start building your app, and it's all going along nicely until you try to use the APEX_MAIL package, or try and configure connection to you LDAP for authentication.
If you do a bit of poking around, you may also come across this error when trying to send email notifications:
ORA-24247: network access denied by access control list (ACL)
or this error if trying to connect to LDAP for Authentication:
ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.
To solve this, you will need to get your DBA to Enable Network Services in Oracle 11g Database.
See the APEX 4.2 Installation Guide, Setion 3.3.8 on 'Enabling Network Services'
The reason seems to be that in 11g, Oracle tightened the security so users that needed to access network credentials like LDAP and Email required specific privileges granted. Unfortunately it seems you can’t grant it for the whole database (makes sense!). So each time you create a new APEX workspace and schema, the ACL will need to be updated for that new schema/user, if it requires Network Services.
Here is the snippet of code that seems to work for our installation - this is run as the SYS user (if you are unsure, check with a DBA before running any scripts in your production environments!)
-- Look for the ACL currently assigned to '*' and give SCHEMA_NAME
-- the "connect" privilege if SCHEMA_NAME does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'SCHEMA_NAME','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'SCHEMA_NAME', TRUE, 'connect');
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml','ACL that lets power users to connect to everywhere','SCHEMA_NAME', TRUE, 'connect');
You can then run this query to see your entries added to the table: