[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Auditiing_Data.

Posted on 2012-03-23
8
Medium Priority
?
268 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

656 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