Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

auditing sql statements

User_DLBYEJan 8 2013 — edited Jan 13 2013
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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 10 2013
Added on Jan 8 2013
14 comments
359 views