The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g

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

, , , , ,

5 Responses to The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g

  1. Pereira September 2, 2011 at 9:49 am #

    ittichai (Is this your nick?),

    The your explanation fixed my UTL_SMTP problem in Oracle DB 11G.

    Thanks for all.

    Pereira

    • ittichai September 2, 2011 at 11:08 am #

      Glad to hear that. Ittichai (real name 🙂 )

Trackbacks/Pingbacks

  1. OraExplorer » Oracle 11g Network Access Denied by Access Control List (ACL) when using UTL_INADDR - February 20, 2010

    […] Category: 11g, Database, Network — ittichai @ 12:10 pm I wrote in my previous post about the Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g. However, it did not cover another PL/SQL network utility package named UTL_INADDR which retrieves […]

  2. OraExplorer » 11gR2 New Feature – Alter Database Link to Change Password - March 26, 2011

    […] The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g […]

  3. OraExplorer » Oracle 11g SQL Error Logging - March 26, 2011

    […] The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g […]

Leave a Reply to ittichai Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress. Designed by WooThemes