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

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