RE: Oracle 11G network ACL not working - ORA-24247: network access denied by access control list (ACL)
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "'m.haddon@xxxxxxxxx'" <m.haddon@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 Jan 2011 09:05:00 -0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 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 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 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
