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

Problem:

----Using SCOTT Database user getting the following error---

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Solution

----execute the follwoing process using SYS user---

SQL> exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'SCOTT', is_grant => true, privilege => 'resolve', start_date   => SYSTIMESTAMP);

PL/SQL procedure successfully completed.

SQL> exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'resolve.xml',principal => 'SCOTT',is_grant => true,privilege => 'connect');


----try to connect using SCOTT user----

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual


SELECT host, lower_port, upper_port, acl FROM dba_network_acls;


----Reference Query---

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;


------DELETE Privilege from ACL--------------

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'resolve.xml',
        principal   => 'SCOTT');
END;

------DROP ACL---------------
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
   acl           IN VARCHAR2);
   
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'resolve.xml');
END;   

BEGIN
   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
     host        => '*.us.oracle.com',
     lower_port  => 80);
END;