To enable users to use UTL_TCP, UTL_SMTP packages to acces network services

See Oracle XML DB Developers Guide for more informaiton

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb21sec.htm

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.

To enale all users to user the UTL_SMTP package to access two mail servers

smtp.test.edu; smtp.test2.edu

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'UTL_SMTP.xml',
description => 'ACL for utl_smtp package',
principal => 'ZENGYH',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL

);

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'UTL_SMTP.xml',principal => 'PUBLIC', is_grant => TRUE,privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'UTL_SMTP.xml',
host => 'smtp.test.edu'
);

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'UTL_SMTP.xml',
host => 'smtp.test2.edu'
);

END;
/
COMMIT;

If need to assigne more mail servers, just copy the DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL code from the above and replace the mail host with a new host.

To delete the access control list to start over, drop the access control list and unassign the host

exec dbms_network_acl_admin.drop_acl('UTL_SMTP.xml');

BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(acl => 'UTL_SMTP.xml', host => 'smtp.missouri.edu');
END;

In addition to the above, install the oracle mail scripts to instal UTL_MAIL packages to enable sending emails form inside of database

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

SQL>@$ORACLE_HOME/rdbms/admin/prvtmail.plb

SQL>ALTER SYSTEM SET smtp_out_server='smtp.test.edu;smtp.test2.edu' scope=BOTH;

to test the mail package

exec utl_mail.send(sender => 'oracle@testdb.test.com', recipients => 'test@test', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');

create a test function send_mail to use the UTL_SMTP package

select send_email('test@test','BALBALBALBA2') from dual;

To check

select * from v$parameter
where name like 'smtp%'

SELECT acl FROM dba_network_acls
WHERE host = 'smtp.missouri.edu' AND lower_port
IS NULL AND upper_port IS NULL;

select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('UTL_SMTP.xml',
'PUBLIC','connect') from dual;

SELECT host, lower_port, upper_port, acl
FROM dba_network_acls;

SELECT acl,
principal,
privilege,
is_grant

FROM dba_network_acl_privileges;

SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;