Last month we just migrated an application from 10G to 11g. During a test of the send mail package using UTL_SMTP, we got this error, “ORA-24247: network access denied by access control list (ACL).” After a quick search, found below way to setup ALC in 11G.
1. The send mail package which executes the UTL_SMTP failed.
TEST_USER SQL> exec pkg_mails.SendMail('user@.com', 'Test Subject', 'Hello World');
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "pkg_mails", line 283
ORA-06512: at line 3
2. To fix it, an ACL has to be created.
The principal is the user or role to be added into this ACL. In this case, the TEST_USER account is added during the ACL creation. This field is case sensitive.
SQL> connect / as sysdba
begin
dbms_network_acl_admin.create_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file
description => 'Resolve Network Access using UTL_INADDR', -- Brief description
principal => 'TEST_USER', -- First user account or role being granted or denied permission
-- this is case sensitive,
-- but typically user names and roles are stored in upper-case letters
is_grant => TRUE, -- TRUE = granted, FALSE = denied
privilege => 'resolve', -- connect or resolve, this setting is case sensitive,
-- so always enter it in lowercase
-- connect if user uses the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL
-- resolve if user uses the UTL_INADDR
start_date => null, -- optional, null is the default
-- in format of timestamp_with_time_zone (YYYY-MM-DD HH:MI:SS.FF TZR)
-- for example, '2008-02-28 06:30:00.00 US/Pacific'
end_date => null -- optional, null is the default
);
commit;
end;
/
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#insertedID11
Note that the privilege used for UTL_INADDR is resolve in lowecase.
The description of each variable is clearly described in the Oracle-Base’s article.
3. Verify a newly-created ACL.
SQL> SELECT any_path
FROM resource_view
WHERE any_path like '%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
4. Optionally you can add more users or roles into this ACL by using the add_privilege procedure. This is similar to the create_acl procedure except no description. Sample shown here is to add ADMIN_ADMIN_ROLE role.
begin
dbms_network_acl_admin.add_privilege (
acl => 'Mail_UTL_Access.xml',
principal => 'APP_ADMIN_ROLE',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
end;
/
5. Add a host and port range allowed.
begin dbms_network_acl_admin.assign_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file to be modified
host => '*', -- Network host to which this access control list will be assigned
-- This a host name or IP address or wild card name
lower_port => null, -- (optional)
upper_port => null); -- (optional)
commit;
end; /
SELECT utl_inaddr.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
hostname1
6. Test the send mail package again. This time there is no error, and the recipient receives email.
TEST_USER SQL> exec pkg_mails.SendMail('user@company.com', 'Test Subject', 'Hello World');
PL/SQL procedure successfully completed
