select * from dba_network_acls;
==============================
begin
dbms_network_acl_admin.create_acl (
acl => 'http_permissions.xml', -- or any other name
description => 'HTTP Access',
principal => 'CHADMOON', -- the user name trying to access the network resource
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'CHADMOON',
is_grant => true,
privilege => 'connect');
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'CHADMOON',
is_grant => true,
privilege => 'resolve');
end;
/
BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'http_permissions.xml',
host => 'smtp.sendgrid.net', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/
lower_port => 25,
upper_port => 25
);
END;
/
======================================================================================
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.
