Could someone point me to some effective URL's and/or white papers on implementing Oracle 11G ACL (access control lists) in order to use the network packages UTL_TCP and UTL_SMTP?
I have an 11.2.0.1 (patched for magic number) database and need to send email from a procedure.
I obtained the mail package from asktom and it works great on 9i and 10g databases but I get an access control list permission denied. I have set up an ACL for allowing the user to use the packages but continue getting the
ORA-24247 error.I added the SMTP relay server and the local database server to the ACL but still no luck.
Below is what I have done and my test block. The server is AIX 5.3 and Oracle 11.2.0.1 -
Any help would be greatly appreciated -----------------------------------------------------------------------------------------------------------------------------------------------------------------SQL> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('TCP_SMTP_acl.xml', 'Network SMTP/TCP access ', 'E1084448', TRUE, 'connect');
PL/SQL procedure successfully completed.SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('TCP_SMTP_acl.xml', 'E1084448', TRUE, 'resolve');
PL/SQL procedure successfully completed.SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('TCP_SMTP_acl.xml','<SMTP Relay Server>',24,26);
PL/SQL procedure successfully completed. SQL> set lines 256 SQL> column principal format a30 SQL> column acl format a40SQL> select acl, principal, privilege, is_grant from dba_network_acl_privileges;
ACL PRINCIPAL PRIVILE IS_GR ---------------------------------------- ------------------------------ ------- ----- /sys/acls/TCP_SMTP_acl.xml E1084448 resolve true /sys/acls/TCP_SMTP_acl.xml E1084448 connect true
SQL> column principal format a20SQL> select a.acl, a.principal, a.privilege, a.is_grant, b.host, b.lower_port, b.upper_port
2 from dba_network_acl_privileges a, dba_network_acls b 3 where a.acl = b.acl 4 /ACL PRINCIPAL PRIVILE IS_GR HOST LOWER_PORT UPPER_PORT ---------------------------------------- -------------------- ------- ----- ---------------------------------------- ---------- ---------- /sys/acls/TCP_SMTP_acl.xml E1084448 resolve true <SMTP Relay Server> 24 26 /sys/acls/TCP_SMTP_acl.xml E1084448 connect true <SMTP Relay Server> 24 26
SQL> connect e1084448/<password> Connected. SQL> select * from user_network_acl_privileges;HOST LOWER_PORT UPPER_PORT PRIVILE STATUS ---------------------------------------- ---------- ---------- ------- -------
<SMTP Relay Server> 24 26 connect GRANTED SQL> select distinct name from user_source; NAME ------------------------------ SEND_TEST_MESSAGE SQL> exec SEND_TEST_MESSAGE BEGIN SEND_TEST_MESSAGE; END; * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_TCP", line 17 ORA-06512: at "SYS.UTL_TCP", line 246 ORA-06512: at "SYS.UTL_SMTP", line 127 ORA-06512: at "SYS.UTL_SMTP", line 150 ORA-06512: at "E1084448.SEND_TEST_MESSAGE", line 8 ORA-06512: at line 1 SQL> connect / as sysdba Connected.SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('TCP_SMTP_acl.xml','<Local Database Server>',24,26);
PL/SQL procedure successfully completed. SQL> commit; Commit complete.SQL> select a.acl, a.principal, a.privilege, a.is_grant, b.host, b.lower_port, b.upper_port
2 from dba_network_acl_privileges a, dba_network_acls b 3 where a.acl = b.acl 4 /ACL PRINCIPAL PRIVILE IS_GR HOST LOWER_PORT UPPER_PORT ---------------------------------------- -------------------- ------- ----- ---------------------------------------- ---------- ---------- /sys/acls/TCP_SMTP_acl.xml E1084448 resolve true <SMTP Relay Server> 24 26 /sys/acls/TCP_SMTP_acl.xml E1084448 connect true <SMTP Relay Server> 24 26 /sys/acls/TCP_SMTP_acl.xml E1084448 resolve true <Local Database Server> 24 26 /sys/acls/TCP_SMTP_acl.xml E1084448 connect true <Local Database Server> 24 26
SQL> connect e1084448/ou812hot4u Connected. SQL> select * from user_network_acl_privileges;HOST LOWER_PORT UPPER_PORT PRIVILE STATUS ---------------------------------------- ---------- ---------- ------- -------
<Local Database Server> 24 26 connect GRANTED
<SMTP Relay Server> 24 26 connect GRANTED
SQL> exec SEND_TEST_MESSAGE
BEGIN SEND_TEST_MESSAGE; END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "E1084448.SEND_TEST_MESSAGE", line 8
ORA-06512: at line 1
SQL> @mail_pkg.pkg
Package created.
Package body created.
declare
l_to varchar2(512);
l_sender varchar2(256) := 'oracle@<MyCompany>.com';
l_from varchar2(256) := 'Oracle Password Policy Minder
<oracle@<MyCompany>.com>';
l_text varchar2(32767);
l_smtphost varchar2(32) := '<SMTP Relay Server>';
l_temp varchar2(64);
begin
l_text := 'This email is being sent to you to inform you that your
database id ';
l_temp := 'E1084448'; -- Query id here
l_text := l_text||l_temp||' on database ';
select name into l_temp from v$database;
l_text := l_text||l_temp||' will expire in ';
l_temp := '10'; -- Query days here
l_text := l_text||l_temp||' days. '||chr(13)||chr(13)||'Please be
prepared to go to the password portal at '||chr(13)||chr(13)||
' http://<password server url>/UI/login.aspx '||chr(13)||chr(13)||'and
change your password';
l_to := '<e1084448 email address>';
mail_pkg.send
( p_sender_email => l_sender,
p_from => l_from,
p_to => mail_pkg.array( l_to ),
p_cc => mail_pkg.array( '<DBA Support Email>' ),
p_subject => 'INFO- Password Expiration Notice',
p_body => l_text );
end;
/
declare
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "E1084448.MAIL_PKG", line 50
ORA-06512: at line 18
SQL> show user
USER is "E1084448"
SQL> select
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('TCP_SMTP_acl.xml','E1084448','connect')
from dual;
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('TCP_SMTP_ACL.XML','E1084448','CONNECT')
-------------------------------------------------------------------------------
1
--
//www.freelists.org/webpage/oracle-l
