auditing sql statements
hii everybody,
i wrote a trigger to audit a single client who logins to database as 'APPS' user using TOAD and SQLPLUS,what sql statements client is running on database.the problem with this trigger is it is not excluding other clients and how can i get client machine name,client ip address,client osuser name in the same trigger?.your help is highly appreciated.
thanks.
DB VERSION:10.2.0.3
platform linux:redhat 5.7
CREATE OR REPLACE TRIGGER fga_logon_audit_trig
AFTER LOGON
ON DATABASE
DECLARE
V_program varchar2(120);
V_osuser varchar2(120);
cnt integer;
CURSOR get_tab IS
SELECT table_name FROM sys.dba_tables where owner='APPS';
v_tabname get_tab%rowtype;
BEGIN
IF NOT get_tab%ISOPEN THEN
OPEN get_tab;
END IF;
SELECT upper(program) into V_program FROM v$session
where audsid=sys_context('USERENV','SESSIONID');
SELECT upper(osuser) into V_osuser FROM v$session
where audsid=sys_context('USERENV','SESSIONID');
IF V_program in ('TOAD.EXE','SQLPLUS.EXE') and V_osuser='XYZ'
THEN
LOOP
FETCH get_tab INTO v_tabname;
EXIT WHEN get_tab%NOTFOUND;
dbms_output.put_line(v_tabname.table_name);
select count(*) into cnt from dba_audit_policies where object_schema='APPS' and object_name=v_tabname.table_name;
if cnt=0 then
dbms_fga.add_policy (
object_schema => 'APPS',
object_name => v_tabname.table_name,
policy_name => 'AUDIT_' || v_tabname.table_name,
audit_column => NULL,
audit_condition => NULL,
statement_types=> 'SELECT,INSERT,UPDATE,DELETE'
);
end if;
END LOOP;
END IF;
END;