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
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 25Trying 127.0.0.1…telnet: connect to address 127.0.0.1: Connection refusedtelnet: 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 25Trying 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
