ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher [ID 557070.1]

posted Sep 13, 2010, 11:18 AM by Sachchida Ojha
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;
            /

Comments