ORA-24247: network access denied by access control list (ACL)
- From: "Patterson, Joel" <Joel.Patterson@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Feb 2013 13:03:30 -0500
I have three examples of trying to send mail via UTL_MAIL. I wish the code
block to run from a stored procedure as someone other than SYS.
Does anyone know why the stored procedure fails if run by DBMON? Has anyone
run across this already?
The analyst working the SR has filed a bug for oracle version 11.2.0.3.3 on
Solaris 10 - with the caveat that it could be the code block. I instinctively
question this, but now the solution is in indefinite limbo.
The anonymous block succeeds as shown in #1, so one believes that the Access
Control Lists are set up. UTL_MAIL has been granted directly to DBMON.
I thought at first that it was the 'execute immediate alter session' statement,
but that succeeds in a stored procedure by itself as shown in #2, and it
executes without errors as user SYS -- #4. This leaves #3.
Thank you,
1. ----------- As user anonymous block succeeds.
DBMON @ lawdev90> alter session set smtp_out_server = 'jaxsmtp01.crowley.com';
declare
sender VARCHAR2(30) := 'DBAGROUP@xxxxxxxxxxx';
ccrecipient VARCHAR2(240) := NULL;
bccrecipient VARCHAR2(240) := NULL;
subject VARCHAR2(80) := 'Purge Audit Data: ';
message VARCHAR2(1200) := 'UTL MAIL MESSSAGE';
header VARCHAR2(120) := 'UTL MAIL HEADEER';
mime_type VARCHAR2(30) := 'text; charset=us-ascii';
priority NUMBER := 3;
recipient VARCHAR2(30) := 'joel.patterson@xxxxxxxxxxx';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com';
begin
UTL_MAIL.send( sender => sender, recipients => recipient, cc => ccrecipient,
bcc => bccrecipient, subject => subject, message => message,
mime_type => mime_type, priority => priority);
end;
/
PL/SQL procedure successfully completed.
2. ----------- execute immediate by itself succeeds inside a stored
procedure as user.
DBMON @ lawdev90> l
1 create or replace procedure altertest as
2 smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com';
3 BEGIN
4 EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server =
"'||smtp_out_server||'"';
5* END;
DBMON @ lawdev90> exec altertest
PL/SQL procedure successfully completed.
3. ----------- code block as stored procedure fails as user
DBMON @ lawdev90> create or replace procedure purge_test_with_exec_immed as
sender VARCHAR2(30) := 'DBAGROUP@xxxxxxxxxxx';
ccrecipient VARCHAR2(240) := NULL;
bccrecipient VARCHAR2(240) := NULL;
subject VARCHAR2(80) := 'Purge Audit Data: ';
message VARCHAR2(1200) := 'UTL MAIL MESSSAGE';
header VARCHAR2(120) := 'UTL MAIL HEADEER';
mime_type VARCHAR2(30) := 'text; charset=us-ascii';
priority NUMBER := 3;
recipient VARCHAR2(30) := 'joel.patterson@xxxxxxxxxxx';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com';
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server =
"'||smtp_out_server||'"';
UTL_MAIL.send( sender => sender, recipients => recipient, cc => ccrecipient,
bcc => bccrecipient, subject => subject, message => message,
mime_type => mime_type, priority => priority);
END;
/
Procedure created.
DBMON @ lawdev90> exec purge_test_with_exec_immed
BEGIN purge_test_with_exec_immed; END;
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at "DBMON.PURGE_TEST_WITH_EXEC_IMMED", line 14
ORA-06512: at line 1
4. ----------- same code block succeeds as stored procedure for SYS
SYS AS SYSDBA @ lawacc90> create or replace procedure
purge_test_with_exec_immed as
sender VARCHAR2(30) := 'DBAGROUP@xxxxxxxxxxx';
ccrecipient VARCHAR2(240) := NULL;
bccrecipient VARCHAR2(240) := NULL;
subject VARCHAR2(80) := 'Purge Audit Data: ';
message VARCHAR2(1200) := 'UTL MAIL MESSSAGE';
header VARCHAR2(120) := 'UTL MAIL HEADEER';
mime_type VARCHAR2(30) := 'text; charset=us-ascii';
priority NUMBER := 3;
recipient VARCHAR2(30) := 'joel.patterson@xxxxxxxxxxx';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com';
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server =
"'||smtp_out_server||'"';
UTL_MAIL.send( sender => sender, recipients => recipient, cc => ccrecipient,
bcc => bccrecipient, subject => subject, message => message,
mime_type => mime_type, priority => priority);
END;
/
Procedure created.
SYS AS SYSDBA @ lawacc90> exec purge_test_with_exec_immed
PL/SQL procedure successfully completed.
Joel Patterson
Senior Oracle Database Administrator | Information Technology
Joel.Patterson@xxxxxxxxxxx
Office: 904-727-2546
Crowley Maritime Corporation / www.crowley.com
9487 Regency Sq. Blvd. | Jacksonville, FL 32225
--
//www.freelists.org/webpage/oracle-l
Other related posts:
