UTL_HTTP giving a nasty ORA-24247: network access denied

I got a question today from a colleague.

He had the following code:

declare
    l_response varchar2(4000);
begin
    l_response := UTL_HTTP.REQUEST('http://www.google.com');
end;

Executing this worked fine but incorporating it in a package function gave back an ORA-24247: network access denied by access control list (ACL).

I didn’t hear about ACLs before but listen to the oracle documentation:

“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)”

(http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#BABCBFHD)

The solution is simple:

DECLARE
    l_filenaam varchar2(100) := '<yourfilename>';
    l_host     varchar2(100) := '<the host of the URL your trying to request, in this case: www.google.com>';
BEGIN
    l_filenaam := replace( l_host, '.', '_' );

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => l_filenaam,
    description  => '<your desctiption like: ACL voor host ' || l_host,
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => null,
    end_date     => null ); 

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl         => l_filenaam,
    host        => l_host,
    lower_port  => null,
    upper_port  => null );
END;
/

This is also a nice one:

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
  'power_users.xml'
, 'ACL that lets power users to connect to everywhere'
, 'APEX_030200'
, TRUE
, 'connect' );
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
'power_users.xml'
,'*' );

Pay attention to the fact that the apex schema has prefix “APEX_” now instead of the old ‘FLOWS_’.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>