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); beginl_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 herel_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