ORA-24247: network access denied by access control list (ACL) while sending email oracle

Question

i have done all the activity i.e mention below, please tell which step / activity i am missing.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
                                    acl         => 'apex_user.xml',
                                    description => 'access to apex email',
                                    principal   => 'DBUSER',
                                    is_grant    => TRUE,
                                    privilege   => 'connect',
                                    start_date  => SYSTIMESTAMP,
                                    end_date    =>Null
                                    );
 COMMIT;
 END; 
  BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                                        acl       => 'apex_user.xml',
                                       principal => 'DBUSER',
                                       is_grant  => true,
                                       privilege => 'resolve'
                                       );

 COMMIT;
 END;

   BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
                                    acl         => 'apex_user.xml',
                                    host        => 'smtp.gmail.com',
                                    lower_port  =>587,
                                    upper_port  =>587
                                    );
COMMIT;
 END;

to make sure the user can access the smtp packages, Run as SYS

GRANT EXECUTE ON UTL_TCP  TO DBUSER;
GRANT EXECUTE ON UTL_SMTP TO DBUSER;
GRANT EXECUTE ON UTL_MAIL TO DBUSER;
GRANT EXECUTE ON UTL_http TO DBUSER;

Enabling UTL_MAIL

alter system set smtp_out_server = 'smtp.gmail.com:587' scope = both;

Once i execute following query in Oracle since [email protected] having less secure app as true from google account

begin
  utl_mail.send(
  sender     => '[email protected]',
  recipients => '[email protected]',
  message    => 'Hello World'
   );
end;

Error report - ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first

and give error once call from apex as

APEX_MAIL.SEND( 
    p_to => '[email protected]', 
    p_from => '[email protected]', 
    p_subj => 'APEX_MAIL with attachment', 
    p_body => 'Please review the attachment.', 
    p_body_html => '<b>Please</b> review the attachment');

ORA-24247: network access denied by access control list (ACL)

however i tried using utl_smtp and again same error

create or replace PROCEDURE send_email(p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 587)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message  || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;

ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. c13sm6735648wrb.38 - gsmtp

please tell which command or anything misisng..

Answer 1

You don't need any commit, since explicit DML operations are not performed for these operations. And using begin..end blocks not needed for every method invoking, either.

Your issue stems from the fact the neccessity of invoking Dbms_Network_Acl_Admin.Add_Privilege method with privilege => 'connect' option also. So you can use the following :

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
                                    acl         => 'apex_user.xml',
                                    description => 'access to apex email',
                                    principal   => 'DBUSER',
                                    is_grant    => TRUE,
                                    privilege   => 'connect',
                                    start_date  => SYSTIMESTAMP,
                                    end_date    =>Null
                                    );
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                                       acl       => 'apex_user.xml',
                                       principal => 'DBUSER',
                                       is_grant  => true,
                                       privilege => 'connect'
                                       );

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                                       acl       => 'apex_user.xml',
                                       principal => 'DBUSER',
                                       is_grant  => true,
                                       privilege => 'resolve'
                                       );


  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
                                    acl         => 'apex_user.xml',
                                    host        => 'smtp.gmail.com',
                                    lower_port  =>587,
                                    upper_port  =>587
                                    );
 END;

With the following query all privileged accesses could be checked ( through SYS or SYSTEM schemas ):

select a.host,p.*
  from dba_network_acl_privileges p
  join dba_network_acls a on a.aclid = p.aclid
 order by a.host, p.principal, p.privilege;

Answer 2

1) IMHO smtp.gmail.com requires secured connection and authentication but package utl_mail is not supporting these functions. You can achieve this with utl_smtp.

2) The Apex_mail package run with the privileges of their definer (apex_scheama) and your acl list is defined for dbuser. Also here you have somehow do the smtp authentication .