This discussion is archived
13 Replies Latest reply: Jul 13, 2012 11:19 PM by 949422 RSS

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

706265 Newbie
Currently Being Moderated
I am getting following ACL error while executing following procedure:

create or replace procedure sat_proc as
http_req utl_http.req;
http_resp utl_http.resp;
BEGIN
http_req := utl_http.begin_request('www.yahoo.com');
http_resp := utl_http.get_response(http_req);
utl_http.end_response(http_resp);
END;
/
exec sat_proc;
/
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "TRANSDBA.SAT_PROC", line 5
ORA-06512: at line 1
________________________________________
I am able to execute successfully while executing above code as PL/SQL block:
________________________________________
DECLARE
http_req utl_http.req;
http_resp utl_http.resp;
BEGIN
http_req := utl_http.begin_request('www.yahoo.com');
http_resp := utl_http.get_response(http_req);
utl_http.end_response(http_resp);
END;
/

PL/SQL procedure successfully completed.

Could help me find why I am getting error while executing same code in a procedure? Is there any privilege missing?
  • 1. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    sb92075 Guru
    Currently Being Moderated
    Could help me find why I am getting error while executing same code in a procedure? Is there any privilege missing?
    privileges acquired via ROLE do no apply within named PL/SQL procedures.

    explicit GRANT to object is required
  • 2. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    706265 Newbie
    Currently Being Moderated
    sb92075, thanks for quick reply.

    which grant/privilege is required to object (sat_proc procedure)?

    Edited by: satnam on Jun 10, 2010 11:10 AM
  • 3. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    sb92075 Guru
    Currently Being Moderated
    You can demonstrate for yourself in sqlplus

    SET ROLE NONE
    DECLARE
    http_req utl_http.req;
    http_resp utl_http.resp;
    BEGIN
    http_req := utl_http.begin_request('www.yahoo.com');
    http_resp := utl_http.get_response(http_req);
    utl_http.end_response(http_resp);
    END;
    /

    above will throw same error.
    To be honest I am not sure which privilege is missing.
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
    24247, 00000, "network access denied by access control list (ACL)"
    // *Cause:    No access control list (ACL) has been assigned to the target
    //            host or the privilege necessary to access the target host has not
    //            been granted to the user in the access control list.
    // *Action:   Ensure that an access control list (ACL) has been assigned to
    //            the target host and the privilege necessary to access the target
    //            host has been granted to the user.
  • 4. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    706265 Newbie
    Currently Being Moderated
    I am still looking for answer. Please someone help me find solution for this problem...

    Edited by: satnam on Jun 10, 2010 12:59 PM
  • 5. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    Tubby Guru
    Currently Being Moderated
    Please pick a SINGLE forum (the most appropriate one) and stick to that ...

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

    Would be your identical post in the SQL/PLSQL forum.
  • 6. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    sb92075 Guru
    Currently Being Moderated
      1  DECLARE
      2  http_req utl_http.req;
      3  http_resp utl_http.resp;
      4  BEGIN
      5  http_req := utl_http.begin_request('www.yahoo.com');
      6  http_resp := utl_http.get_response(http_req);
      7  utl_http.end_response(http_resp);
      8* END;
    SQL> set term on echo on
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> set role none
      2  
    SQL> /
    
    Role set.
    
    SQL> DECLARE
    http_req utl_http.req;
    http_resp utl_http.resp;
    BEGIN
    http_req := utl_http.begin_request('www.yahoo.com');
    http_resp := utl_http.get_response(http_req);
    utl_http.end_response(http_resp);
    END;
    /  2    3    4    5    6    7    8    9  
    DECLARE
    *
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1130
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at line 5
    I am still looking for answer.
    where are you looking?
  • 7. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    706265 Newbie
    Currently Being Moderated
    how to find which privilege is missing?
  • 8. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    sb92075 Guru
    Currently Being Moderated
    1 begin
    2 dbms_network_acl_admin.assign_acl (
    3 acl => 'utlpkg.xml',
    4 host => 'www.proligence.com',
    5 lower_port => 1,
    6 upper_port => 10000);
    7* end;
    8 /

    http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-security.html
  • 9. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    706265 Newbie
    Currently Being Moderated
    I have already added below rule
    1 begin
    2 dbms_network_acl_admin.assign_acl (
    3 acl => 'utlpkg.xml',
    4 host => 'www.yahoo.com',
    5 );
    7* end;

    This was reason I was able to execute below PL/SQL block successfully:

    DECLARE
    http_req utl_http.req;
    http_resp utl_http.resp;
    BEGIN
    http_req := utl_http.begin_request('www.yahoo.com');
    http_resp := utl_http.get_response(http_req);
    utl_http.end_response(http_resp);
    END;
    /

    PL/SQL procedure successfully completed.

    But my question is why I am getting below error when I wrap above PL/SQL code in a procedure:

    create or replace procedure sat_proc as
    http_req utl_http.req;
    http_resp utl_http.resp;
    BEGIN
    http_req := utl_http.begin_request('www.yahoo.com');
    http_resp := utl_http.get_response(http_req);
    utl_http.end_response(http_resp);
    END;
    /
    exec sat_proc;
    /
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1130
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at "TRANSDBA.SAT_PROC", line 5
    ORA-06512: at line 1

    Edited by: satnam on Jun 10, 2010 1:51 PM

    Edited by: satnam on Jun 10, 2010 1:51 PM
  • 10. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    sb92075 Guru
    Currently Being Moderated
    GRANT EXECUTE ON SYS.UTL_HTTP TO <your_user>;
    SQL> set time on
    17:21:01 SQL> set role none;
    
    Role set.
    
    17:21:23 SQL> @utl_http.sql
    17:21:34 SQL> DECLARE
    17:21:34   2  http_req utl_http.req;
    17:21:34   3  http_resp utl_http.resp;
    17:21:34   4  BEGIN
    17:21:34   5  http_req := utl_http.begin_request('www.yahoo.com');
    17:21:34   6  http_resp := utl_http.get_response(http_req);
    17:21:34   7  utl_http.end_response(http_resp);
    17:21:34   8  END;
    17:21:34   9  /
    
    PL/SQL procedure successfully completed.
    
    17:21:35 SQL> connect / as sysdba
    Connected.
    17:22:47 SQL> connect dbadmin/admindb
    Connected.
    17:23:06 SQL> @utl_http.sql
    17:23:22 SQL> DECLARE
    17:23:22   2  http_req utl_http.req;
    17:23:22   3  http_resp utl_http.resp;
    17:23:22   4  BEGIN
    17:23:22   5  http_req := utl_http.begin_request('www.yahoo.com');
    17:23:22   6  http_resp := utl_http.get_response(http_req);
    17:23:22   7  utl_http.end_response(http_resp);
    17:23:22   8  END;
    17:23:22   9  /
    
    PL/SQL procedure successfully completed.
    
    17:23:23 SQL> set role none;
    
    Role set.
    
    17:23:29 SQL> @utl_http.sql
    17:23:31 SQL> DECLARE
    17:23:31   2  http_req utl_http.req;
    17:23:31   3  http_resp utl_http.resp;
    17:23:31   4  BEGIN
    17:23:31   5  http_req := utl_http.begin_request('www.yahoo.com');
    17:23:31   6  http_resp := utl_http.get_response(http_req);
    17:23:31   7  utl_http.end_response(http_resp);
    17:23:31   8  END;
    17:23:31   9  /
    DECLARE
    *
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1130
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at line 5
    
    
    17:23:31 SQL> 
    above is from test user


    Below is from SYSDBA account
    SQL> set time on
    17:20:53 SQL> revoke execute on sys.utl_http to dbadmin;
    revoke execute on sys.utl_http to dbadmin
                                   *
    ERROR at line 1:
    ORA-00905: missing keyword
    
    
    17:22:03 SQL> revoke execute on sys.utl_http from dbadmin;
    revoke execute on sys.utl_http from dbadmin
    *
    ERROR at line 1:
    ORA-04020: deadlock detected while trying to lock object
    ACLiLZU+w09hR7gQAB/AQAjcw==
    
    
    17:22:32 SQL> /
    
    Revoke succeeded.
    
    17:22:52 SQL> 
    Edited by: sb92075 on Jun 10, 2010 5:24 PM
  • 11. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    153504 Newbie
    Currently Being Moderated
    Satnam, did you ever get an answer to this?
  • 12. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    CKPT Guru
    Currently Being Moderated
    Hi,

    Please follow this metalink id
    *ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages [ID 453786.1] *

    Possibly this will help you Dear.
  • 13. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
    949422 Newbie
    Currently Being Moderated
    If anyone get the answer of satnam's problem, please let me know. I am also facing same problem.
    Thanks in Advance....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points