Install / Configure utl_mail and utl_smtp on Oracle 11g R2

Install utl_mail/utl_smtp

[host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlmail
SQL> @utlsmtp
SQL> @prvtmail.plb
SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mail.server.com' scope=both;

ACL: Access Control List

in White Horses Blog

Create ACL and privileges

Now first create an ACL as SYS (or any other user with DBMS_NETWORK_ACL_ADMIN execute granted), this will hold the privileges. You can add as many privileges as you like to this file, but I would recommend to split privileges in the ACL to specific tasks or users. You must create an ACL with at least one privilege, so lets start with the ‘connect’ privilege for user SCOTT, (also a role can be added as principal):

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

Add Privilege

Great, now that the ACL is created, you can add more privileges like the ‘resolve’ privilege:

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

Assign ACL

Cool, you granted SCOTT to connect and resolve, but you have not defined to which resources he is allowed to connect:

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'smtp server host name or address'
  );
  commit;
end;

I hope it was useful!!!
Cheers

  1. pls how can user SCOTT send a simple email?

  2. Note: dbms_network_acl_admin requires XML DB to be installed (which comes with an APEX installation, but can be installed seperately)!

  3. Thanks, cant be clearer than this.

  4. Here is test procedure after above setup.

    CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
    p_from IN VARCHAR2,
    p_message IN VARCHAR2,
    p_smtp_host IN VARCHAR2,
    p_smtp_port IN NUMBER DEFAULT 25)
    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;

    begin
    send_mail(‘ashish@mail.com’,’ashish@mail.com’,’Testmail from oracle’,’css-smtp.host.com’,25);
    end;
    /

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Optimization WordPress Plugins & Solutions by W3 EDGE