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.
helpful thanks.
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?
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’;
Nice tutorial, helped to solve my problem!
Helpful post, Thanks
Thanks! Worked like a charm.
Thank you.!!
Thanks a lot.
Thaks a lot! Helpful information!
Worked for Me…thank you very much…very helpful….
Good article .. Thanks for sharing..
I Want to Know what is “http_permissions.xml”
is this a fix named file or may be deferred name ?
It can be any name you want
thanks for Helpful information
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.
@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.
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 !!!!!
YESSSSSSSSSSSSSSSSSSSSSSSS
I find the solution : need GRANT EXECUTE ANY PROGRAM TO ;
UPPYYYYYYYYYYYYYYYYYYYYYYYYYYYY !! 🙂 🙂
GRANT EXECUTE ANY PROGRAM TO ;
TO what??? 🙁 🙁
This Tutorial is very helpful
My first time of dealing with ACL and it worked like a magic
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 => ‘*’);
works like a charm. Thanks very much.
Kevin
Very useful. Thanks.
thank you very much
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
You need to ask your DBA to grant you as SYS the required privileges to use this
Pingback: How to resolve ORA-24247: network access denied by access control list (ACL) | Wicky Hu's Blog
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.
http://dbtricks.com/?p=159#comment-838