Oracle Database Discussions

hi.
i like to send audit information to particular mail account. but i have failed.
is any one can help me. i have writtten step by step what i have done for my work. my mail server work properly.

SQL> CONNECT SYS/PAS AS SYSDBA

after that i have created a procedure called "FGA_NOTIFY" to send mail WHEN user used SELECT command on sal column in emp table other than SCOTT. Then i have added a policy that is given belllow.

But The problem is now when sys user or any other user give command like


sql> select sal from scott.emp;

oracle server didnot send audit information mail to mail server. but
if i execute procedure "FGA_NOTIFY" independently , procedure send mail.

please help me. i have given procedure and
policy code down.



This is my policy
---------------------
BEGIN
DBMS_FGA.add_policy(
object_schema => 'SCOTT',
object_name =>'EMP',
policy_name => 'Example',
audit_condition => 'ENAME != USER',
audit_column => 'SAL',
handler_schema => 'SYS',
handler_module => 'FGA_NOTIFY');
END;


THIS IS MY PROCEDURE
----------------------

CREATE OR REPLACE PROCEDURE fga_notify (
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2)
AS
l_messege VARCHAR2 (32767);
l_mailhost VARCHAR2 (30) :='rmail';
l_mail_conn UTL_SMTP.connection;
l_from VARCHAR2 (30):= 'admin@rmail';
l_to VARCHAR2 (30):= 'admin@rmail';
BEGIN

l_messege :=
'User'||USER
||'successfully accessed'||Object_schema||'.'
||object_name||'at'
||TO_CHAR (SYSDATE, 'Month DD HH24 :MI:SS')
||'with this statement :"'
||SYS_CONTEXT ('userenv','currrent_sql')
||'"';

l_mail_conn :=UTL_SMTP.open_connection (l_mailhost,
25);
UTL_SMTP.helo (l_mail_conn, l_mailhost);
UTL_SMTP.mail (l_mail_conn, l_from);
UTL_SMTP.rcpt (l_mail_conn,l_to);
UTL_SMTP.DATA (l_mail_conn,
UTL_TCP.crlf||'subject: FGA Alert'
||UTL_TCP.crlf
||'To:'
||l_to
||UTL_TCP.crlf
||l_messege);
UTL_SMTP.quit (l_mail_conn);

EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.quit (l_mail_conn);
raise_application_error (-2000,'Failed due to the
following error'||SQLERRM) ;
END;
/

Message was edited by:
Md Ruhul Amin