OraExplorer

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('user@company.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_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('user@company.com', 'Test Subject', 'Hello World');

PL/SQL procedure successfully completed

Tags: 11g, ACL, Network Service, UTL_HTTP, UTL_SMTP, UTL_TCP