Configure MS Outlook Mail for Oracle 12c Database

First install Oracle Packages to Configure Outlook Mail

[host@oracle]$sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin\utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin\utlsmtp.sql

SQL> @$ORACLE_HOME/rdbms/admin\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 or IP of Mail Server' scope=both;

ACL: Access Control List

Check for current existing ACL’s
Let’s check out all currently present Access Control Lists in the database;

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
no rows selected

SQL>

All right none present – let’s create a new Network Access List using

DBMS_NETWORK_ACL_ADMIN — privilege

Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access.
Use ‘resolve’ for UTL_INADDR name/IP resolution.
The text for the privilege is case sensitive.
A list of all available network access privileges is in the
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE reference

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)

SQL>BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_test_for_scott.xml',
    description  => 'A test of the ACL functionality',
    principal    => 'SCOTT', */ schema (user) name/*
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/

Add Privilege

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

SQL>begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT', */ schema (user) name/*
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

Assign Access Control List (ACL)

Cool, you granted SCOTT to connect and resolve
But you have not defined to which resources/port he is allowed to connect

== ADD PORT 80

SQL>begin
  dbms_network_acl_admin.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.<domain>.nl',
    lower_port => 80,
    upper_port => NULL);
END;
/

== ADD PORT 25

SQL>begin
  dbms_network_acl_admin.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.********.nl',
    lower_port => 25,
    upper_port => NULL);
  COMMIT;

SQL> select * from dba_network_acls

HOST       LOWER_PORT UPPER_PORT ACL
---------- ---------- ---------- ------------------------------
mail.**.nl         80         80 /sys/acls/acl_test_for_scott.xml
mail.**.nl         25         25 /sys/acls/acl_test_for_scott.xml
SQL>

Add another Database User (SCHEMA) to ACL list

SQL>BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'acl_test_for_scott.xml',
    principal => 'PANNENKOEK', */ schema (user) name/*
    is_grant => TRUE,
    privilege => 'connect');
END;
/

SQL> SELECT acl,
          principal,
           privilege,
           is_grant,
           TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
          TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
  FROM   dba_network_acl_privileges;
SQL> /

ACL                                 PRINCIPAL  PRIVILE IS_GR START_DATE  END_DATE
------------------------------      ---------- ------- ----- ----------- -----------
/sys/acls/acl_test_for_scott.xml    SCOTT      connect true  30-JAN-2013
/sys/acls/acl_test_for_scott.xml    PANNENKOEK connect true  30-JAN-2013