Steps to resolve ORA-24247: network access denied by access control list (ACL)

Steps to resolve ORA-24247: network access denied by access control list (ACL):




After we refreshed some of the production schemas to QA database, application users started reporting that the jobs are failing on QA database with below error.Later, we started debugging and  found that the users don't have necessary permissions on ACL.

 ERROR[com.cp.ejb.session.portfoliomgmt.PortfolioDelegatorBean][[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)']: got Exception while creating portfolio Schedule data:::::::::::

javax.ejb.EJBException: nested exception is: javax.ejb.TransactionRolledbackLocalException: Error committing transact ion:; nested exception is: java.sql.SQLException: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "USER1.portfolio", line 135
ORA-04088: error during execution of trigger 


Cause:
If you have upgraded from a release before Oracle Database 11g Release 1 (11.1), and your applications depend on PL/SQL network utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the following error may occur when you try to run the application:

ORA-24247: network access denied by access control list (ACL)

Solution:

Grant the connect, resolve privileges to users




SQL> SET LINESIZE 150

COLUMN acl FORMAT A50
COLUMN principal FORMAT A20
COLUMN privilege FORMAT A10

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
ORDER BY acl, principal, privilege;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8

ACL                                                PRINCIPAL            PRIVILEGE  IS_GRANT             START_DATE           END_DATE
-------------------------------------------------- -------------------- ---------- -------------------- -------------------- --------------------
/sys/acls/oracle-sysman-ocm-Resolve-Access.xml     ORACLE_OCM           resolve    true


Work around:

step 1 : Grant the connect and resolve privileges to SCOTT.

Simply run the below command to grant connect and resolve privileges to SCOTT user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => '/sys/acls/network_services_Resolve-Access.xml',
                                    description => 'SCOTT ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/network_services_Resolve-Access.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => '/sys/acls/network_services_Resolve-Access.xml',
                                    host => '*');
END;
/
COMMIT;


To add more users or roles to the access control list, or grant additional privileges to one user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure. The syntax is as follows:

If you want to grant connect and resolve privileges to multiple users on same host there is no need to create the new ACL using CREATE_ACL . we can simply use the existing ACL (/sys/acls/network_services_Resolve-Access.xml) created in previous step and add this privileges to others using ADD_PRIVILEGE procedure as mentioned below.



BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl         => '/sys/acls/network_services_Resolve-Access.xml',
                                    principal   => 'USER1',
                                    is_grant    => true,
                                    privilege   => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/network_services_Resolve-Access.xml',
                                       principal => 'USER1',
                                       is_grant  => true,
                                       privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => '/sys/acls/network_services_Resolve-Access.xml',
                                    host => '*');
END;
/
COMMIT;

ACL                                                PRINCIPAL            PRIVILEGE  IS_GRANT             START_DATE           END_DATE

-------------------------------------------------- -------------------- ---------- -------------------- -------------------- --------------------

/sys/acls/network_services_Resolve-Access.xml      CP360                connect    true

/sys/acls/network_services_Resolve-Access.xml      CP360                resolve    true

/sys/acls/network_services_Resolve-Access.xml      CP360MFADM           connect    true

/sys/acls/network_services_Resolve-Access.xml      CP360MFADM           resolve    true

/sys/acls/network_services_Resolve-Access.xml      MFEED                connect    true

/sys/acls/network_services_Resolve-Access.xml      MFEED                resolve    true

/sys/acls/network_services_Resolve-Access.xml      UNPRICED             connect    true

/sys/acls/network_services_Resolve-Access.xml      UNPRICED             resolve    true

/sys/acls/oracle-sysman-ocm-Resolve-Access.xml     ORACLE_OCM           resolve    true