Print

Enabling Network Services in Oracle 11g

Written by Mandy. Posted in General Application.

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'
http://docs.oracle.com/cd/E37097_01/doc/install.42/e35123/otn_install.htm#HTMIG506

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!)

DECLARE
    ACL_PATH VARCHAR2(4000);
BEGIN
    -- 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');
    END IF;
EXCEPTION
-- 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');
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

 

You can then run this query to see your entries added to the table:

select * 
from dba_network_acl_privileges

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2