Table tracking 1

[amberlynn]

Is it possible to use some type of tracking that would show daily changes made to a given table, and the user that made those changes?

Cheers,
Amber

 

[Thargy]

Amberlyn,

Oracle has a facility called Fine Grained Auditing which can be enabled by your DBA team and will do what you need.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html

refers to this, but then goes on to use 10g. However, the facility definitely does exist in 9i.

Google is your friend on this occasion.

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Yes, Amber...I create a TRIGGER on each table which I want to audit. The trigger writes to an audit table WHICH table changed, WHICH row's column changed, WHAT the pre-change value was and the new value, WHO made the change, and WHEN the change occurred.

Here are components I use for auditing:

Section 1 -- Audit table:

connect system/<password>@<instance TNS alias>
create table auditor
	(table_name		varchar(30)
	,action_datetime	date
	,perpetrator		varchar(200)
	,action_type		varchar(10)
	,pk			varchar(100)
	,which_column		varchar(30)
	,old_value		varchar(4000)
	,new_value		varchar(4000)
	)
/
grant select, insert on auditor to public;

Section 2 -- Generic user-defined Audit procedure:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
connect system/<password>@<instance TNS alias>
create or replace view system.sessions as select * from sys.v_$session;
grant select on system.sessions to public;
Create or Replace procedure system.generic_audit
  (table_name      in varchar2,
   action_type     in varchar2,
   updated_column  in varchar2, 
   pk              in varchar2, 
   old_value       in varchar2, 
   new_value       in varchar2)
  is
	userinfo	varchar2(200);	
  begin
     IF (old_value is null and new_value is not null) or
	(old_value is not null and new_value is null) or
	(old_value <> new_value) THEN
	select osuser||' on '||machine||'(as '||user||')' into userinfo
		from system.sessions where audsid = userenv('sessionid');
        INSERT INTO system.auditor
               VALUES (table_name, SYSDATE,USERINFO,action_type, 
               pk,updated_column,old_value,new_value);
     END IF;
  END;
/
GRANT execute on generic_audit to public;

Section 3 -- Sample TRIGGER to insert into AUDITOR table:

Create or Replace TRIGGER audit_s_emp_changes
Before insert or update or delete on test.s_emp FOR EACH ROW
BEGIN
	if inserting then
		generic_audit('test.s_emp','INSERT','All columns',:new.id,'inserting','INSERTING');
	elsif deleting then
		generic_audit('test.s_emp','DELETE','All columns',:old.id,'deleting','DELETING');
	else
		generic_audit('test.s_emp','UPDATE','LAST_NAME',:old.id,:old.last_name,:new.last_name);
		generic_audit('test.s_emp','UPDATE','FIRST_NAME',:old.id,:old.FIRST_name,:new.FIRST_name);
		generic_audit('test.s_emp','UPDATE','SALARY',:old.id,:old.SALARY,:new.SALARY);
	end if;
end;
/

The above example audits INSERTs, DELETEs, and UPDATEs to LAST_NAME, FIRST_NAME, and SALARY on my s_emp table.

It will take some time to look over and understand the code, above, but if you have questions, please post them.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amberlynn]

Thanks Mufasa,
I'm trying to understand exactly what you are doing, and everything is clear to me, right up to the Trigger.
What is the new.id and old.id ?
Cheers,
Amber

 

[SantaMufasa]

When you create/use ROW-LEVEL triggers (i.e., "FOR EACH ROW"), Oracle creates a ":new" buffer and an "ld" buffer. The ":new" buffer contains a post-modification image of every column in the current row, while the "ld" buffer contains a pre-modification image of every column in the current row. (For INSERTs, the "ld" buffer is NULL; for DELETEs, the ":new" buffer is NULL).

So, each column in the current row has a ":new.<column>" image and an "ld.<column>" image.

Does that answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amberlynn]

Yes, that cleared it up for me.
This works excellent!
Exactly what I wanted!!
Cheers,
Amber

 

[sergelevesque]

I use this solution in my case and I made a few change it work like a charm

http://astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11090

Serge Levesque