ESPIONAGE!!

[joeythelips]

Hi,

my manager has asked my to check what user of our oracle system updated some customer records.
this is all the info he gave me.
I know its to do with database auditing.
my table name is cust.
how can i check?
i also have the customer details

Thanks,

Joe

 

[carp]

The problem you're likely to run into is the fact that Oracle Auditing will tell you who updated the table, but it won't tell you which records or even how many records were updated. Nor will it tell you what the updates were.

If this is the kind of information you are supposed to find, you would need to build a table to hold your specific audit information and a trigger on your cust table that will record the user name, the record ID, and the before and after values of each field updated.

If, on the other hand, you just need a list of people that updated your cust table, then Oracle auditing will do the trick:

AUDIT update ON cust;

Of course, you also have to turn auditing on and this WILL have an effect on your overall DB performance.

In short, it sounds to me like your manager has to give you some more specific requirements and be made aware of the ramifications.

 

[sfvb]

Joe,
You may want to add a column for the username into the cust table.

At work, our DBA added a not null column for user_name and machine_id, for every table that stores data where money exchanges hands. There may be a more efficient approach, but that is how our DBA handled it. We also have a table to record the original info, for any rows that were deleted from these tables. None of our end users have access to this table.

Steve

 

[ee384]

I think you can create a trigger that will fire when INSERT, UPDATE, and DELETE on customer and customer detail tables. In your trigger, you can log the user_id and time.

 

[alexhu]

Unless you have auditing on now - you are too late - the suggestions above will help in the future

Alex