After upgrading a database to 11gR1 (11.1.0.6), trying to send emails using UTL_SMTP fail with ERROR at line 1: ORA-20000: Failed to send mail due to the following error:ORA-24247: network access denied by access control list (ACL) ORA-06512: at %, line %d ORA-06512: at line %d even if nothing has changed (i.e. same machine used) This is due to Starting with Oracle 11gR1 (11.1.0.6) so called "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc. By default, the ports are blocked and ORA-24247 is raised to signal this. To control the ACL, Package DBMS_NETWORK_ACL_ADMIN can be used. Solution: 1. Please connect as SYS user to database and create the following procedure: connect / as sysdba set serveroutput on show user; create or replace procedure mailserver_acl( aacl varchar2, acomment varchar2, aprincipal varchar2, aisgrant boolean, aprivilege varchar2, aserver varchar2, aport number) is begin begin DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl); dbms_output.put_line('ACL dropped.....'); exception when others then dbms_output.put_line('Error dropping ACL: '||aacl); dbms_output.put_line(sqlerrm); end; begin DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege); dbms_output.put_line('ACL created.....'); exception when others then dbms_output.put_line('Error creating ACL: '||aacl); dbms_output.put_line(sqlerrm); end; begin DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport); dbms_output.put_line('ACL assigned.....'); exception when others then dbms_output.put_line('Error assigning ACL: '||aacl); dbms_output.put_line(sqlerrm); end; commit; dbms_output.put_line('ACL commited.....'); end; / show errors 2. Grant the desired access by adjusting values * ACL XML file (here : mailserver_acl.xml) * mail server (here: my.mail.server) * mail server port (here: 25) * Oracle user using the UTL_SMTP package (here SCOTT) to send email * access privilege (here connect) Note: It is not possible to replace the Oracle user by a Role - privileges are not cascaded to the specific users which have been granted a role - see Bug 7010711 and executing as SYS begin mailserver_acl( 'mailserver_acl.xml', 'ACL for used Email Server to connect', 'SCOTT', TRUE, 'connect', 'my.mail.server', 25); end; / which gives following OUTPUT Connected. USER is "SYS" Procedure created. No errors. Error dropping ACL: mailserver_acl.xml ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml" ACL created..... ACL assigned..... ACL commited..... PL/SQL procedure successfully completed. The ORA-31001 is thrown when a ACL is tried to drop which is not existant. This is an expected behaviour. 3. Running the procedures which send the emails will now work and NO ORA-24247 is raised 4. Customization issues * When using the DBMS_NETWORK_ACL_ADMIN package in a own implementation to create / update / delete ACLs it has to be ensured that the changes are commited by doing a explicit COMMIT after the last call to DBMS_NETWORK_ACL_ADMIN package. * To allow a different user to send emails then the user to be used during creation of the ACL following code can be used begin DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('mailserver_acl.xml','<ANOTHER SCHEMA>',TRUE,'connect'); commit; end; / |
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's > Database Monitoring and Troubleshooting >