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

If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:

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

The cause according to oracle is that

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

This error is common after an upgrade to Oracle 11. before oracle 11, using network resources via packages like utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr exposed the database to a serious security threat because once the user is granted with permission to use those packages there was no other limitation to connect to any computer.

Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).

This new feature gave the DBA a better control on which user can connect to which computer

In order to solve ORA-24247 you will need to:

1) Create an acl (if it is not already created)

2) Add privileges to the user using the network resources

3) Assign the acl to a specific address

1) run the following query to check if an ACL exists

SELECT *

FROM dba_network_acls;

If the computer you are trying to connect to is not listed under host, you will need to create an acl:


begin

dbms_network_acl_admin.create_acl (

acl => 'http_permissions.xml', -- or any other name

description => 'HTTP Access',

principal => 'SCOTT', -- the user name trying to access the network resource

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

end;

/

commit;

This will create the acl and grant SCOTT the connect privilege.

2) IF the acl exists run the following query to verify the user is granted with the appropriate privilege

SELECT *

FROM dba_network_acl_privileges

where principal='SCOTT';

In order to use UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL the user will need the connect privilege

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'SCOTT',

is_grant => true,

privilege => 'connect');

end;

/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'SCOTT',

is_grant => true,

privilege => 'resolve');

end;

/

commit;

3) The final step is to assign the acl to a specific target

BEGIN

dbms_network_acl_admin.assign_acl (

acl => 'http_permissions.xml',

host => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

lower_port => 80,

upper_port => 80

);

END;

It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.

However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.

29 thoughts on “How to resolve ORA-24247: network access denied by access control list (ACL)

  1. nasrin

    hello, I am in trouble, I did all the steps you mentioned in your article. I create the xml file and create insert privilege access to my user in oracle apex, but I get network access denied. I don’t know what is exactly wrong?! could u please help me?

  2. Asaf Tal Post author

    it is almost impossible to help remotely without steps to reproduce and a lot more details.

    I would suggest to post your question somewhere more appropriate (http://serverfault.com/ or http://stackoverflow.com/ ) you will need of course to describe your problem in more details.

    start with the results of :
    SELECT * FROM dba_network_acls;
    and
    SELECT * FROM dba_network_acl_privileges where principal=’THE USER YOU CREATED’;

  3. Pankaj

    I Want to Know what is “http_permissions.xml”
    is this a fix named file or may be deferred name ?

  4. Andries Burger

    Nice tutorial, and informative. We have just been hit with this exact same problem, except we have been on 11G for over a year. Never prior to going to 11G, nor when upgrading to 11GR2 had we needed an ACL. On Monday we suddenly have a stored procedure complain about ACL restriction to the email server. Anyone know of changes on a network, domain, GPO level that could trigger this response in Oracle 11G? I have an SR open with Oracle support on this issue. Your attention and thoughts are appreciated.

  5. Asaf Tal Post author

    @andries, if you never set up an ACL (can be easily checked using the query in the post) and now is the first time you get this error, i would assume that the procedure never tried to send an email. Maybe it sends mail only when it hits a specific event.
    If you did set up an ACL in the past, maybe the change is in the address you are trying to reach.

  6. Fra

    Hi, I try to excecute all steps in the post
    but I dont’ resolve my problem:
    This is the situetion:
    – I run then script from DWCMA user in one machine with this IP 134.44.28.44 , I dont know the port of this machine :

    SELECT host, lower_port, upper_port, acl
    FROM dba_network_acls;

    134.44.28.44 1 10000 /sys/acls/dbanag_acl_file.xml

    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;

    /sys/acls/dbanag_acl_file.xml DWCMA connect true
    /sys/acls/dbanag_acl_file.xml DWCMA resolve true

    i I run this script

    declare
    appo varchar2 ( 3000) ;
    begin

    select UTL_INADDR.GET_HOST_NAME into appo
    from dual ;
    dbms_output.put_line (appo) ;
    end;
    /

    It’s ok !

    BUT BUT BUT BUT if I Called this script inside the procedure or package dont’ work e give me the error

    ERROR at line 1:
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at “SYS.UTL_INADDR”, line 4
    ORA-06512: at “SYS.UTL_INADDR”, line 35
    ORA-06512: at line 1
    ORA-06512: at “DWCMA.PIPPO”, line 28
    ORA-06512: at line 3

    πŸ™ :'( I’m try eberythings HELP ME PLEASE !!!!!

  7. Fra

    YESSSSSSSSSSSSSSSSSSSSSSSS

    I find the solution : need GRANT EXECUTE ANY PROGRAM TO ;

    UPPYYYYYYYYYYYYYYYYYYYYYYYYYYYY !! πŸ™‚ πŸ™‚

  8. Daniel Osei Boateng

    This Tutorial is very helpful
    My first time of dealing with ACL and it worked like a magic

  9. ganesh

    try this
    Execute Dbms_Network_Acl_Admin.Create_Acl ( Acl => ‘utl_http.xml’,Description => ‘HTTP Access’, Principal => ‘schema’ ,Is_Grant => True, Privilege => ‘connect’ );
    execute dbms_network_acl_admin.add_privilege ( acl => ‘utl_http.xml’,principal => ‘schema’, is_grant => TRUE, privilege => ‘resolve’);
    Execute Dbms_Network_Acl_Admin.Assign_Acl (Acl => ‘utl_http.xml’,Host => ‘*’);

  10. aaditya

    I am not able to access the tables

    SELECT * FROM dba_network_acls;
    SELECT * FROM dba_network_acl_privileges ;

    and when I am executing the below statement :
    BEGIN
    DBMS_NETWORK_ACL_ADMIN.create_acl (acl => ‘http_permissions.xml’, — or any other name
    description => ‘HTTP Access’,
    principal => ‘CMT’, — the user name trying to access the network resource
    is_grant => TRUE,
    privilege => ‘connect’,
    start_date => NULL,
    end_date => NULL);
    END;

    Error : ‘DBMS_NETWORK_ACL_ADMIN’ must be declared

    Please help me

  11. Pingback: How to resolve ORA-24247: network access denied by access control list (ACL) | Wicky Hu's Blog

  12. Poorwa

    Hi, I try to excecute all steps in the post
    but it dont’ resolve my problem:

    still getting the error -ORA-24247: network access denied by access control list (ACL)
    when try to execute the utl_mail.send in procedure.

    please suggest how to resolve the issue.

Leave a Reply

Your email address will not be published. Required fields are marked *