Solved

Auditiing_Data.

Posted on 2012-03-23
8
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 77

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 77

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 
LVL 77

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 77

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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