ACL Creation and Access in 11g

1. To Drop ACL

exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('utlmailpkg.xml');

2. Create and Assign ACL TO DBUSER and Assign it all host.
-- BELOW CODE WILL CREATE NETWORK_SERVICES.xml ACL file and REQUESTS DBUSER AS INPUT, TO WHICH ACL ACCESS NEEDS TO BE PROVIDED

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL

( acl =>'NETWORK_SERVICES.xml',

description => 'ACL Access',

principal => '&&DBUSER',

is_grant => true,

privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE

( acl => 'NETWORK_SERVICES.xml',

principal => '&DBUSER',

is_grant => true,

privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL

( acl => 'NETWORK_SERVICES.xml',

host => '*');

COMMIT;

END;

/

3. Adding ACL privilege to ANOTHER DBUSER
-- BELOW CODE WILL REQUEST FOR ANOTHER DBUSER NAME TO WHICH ACL ACCESS NEEDS TO BE PROVIDED

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&&ANOTHERDBUSER', is_grant => true, privilege => 'connect');

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&ANOTHERDBUSER', is_grant => true, privilege => 'resolve');

4. smtp_out_server parameter settings
alter system set smtp_out_server='localhost:25' scope=BOTH;

5. To Test the ACL Access
conn DBUSER/
SQL> select utl_http.request('http://www.oracle.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

--------------------------------------------------------------------------------
var ssUrlPrefix = "/us/"; var ssUrlType = "2"; var g_navNode_Path = new Array(); g_navNode_Path[0] = '8'; var g_ssSourceNodeId = "8"; var g_ssSourceSiteId = "ocomen";

UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------

6. it should provide the above result,

if it is failure, then it throws
ORA-24247: network access denied by access control list (ACL)

7. In case you get ORA-29278: SMTP transient error: 421 Service not available.
The error shows that the smtp port (25) is not accessible.

( as per parameter smtp_out_server=localhost:25 )
When you do telnet 25 port , it will throw connection refused error..

[prod@linux dbhome_1]$ telnet localhost 25

Trying 127.0.0.1...telnet: connect to address 127.0.0.1: Connection refused

telnet: Unable to connect to remote host: Connection refused

IF that is the case Then, For linux ,please start sendmail service, from root
[root@linux ~]# service sendmail start
Once it is started you should be able get response from telnet and ORA-29278 error will be recovered
[prod@linux dbhome_1]$ telnet localhost 25

Trying 127.0.0.1...Connected to linux.mydb.com (127.0.0.1).

Escape character is '^]'.

220 linux.mydb.com ESMTP Sendmail 8.13.1/8.13.1; Thu, 26 Jan 2012 07:10:21 -0800