Working with ACL

As sys USER:
----------------

exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
(
acl=> 'EBIS.xml',
description => 'ACL for Test Purpose',
principal=> 'TESTADM',
is_grant=> true,
privilege =>'connect'
);

exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'Test.xml', host => '192.168.9.4');

select * from dba_network_acls;
select * from dba_network_acl_privileges;
select * from user_network_acl_privileges;
SELECT DISTINCT aclid, XD.DESCRIPTION FROM xds_acl xd;

If you want to unassign an host from an ACL please execute below procedure

begin
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(acl => 'Test.xml', host => '192.168.9.5');
end;
/

As a TESTADM USER:
-------------------

> select sys.utl_http.request('http://192.168.9.5') from dual;

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

> select sys.utl_http.request('http://192.168.9.4') from dual;

...............
................
< width="50%" valign="middle" align="center">




-- means it works !!!


If we want to add a new principal to this ACL List. Please try with below code

Begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
(
acl => 'EBIS.xml',
principal => 'USER2',
is_grant => true,
privilege => 'connect',
position => null
);
COMMIT;
END
/

we can test by sending a sample mail

create or replace procedure test_mail is
mailhost VARCHAR2(30) := '192.168.10.74';
mail_conn utl_smtp.connection;
mesg varchar2(32767);
crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
text varchar2(32767);

begin
text := 'text_sample';
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
mesg := 'Subject: DBA ALERTS' || crlf || 'To: ' || crlf ||
'Mime-Version: 1.0' || crlf ||
'Content-Type: text/html; charset=BLT8MSWIN1257' || crlf || text;
utl_smtp.mail(mail_conn, 'saifur.rashid@saif.com');
utl_smtp.rcpt(mail_conn, 'saifur.rashid@saif.com');
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
end test_mail;


exec test_mail;

NB: for more command reference please visit
http://psoug.org/reference/dbms_network_acl_admin.html
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm