Send email using utl_smtp in Oracle 11g (results in ORA-24247)(11g ACL 网络访问控制列表)

数据库中有使用UTL_SMTP包 发送邮件的功能，但是升级11G 后该存储过程执行出现ORA-24247错误
ALERT 日志记录
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 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
UTL_TCP、UTL_HTTP 、UTL_SMTP支持数据库领域外的服务器之间的通信，utl_smtp 用于在主机间进行SMTP邮件通信， 11G前数据库用户可以与达该主机可通信的任何其他计算机(默认) ，开放的同时也带来了巨大的安全风险， 在11G出于安全考虑增加了数据库用户与 主机 加端口的细粒度权限控制Access Control List (ACL) ，DBMS_NETWORK_ACL_ADMIN 提供非常COOL的管理功能.
下面来演示问题解决的过程，注意send_mail procedure是在SYSTEM下创建，调用是在icme用户的procedure中

check for current existing ACL’s

selec * from dba_network_acls

create new list

sys@ICME>BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.create_acl (
  3      acl          => 'acl_sendmail_for_icme.xml', 
  4      description  => 'send mail of the ACL functionality',
  5      principal    => 'ICME',
  6      is_grant     => TRUE, 
  7      privilege    => 'connect',
  8      start_date   => null,
  9      end_date     => NULL);
 10  
 11    COMMIT;
 12  END;
 13  /
PL/SQL procedure successfully completed.

add access point to the new ACL

sys@ICME>BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.assign_acl (
  3      acl => 'acl_sendmail_for_icme.xml',
  4      host => 'smtp.mail.haoyisheng.com', 
  5      lower_port => 25,
  6      upper_port => null); 
  7  END;
  8  /
PL/SQL procedure successfully completed.
sys@ICME>select * from dba_network_acls
  2  ;
HOST                      LOWER_PORT UPPER_PORT ACL                                 ACLID
------------------------- ---------- ---------- ----------------------------------- --------------------------------
smtp.mail.haoyisheng.com         25         25 /sys/acls/acl_sendmail_for_icme.xml E7812CF27B6666AFE04338D4A8C0CB1A

Tip:add another access point.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'acl_sendmail_for_icme.xml',
    host => 'smtp.mail.haoyisheng.com', 
    lower_port => 80,
    upper_port => NULL); 
  COMMIT
  end;

test and verify

sys@ICME>DECLARE
  2     v_mailsever_host VARCHAR2(30) := 'smtp.mail.haoyisheng.com';
  3     v_mailsever_port PLS_INTEGER  := 25;
  4     l_mail_conn  UTL_SMTP.CONNECTION;
  5   BEGIN
  6     l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
  7   END;
  8   /
PL/SQL procedure successfully completed.
sys@ICME>select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('acl_sendmail_for_icme.xml','ICME','connect') from dual;
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('ACL_SENDMAIL_FOR_ICME.XML','ICME','CONNECT')
------------------------------------------------------------------------------------
                                                                                   1

create new session login db as icme

SQL> exec system.send_mail('zhangweizhao@mail.haoyisheng.com','test','test');
BEGIN system.send_mail('zhangweizhao@mail.haoyisheng.com','test','test'); END;
*
第 1 行出现错误:
ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
ORA-06512: 在 "SYS.UTL_TCP", line 17
ORA-06512: 在 "SYS.UTL_TCP", line 267
ORA-06512: 在 "SYS.UTL_SMTP", line 161
ORA-06512: 在 "SYS.UTL_SMTP", line 197
ORA-06512: 在 "SYSTEM.SEND_MAIL", line 18
ORA-06512: 在 line 1

Grant the connect privilege add send_mail procedure owner SYSTEM to the ACL list

sys@ICME>select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('acl_sendmail_for_icme.xml','SYSTEM','connect') from dual;
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('ACL_SENDMAIL_FOR_ICME.XML','SYSTEM','CONNECT')
--------------------------------------------------------------------------------------
sys@ICME>BEGIN
  2  dbms_network_acl_admin.add_privilege (
  3      acl=> 'acl_sendmail_for_icme.xml',    
  4      principal    => 'SYSTEM',
  5      is_grant     => TRUE, 
  6      privilege    => 'connect',
  7      start_date   => SYSTIMESTAMP,
  8      end_date     => NULL);
COMMIT;
END;
 11  /
PL/SQL procedure successfully completed.
sys@ICME>select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('acl_sendmail_for_icme.xml','SYSTEM','connect') from dual;
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('ACL_SENDMAIL_FOR_ICME.XML','SYSTEM','CONNECT')
--------------------------------------------------------------------------------------
                                                                                     1

query ACL grants

  1  SELECT acl,
  2           principal,
  3           privilege,
  4           is_grant,
  5           TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
  6           TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
  7*   FROM   dba_network_acl_privileges
sys@ICME>/
ACL                                 PRINCIPAL                      PRIVILE IS_GRANT   START_DATE     END_DATE
----------------------------------- ------------------------------ ------- ---------- -------------- --------------
/sys/acls/acl_sendmail_for_icme.xml ICME                           connect true       29-SEP-2013
/sys/acls/acl_sendmail_for_icme.xml SYSTEM                         connect true       29-SEP-2013

create new session login db as icme,test again

SQL> exec system.send_mail('zhangweizhao@mail.haoyisheng.com','test','test');
PL/SQL 过程已成功完成。

drop ACl

											 
sys@ICME> exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('acl_sendmail_for_icme.xml');
PL/SQL procedure successfully completed.

Summary:
从11g起使用 UTL_TCP、UTL_HTTP 、UTL_SMTP建立通信需要使用ACL授权，对用户，主机，端口的连接控制，主机允许使用星号的通配符。