Solved

Auditiing_Data.

Posted on 2012-03-23
8
259 Views
Last Modified: 2012-08-13
I want to use this sample trigger to audit iupdates and deletes to a table EMP.

So i create my new audit table with extra columns to know WHO, WHAT and WHEN.
I am logging in the whole record into this audit table.


As i am analyzing this, i think there might be something wrong with logging the values for those extra columns for UPDATE since i am copying the old values of the record. The user name, ip, terminal is not
what created the audited row. The user did the updates stored in the base table and not this audited row.

Is this correct and how do I associate the old audited row values with the original user info?

*******************************************************************
CREATE OR REPLACE TRIGGER TRG_EMP_AUDIT
 AFTER  DELETE OR UPDATE ON EMP_TAB
 FOR EACH ROW DECLARE
 
 v_operation   VARCHAR2(10) := NULL;
 v_user        VARCHAR2(20);
 v_timestamp   Date;
 v_ip_address  VARCHAR2(25),
 v_terminal    VARCHAR2(10);

BEGIN
 
 v_user := USERENV(user);
 v_timestamp := SYSDATE;
 v_ip_address := USERENV(ip_address);
 v_terminal := USERENV(terminal);
 
 IF UPDATING THEN
   v_operation := 'UPD';
  ELSE
   v_operation := 'DEL';
  END IF;
 
 
   INSERT INTO EMP_TAB_AUDIT (
    seq,
    operation,
    user
    timestamp,
    ip_address,
    terminal,
    empno,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno )
   VALUES (
     audit_seq.nextval,
     v_operation,
     v_user,
     v_timestamp,
     v_ip_address,
     v_terminal,
    :old.empno,
    :old.job,
    :old.mgr,
    :old.hiredate,
    :old.sal,
    :old.comm,
    :old.deptno);
   
  END;
0
Comment
Question by:sam15
  • 4
  • 4
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37759595
>> v_user := USERENV(user);

What version of Oracle are you running?  This does not even compile for me in 10g.  The syntax is incorrect.

USER is not a valid parameter for USERENV in 10g:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions208.htm

You should be using SYS_CONTEXT:
      SYS_CONTEXT('USERENV', 'SESSION_USER')

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176


As for the incorrect values:  Are you running a N-Tier client server app where users log into the application and the application uses a common user to log into the database?  Such as Peoplesoft?
0
 

Author Comment

by:sam15
ID: 37759745
It is 9i but dont worry about the user environment variables.
There is one web application and one client/server application that user the database. we also use one database account now so i need to capture the O/S or machine name.

But the issue is it seems i may have to end up adding those fields to the base table (not audit table) so it gets copied to the audit table with the original record because iam copying the old values. If i am copying the NEW values it would work this way but then there are other issues is that i have to audit INSERTs and copy all the records in the existing table.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37759763
I do not understand the new versus old issue.   For example,on update, if the env can caputure the user ip address, if the column is in the origianl table or the audit table shold not matter.  Can you explain a little more?
0
 

Author Comment

by:sam15
ID: 37760663
What i am saying is that let us say USER JOHN updates Record 1 in Table 1.
Now the trigger will copy old data for Record 1 to  EMP_AUDIT table. The new/current record is stored in EMP base table.

All the ENV variables i store in audit table (i.e ipaddress, machine name, OS username) in the audit table do not really belong to the record stored in audit table. These belong to the new record in base table.

If i  want to store the OLD in audit table and all these ENV variables it means I have to add those fields to the original EMP table so the trigger copies those with each record and I need a trigger on the base table to update those values in the base table.

Do you see the issue now?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37766018
Thanks, I understand now.

I can see two viable options and one ugly one:
1: create a new table to capture the user that 'owns' the current row and keep that current with another trigger or add the 'last_updated' to the table itself.
or
2: change the way you read your audit records and keep everything the way you have it.  The audit table would say: user john updated this row from their '/original values of <old values> to what they currently are.

The ugly possibility:
change the audit.  Create an original state/sopy of the existing table.  Then on the trigger, only capture 'new' values since the 'old' will be the previous audit row.
0
 

Author Comment

by:sam15
ID: 37766396
On option #1 why create another table, you can add the audit columns (ip, machine name, updated_by) to the base table and it will be copied to the audit table with every row. But i need to add these audit columns to every base table plus i need a trigger to populate so ther is some work involved.

On option#2, i decide to go with the shadow table approach and copy the entire record. There are 100 column tables and this is the easiest instead of writing ton of code to derive the chanegs only with every DML.

On Option#3 (the ugly), i was thinking about this and i think i will do this.
I am not sure why you are calling this ugly. The only draw is "storage" and i do not have millions of records. They are pretty small tables.
That way i will have all the history data in one table instead of joining to the base table for the current one. basically I want to audit INSERT (capture :NEW) and audit UPDATE capture :OLD) and audit DELETE (capture :OLD).

Is #3 bad solution if you dont have ton of data? The only disadvantage is duplicate row in audit table.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37766441
#1:  If you can add columns to the base table, then this would be my choice.  The problem wit messing with base tables is it can break code.  If you had 'bad' programmers and they did things like:  select * from emp into id,name;  and you add a new column, the code will break since the INTO doesn't have the new columns.

#2:  It appears you already understand the problems here.

#3:  I guess it depends on how much of your data is updated over time.  For my system, very few updates occur.  Mostly inserts.  Why duplicate an entire table for less than 1% total updates.  My update 'audits' are rare.

If you will liekly have a high percentage, then it might not be an issue for you.

Then main issue is reporting on 'changes'.  Since the audit table has an initial copy, you will need to filter out actual 'changes' (likely the username is NOT NULL) but you neeed to account for it.
0
 

Author Comment

by:sam15
ID: 37769375
for reporting, I am thinking of creating a VIEW with LAG() function that comapares each row to the previous one and it is same value it would show NULL and if value changed it would show the new value.

I think that should be straighforward.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now