13 Replies Latest reply: Jul 14, 2012 1:19 AM by 949422 RSS

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

    satnam
      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
          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
            satnam
            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
              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
                satnam
                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
                  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
                      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
                      satnam
                      how to find which privilege is missing?
                      • 8. Re: ORA-24247: network access denied by access control list (ACL)error-UTL_HTTP
                        sb92075
                        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
                          satnam
                          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
                            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
                              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
                                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
                                  If anyone get the answer of satnam's problem, please let me know. I am also facing same problem.
                                  Thanks in Advance....