e-mail Audit Alerts

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.

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.

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.

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

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.

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;