Wednesday 28 January 2015

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;

1 comment:

  1. Jadwal Sambung Ayam SV388 3 Maret 2019 - Minggu, Medan 3 Maret 2019 – Pada Hari Tersebut Akan Di Laksanakan Berbagai Pertandingan Sabung Ayam Secara Live di Arena Sabung Ayam Thailand.

    Situs Judi Sabung Ayam Online SV388 Merupakan Situs Judi Asal Thailand Yang Sangat Terkenal Dengan Permainan Sabung Ayam Yang Fair dan Menghibur Para Penonton Judi Sabung Ayam.

    Untuk Info Lebih Lanjut Bisa Hub kami Di :
    wechat : bolavita
    line : cs_bolavita
    whatsapp : +628122222995
    BBM: BOLAVITA

    ReplyDelete