RE: Oracle 11G network ACL not working - ORA-24247: network access denied by access control list (ACL)
Date: Thu, 27 Jan 2011 09:05:00 -0800
Message-ID: <D29F9902E534D5478F2E83FD6A44B30625F1273D87_at_mail02.mba.xifin.com>
http://oraclehack.blogspot.com/2010/10/ora-24247-network-access-denied-by.html
Maybe this might help.
Michael Dinh
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mike Haddon
Sent: Wednesday, January 26, 2011 5:25 PM
To: oracle-l
Subject: Oracle 11G network ACL not working - ORA-24247: network access denied by access control list (ACL)
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 a40 SQL> select acl, principal, privilege, is_grant fromdba_network_acl_privileges;
ACL PRINCIPALPRIVILE IS_GR
---------------------------------------- ------------------------------ ------- -----connect true
/sys/acls/TCP_SMTP_acl.xml E1084448
resolve true
/sys/acls/TCP_SMTP_acl.xml E1084448
SQL> column principal format a20
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
SQL> connect e1084448/<password>
Connected.
SQL> select * from user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILESTATUS
---------------------------------------- ---------- ---------- ------- ------- <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 PRIVILESTATUS
---------------------------------------- ---------- ---------- ------- ------- <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> _at_mail_pkg.pkg
Package created.
Package body created.
declare
l_to varchar2(512);
l_sender varchar2(256) := 'oracle_at_<MyCompany>.com';
l_from varchar2(256) := 'Oracle Password Policy Minder
<oracle_at_<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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 27 2011 - 11:05:00 CST