Troubleshoot: ORA-24247 al tratar de enviar un email

Sonará inventado, pero todavía existen instalaciones con Oracle 10.2, y precisamente en una de estas, recién actualizada a 19c, surgió un problema para el que pidieron mi asistencia: una aplicación poco utilizada, que entre sus procesos envía un email de alerta, dejó de operar.

Por fortuna, su resolución es bastante simple, tal como veremos a continuación, acompáñame.

El problema

Hasta antes de Oracle Database 11gR1, el uso de los paquetes UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP y UTL_HTTP, solamente requería de otorgar permisos de ejecución (grant execute), pero ahora se requiere de un registro especial, llamado Access Control List (en adelante ACL).

Sin este registro toda interacción con servicios de red externos será impedido, con el error ORA-24247: network access denied by access control list (ACL).

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Advertencia!',
    message    => 'Oracle 23ai on-prem sigue sin liberarse');
END;
/

BEGIN
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 295
ORA-06512: at "SYS.UTL_SMTP", line 164
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2

La solución

Para obtener permisos totales para utilizar los paquetes UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP y UTL_HTTP, ahora se requiere adicionalmente de su registro a través del uso del paquete DBMS_NETWORK_ACL_ADMIN, de la siguiente manera:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  )
  ); 
END;
/

Donde:

  1. En host especificamos el nombre o la dirección IP del servidor al que requerimos acceder.
  2. En lower_port indicamos el puerto TCP donde se procesarán nuestras solicitudes.
  3. En ace especificamos el privilegio a conceder, quién lo recibe y el tipo de usuario.
    1. En privilege_list podríamos indicar connect, pero eso no solo permitiría el acceso a UTL_MAIL y UTL_SMTP, sino también a UTL_TCP y UTL_HTTP, por lo que lo recomendable es indicar smtp, para un mejor control.
    2. En principal_name especificamos el usuario de base de datos, para nuestro caso será el esquema ADMIN.
    3. En principal_type indicaremos siempre ptype_db, ya que estamos configurando un usuario de base de datos.

 

Finalmente, verificamos para confirmar que el código ya funciona de manera normal, lo que nos indica que el incidente ha sido solucionado.

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Advertencia!',
    message    => 'Oracle 23ai on-prem sigue sin liberarse');
END;
/

PL/SQL procedure successfully completed.

Tareas adicionales

  1. Esta consulta te permite identificar qué usuarios requieren de configuración adicional (ACL):
SELECT DISTINCT owner, referenced_name 
  FROM dba_dependencies
 WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP')
   AND owner NOT IN ('SYS','PUBLIC','GSMADMIN_INTERNAL','ORACLE_OCM')
 ORDER BY owner, referenced_name;
 
OWNER    REFERENCED_NAME
-------- ---------------
ADMIN    UTL_HTTP
ADMIN    UTL_MAIL
SCOTT    UTL_HTTP
SCOTT    UTL_SMTP
SCOTT    UTL_TCP
  1. Es posible comprobar los privilegios ya concedidos mediante la siguiente consulta:
SELECT host,
       lower_port,
       upper_port,
       principal,
       principal_type,
       privilege
  FROM dba_host_aces
 WHERE host = 'smtp.acme.com';
 
HOST          LOWER_PORT UPPER_PORT PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
------------- ---------- ---------- --------- -------------- ---------
smtp.acme.com         25         25 ADMIN     DATABASE       SMTP
  1. Puedes suprimir un privilegio ya otorgado mediante:
BEGIN
  DBMS_NETWORK_ACL_ADMIN.remove_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  ),    
    remove_empty_acl => TRUE); 
END;
/

Esto es solo uno de los muchos casos donde se aplica ACL. Para explorar más situaciones, revisa este artículo, redactado por Tim Hall. Sin embargo, ten en cuenta que se enfoca en la procedimiento usado con Oracle 11gR1 y 11gR2, dado que desde Oracle 12cR1 el procedimiento sufre cambios, tal como lo detalla en este artículo actualizado.

Para complementar lo aquí expuesto, también les recomiendo la lectura de las notas:

958129.1How To Set Network ACLs in Oracle To Access Packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR
1209644.1ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later

Posts Recientes

MView - Current
Si observas logs de vista materializada inmensos, debes seguir estos pasos para diagnosticar el problema y depurar sus contenidos.
slow mview refresh
Si el refresco de vistas materializadas es lento y estás en Oracle 19c, puedes hacer un pequeño cambio para optimizarlo.
Grid OOP patching - install golden image
Aprende a parchar Grid Infrastructure for Single Instance, con la modalidad out-of-place y usando gold image.
oop patch grid siha - patch new OH
Aprende a parchar Grid Infrastructure for Single Instance, también conocido como Oracle Restart, con la modalidad out-of-place.
Out of place patching Oracle RAC
Aprende a parchar base de datos Oracle RAC con la modalidad out-of-place, y usando Gold Image para máxima simplicidad.
Hide column contents
Implemente el ocultamiento de los contenidos de columnas, dinámicamente y sin tener que modificar sus aplicaciones.

¿Necesitas Ayuda?

Completa estos datos y estaré en contacto a la brevedad.