2011-09-13

ORA-24247

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

SymptomsAfter 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)

Changes

Upgrade / new install of 11gR1 (11.1.0.6) or higher

Cause

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. See

Oracle� Database PL/SQL Packages and Types Reference
11g Release 1 (11.1.)

Part Number B28419-02

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;
      /

References

BUG:7010711 - ACL PRIVILEGES GRANTED TO ROLES ARE NOT CASCADED TO THE USERS
NOTE:553542.1 - How to Send Emails with UTL_SMTP containing Unicode Characters in Mail Subject and Body
NOTE:754909.1 - ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure

Show Related Information Related


Products

  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
  • Oracle Database Products > Oracle Database > Application Development > PL/SQL
Errors

ORA-20000; ORA-24247; ORA-31001; ORA-6512

Back to topBack to top

Niciun comentariu:

Trimiteți un comentariu