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

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 top