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