This is one of the 11g features I read it once when it was first released but did not see its significance until now. Last week we just migrated an application from 9i 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, I’m in luck because I found a lot of articles written about this new 11g feature. However, I particularly find these two well-written concepts and samples from Arup Nanda’s Access Control Lists for UTL_TCP/HTTP/SMTP and Oracle-Base’s Fine-Grained Access to Network Services in Oracle Database 11g Release 1 very helpful.
My sample here is from our test case:
1. The send mail package which executes the UTL_SMTP failed.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('firstname.lastname@example.org', '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_LoadStatus", 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 => 'Mail_UTL_Access.xml', description => 'Mail UTL Network Access', principal => 'TEST_USER', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); commit; end; /
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 '/sys/acls/%.xml'; ANY_PATH -------------------------------------------------------------------------------- /sys/acls/Mail_UTL_Access.xml /sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3684e24e04403ba6c65c6_acl.xml /sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf36e4e24e04403ba6c65c6_acl.xml /sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3724e24e04403ba6c65c6_acl.xml /sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN602a67cf3764e24e04403ba6c65c6_acl.xml /sys/acls/all_all_acl.xml /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 => 'Mail_UTL_Access.xml', host => 'smtp.company.com', lower_port => 1, upper_port => 1024); commit; end; /
6. Test the send mail package again. This time there is no error, and the recipient receives email.
TEST_USER SQL> exec pkg_LoadStatus.SendMail('email@example.com', 'Test Subject', 'Hello World'); PL/SQL procedure successfully completed