Home -> Mailing Lists -> Oracle-L -> RE: ORA-24247: network access denied by access control list (ACL)

RE: ORA-24247: network access denied by access control list (ACL)

From: Patterson, Joel <Joel.Patterson_at_crowley.com>
Date: Wed, 20 Feb 2013 13:51:24 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA2321EF4605_at_JAXMSG01.crowley.com> 





select * from sys.dba_network_acl_privileges order by principal, privilege

ACL                                                ACLID                            PRINCIPAL       PRIVILE IS_GR INVER START_DATE END_DATE
-------------------------------------------------- -------------------------------- --------------- ------- ----- ----- ---------- ----------


/sys/acls/utl_mail_access.xml                      D254672416A906C3E0440021287E56B4 DBMON           connect true  false

/sys/acls/oracle-sysman-ocm-Resolve-Access.xml     D14BA62C9B89197EE0440021287E56B4 ORACLE_OCM      resolve true  false


SELECT host, acl, lower_port, upper_port FROM DBA_NETWORK_ACLS;
HOST                           ACL                                                          LOWER_PORT UPPER_PORT
------------------------------ ------------------------------------------------------------ ---------- ----------
jaxsmtp01_at_crowley.com          /sys/acls/utl_mail_access.xml
localhost                      /sys/acls/oracle-sysman-ocm-Resolve-Access.xml





DBMON  _at_ lawdev90> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('jaxsmtp01_at_crowley.com'));



COLUMN_VALUE





jaxsmtp01_at_crowley.com


*.com


*


DBMON  _at_ lawdev90> SELECT DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('jaxsmtp01_at_crowley.com') domain_level
  2           FROM DUAL;


DOMAIN_LEVEL





           2




Joel Patterson


Database Administrator


904 727-2546


From: rjamya [mailto:rjamya_at_gmail.com]
Sent: Wednesday, February 20, 2013 1:35 PM
To: Patterson, Joel


Cc: oracle-l_at_freelists.org


Subject: Re: ORA-24247: network access denied by access control list (ACL)



Is the ACL privilege granted via role? or granted directly to dbmon? you need direct privs.



Raj



On Wed, Feb 20, 2013 at 1:03 PM, Patterson, Joel <Joel.Patterson_at_crowley.com<mailto:Joel.Patterson_at_crowley.com>> wrote:
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 _at_ lawdev90> alter session set smtp_out_server = 'jaxsmtp01.crowley.com<http://jaxsmtp01.crowley.com>';
declare


sender VARCHAR2(30) := 'DBAGROUP_at_crowley.com<mailto:DBAGROUP_at_crowley.com>';
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_at_crowley.com<mailto:joel.patterson_at_crowley.com>';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com<http://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  _at_ lawdev90> l


  1  create or replace procedure altertest as
  2  smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com<http://jaxsmtp01.crowley.com>';
  3  BEGIN



  4   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = "'||smtp_out_server||'"';
  5* END;



DBMON  _at_ lawdev90> exec altertest



PL/SQL procedure successfully completed.




3.      ----------- code block as stored procedure fails as user




DBMON _at_ lawdev90> create or replace procedure purge_test_with_exec_immed as
 sender VARCHAR2(30) := 'DBAGROUP_at_crowley.com<mailto:DBAGROUP_at_crowley.com>';
 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_at_crowley.com<mailto:joel.patterson_at_crowley.com>';
 smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com<http://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  _at_ 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 _at_ lawacc90> create or replace procedure purge_test_with_exec_immed as
 sender VARCHAR2(30) := 'DBAGROUP_at_crowley.com<mailto:DBAGROUP_at_crowley.com>';
 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_at_crowley.com<mailto:joel.patterson_at_crowley.com>';
 smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com<http://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 _at_ lawacc90> exec purge_test_with_exec_immed



PL/SQL procedure successfully completed.









Joel Patterson


Senior Oracle Database Administrator | Information Technology
Joel.Patterson_at_Crowley.com<mailto:Joel.Patterson_at_Crowley.com>
Office: 904-727-2546<tel:904-727-2546>


Crowley Maritime Corporation / www.crowley.com<http://www.crowley.com>
9487 Regency Sq. Blvd. | Jacksonville, FL 32225





--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


Received on Wed Feb 20 2013 - 19:51:24 CET












Original text of this message