SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
e-mail Audit Alerts
Goto page Previous  1, 2
 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
e-mail Audit Alerts
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I think the message body is generated by the auditing code, but somehow it gets lost in transition between the database server - email server - email client. I'm still researching this topic.
Tue Jan 10, 2012 6:05 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Hi, the issue might be caused by certain incompatibility between Oracle's UTL_SMTP package, the format of audit message sent using that package and your email server, in the end leading to loss of the message body


Please try the following on your test Oracle server. Locate DB_AUDIT.SP_AUDIT_SENDMAIL stored procedure. Replace the body, I mean all text between BEGIN...END tags including BEGIN and END with the code below

Code:
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''' || mail_server || '''';
   UTL_MAIL.SEND(
            sender => sender,
            recipients => recipient,
            cc => cc,
            subject => subject,
            message => message,
            mime_type => 'text; charset=us-ascii'
   );
EXCEPTION
   WHEN OTHERS THEN
       dbms_output.put_line('Errors sending email from DB Audit trigger');
END;


You will also need to execute once
GRANT EXECUTE ON UTL_MAIL TO DB_AUDIT;

Please try that and let us know if that resolves the issue.
Wed Jan 11, 2012 9:53 am View user's profile Send private message
tnazz



Joined: 28 Feb 2011
Posts: 11
Country: United States

Post Reply with quote
I'm sorry about the delay in pursuing this issue, but I had to refresh our test database and then re-set up DB_Auditing.
I modified the SP_AUDIT_SENDMAIL procedure with the text and no messages were sent. It looks like the connection to the e-mail server was severed.
I replaced the original text in the procedure and the empty messages were again being sent.
Thu Jan 19, 2012 6:23 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
If you have a SQL editor that can display DBMS_OUTPUT messages (for example if you use SQL*Plus or Oracle SQL Developer), add the following to the code

Code:

BEGIN
   dbms_output.put_line('Message text: [' || nvl(message, 'NULL') || ']');

   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''' || mail_server || '''';
   UTL_MAIL.SEND(
            sender => sender,
            recipients => recipient,
            cc => cc,
            subject => subject,
            message => message,
            mime_type => 'text; charset=us-ascii'
   );
EXCEPTION
   WHEN OTHERS THEN
       dbms_output.put_line('Errors sending email from DB Audit trigger');
END;



Update SP_AUDIT_SENDMAIL procedure and then from the editor use regular SQL UPDATE command to change some data in one of the audited tables. This should make the audit code to invoke SP_SEND_MAIL procedure and that in turn should print to DBMS_OUTPUT the text of email message. Please let us know what you get in the output.

Just in case… here is how to use enable DBMS_OUTPUT in SQL Developer http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch9.htm and in SQL*Plus http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm
Fri Jan 20, 2012 1:13 am View user's profile Send private message
tnazz



Joined: 28 Feb 2011
Posts: 11
Country: United States

Post Reply with quote
From SQL DEveloper, I get this message when I run the latest script.

Connecting to the database Test.
Message text: [NULL]
Errors sending email from DB Audit trigger
Process exited.
Disconnecting from the database Test.


I get this message when I run the original script or the first modified script.

Connecting to the database Test.
Errors sending SMTP mail message from DB Audit trigger
Process exited.
Disconnecting from the database Test.

Mon Jan 23, 2012 10:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I've got some updates on this issue, but before I can say if they are applicable I need to check error error code returned with the message.

Please modify the code and add SQLERRM to the last dbms_output.put_line line, execute the code and let us know what's printed to the output.


Code:
BEGIN
   dbms_output.put_line('Message text: [' || nvl(message, 'NULL') || ']');

   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''' || mail_server || '''';
   UTL_MAIL.SEND(
            sender => sender,
            recipients => recipient,
            cc => cc,
            subject => subject,
            message => message,
            mime_type => 'text; charset=us-ascii'
   );
EXCEPTION
   WHEN OTHERS THEN
       dbms_output.put_line('Errors sending email from DB Audit trigger. ' || SQLERRM );
END;

Sat Feb 25, 2012 12:33 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.