FW: ACL Lists and UTL_MAIL FAIL when executed from a stored procedure.
Date: Mon, 11 Mar 2013 14:45:11 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F714F76A20D1_at_EIHQEXVM2.ei.local>
Hello list,
I have an outstanding Service Request with Oracle - who has sent this issue to development stating that this is a possible bug. I would like to verify that this issue does not work for anyone that attempts it. I have received feedback from some that it would work - but not from anyone who specifically stated that they 'executed' a stored procedure that would send the email.
The issue is that the 'last line' executing the code as a stored procedure -- 'only' works as user SYS, and in this example does not work for the newly created user ACLTEST.
I created all the steps below needed to create a user that can send a sample email from oracle 11g, (11.2.0.3.3, Solaris 5.10) successfully. I bolded eight lines below that contain the smtp_out_server, host, sender, and recipient that would need to change for someone to duplicate this.
I was wondering if some of the listers would execute the below lines to confirm that this is an issue or can specifically show that it works from a stored procedure?
All the below lines should all succeed except for the very last line! (exec purge_test_with_exec_immed)?
- ***********************************************************************************************
- AS SYS:
alter system set smtp_out_server = 'jaxsmtp01.crowley.com';
show parameter smtp_out_server
create user ACLTEST identified by ACLIT
default tablespace USERS temporary tablespace TEMP
quota unlimited on USERS;
grant create session to ACLTEST;
grant DBA to ACLTEST;
revoke UNLIMITED TABLESPACE from ACLTEST;
grant select on dba_tables to ACLTEST;
grant execute on utl_mail to ACLTEST;
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'utl_mail_access.xml'
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'utl_mail_access.xml',
description => 'Permission to access e-mail server.',
principal => 'ACLTEST',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'utl_mail_access.xml',
host => 'jaxsmtp01.crowley.com',
lower_port => 80,
upper_port => NULL
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'utl_mail_access.xml',
principal => 'ACLTEST',
is_grant => TRUE,
privilege => 'resolve',
start_date => SYSTIMESTAMP,
end_date => NULL
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'utl_mail_access.xml',
principal => 'ACLTEST',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL
);
COMMIT;
END;
/
********************************************
AS ACLTEST:
declare
sender VARCHAR2(30) := '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 HEADER'; mime_type VARCHAR2(30) := 'text; charset=us-ascii'; priority NUMBER := 3;
recipient VARCHAR2(30) := 'jpatterson_at_entint.com'; 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;
/
create or replace procedure purge_test_with_exec_immed as
sender VARCHAR2(30) := 'DBAGROUP_at_crowley.com';
ccrecipient VARCHAR2(240) := NULL;
bccrecipient VARCHAR2(240) := NULL;
subject VARCHAR2(80) := 'Purge Audit Data from stored procedure: ';
message VARCHAR2(1200) := 'UTL MAIL MESSSAGE FROM STORED PROCEDURE';
header VARCHAR2(120) := 'UTL MAIL HEADER FROM STORED PROCEDURE';
mime_type VARCHAR2(30) := 'text; charset=us-ascii';
priority NUMBER := 3;
recipient VARCHAR2(30) := 'jpatterson_at_entint.com';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com';
BEGIN
-- EXECUTE IMMEDIATE 'ALTER SYSTEM 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;
/
exec purge_test_with_exec_immed
Joel Patterson
Senior Oracle Database Administrator
jpatterson_at_entint.com<mailto:jpatterson_at_entint.com>
Office: 904 928-2790
Enterprise Entegration Corporation / www.entint.com<http://www.entint.com>
7601 Centurion Parkway | Jacksonville, FL 32256
-- Joel Patterson Sr. Database Administrator | Enterprise Integration Phone: 904-928-2790 | Fax: 904-733-4916 www.entint.com<http://www.entint.com/> [http://i1202.photobucket.com/albums/bb367/Entint/signaturev61.jpg]<http://www.entint.com/> [http://i1202.photobucket.com/albums/bb367/Entint/th_FaceBook1.jpg]<http://www.facebook.com/pages/Enterprise-Integration/212351215444231> [http://i1202.photobucket.com/albums/bb367/Entint/th_Twitter1.jpg] <http://twitter.com/#!/entint> [http://i1202.photobucket.com/albums/bb367/Entint/th_LinkedIn1.jpg] <http://www.linkedin.com/company/18276?trk=tyah> [http://i1202.photobucket.com/albums/bb367/Entint/th_YouTube1.jpg] <http://www.youtube.com/user/ValueofIT> This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1] -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 11 2013 - 19:45:11 CET
