11g and Demantra R12 - Access Control List (ACL)

Hi Everyone!!

I have integrated Demantra 7.2 with 11.1.0.6 DB Apps environment 12.0.3.
After the integration I started facing the issues mentioned below. This issue is likely to happen if you are upgrading your 10g environment to 11g which had Demantra integration.

Errors seen integration with 11g:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)

This is due to 11g new feature ACL. UTL_TCP, UTL_HTTP and UTL_SMTP packages enable communication between servers outside the realm of the database. These packages impose security risks which are addressed by 11g novel technique called as ACL. Risk is averted by granting access to required resources to schema which is really intended by developer. More about ACL can be found at http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-security.html with examples.(search for Access Control List)


DEMANTRA_TEMPLATE is the schema you would have created when integrating Demantra with Ebiz R12 in Ebiz DB. when you have already integrated 10g DB and you are going for 11g, you need to grant access to DEMANTRA_TEMPLATE as DEMANTRA_TEMPLATE schema internally uses UTL_HTTP packages.

You can willingly reproduce the issue by running the PL/SQL block as DEMANTRA_TEMPLATE schema

DECLARE
l_url varchar2(32767);
l_conn utl_http.req;
BEGIN
l_url := 'http://www.oracle.com';
l_conn := utl_http.begin_request(url => l_url, method => 'POST',
http_version=> 'HTTP/1.0');
dbms_output.put_line('Anonymous Block Executed Successfully');
END;
/

DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 6

What is the fix?

You can run below plsql block and grant access to DEMANTRA_TEMPLATE schema.

DECLARE
ACL_PATH VARCHAR2(32767);
BEGIN
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
dbms_output.put_line('acl_path = '|| acl_path);
dbms_output.put_line('ACL exists. Now checking for privilege and adding the privilege');
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH,'DMTRA_TEMPLATE','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'DMTRA_TEMPLATE', TRUE, 'connect');
END IF;
EXCEPTION -- When no ACL has been assigned to '*'
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('DMTRA_TEMPLATE does not have privilege, creating the ACL now');
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('users.xml',
'ACL that lets DMTRA_TEMPLATE to use the UTL Package',
'DMTRA_TEMPLATE', TRUE, 'connect');
dbms_output.put_line('DMTRA_TEMPLATE does not have privilege, assigning ACL now');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('users.xml','*');
END;


Or you can move to 12.04 RUP level, where you have fix patch. relavent metalink note:730883.1

If you going to move to 11g from 10g Demantra environment, this article will help you.

See you all in next article.

Happy Reading!!