0

In Production,as per user request,developer will update/insert/delete the data in one or more tables, and others are not aware of these changes (like which tables are updated and how many row got updated) ,and they don't have any idea about How to Track table data changes.

I am planing to create a script to track the changes(update/insert/delete) and insert the new and old data one new table (data_fix_history) table with below details.

  1. ticket no
  2. developer name
  3. old row
  4. new row if update
  5. update time

Can you please suggest the how to achieve this requirement?

Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
user3733693
  • 1
  • Add audit tables, create triggers for each table. http://viralpatel.net/blogs/auditing-dml-oracle/ FGA is another way. [Oracle Fine Grained Auditing for entire schema](http://stackoverflow.com/q/13257086) – Sathyajith Bhat Jun 12 '14 at 11:21
  • Thanks for ur response.As part of requirement maximum they will update/insert/delete just 3-8 tables only.for this we won't like to touch existing triggers.our plan is creating one procedure/function by passing parameter 'DATA_FIX' along with other parameters and then we like to 1) if udpate then insert column old and new values into datafix_table,2) if insert, insert new values into datafix_table, 3) if delte insert only old values into datafix_table. – user3733693 Jun 13 '14 at 00:46
  • 2
    I suggest you implement a protocol where developers are not allowed to manipulate production data. Instead, require that they write a "one-shot" script, migrate this to production per your normal code migration process (with appropriate managerial blessing, etc), and have your production control people run this code. This will not only keep your auditors happy but it may also save your developers a fair amount of grief if one of their "user-requested" changes is later disavowed in the midst of a crisis. Share and enjoy. – Bob Jarvis - Слава Україні Jun 13 '14 at 02:40
  • @BobJarvis, The normal way may not always be possible. For example when the security requirements of the data exceeds that of the source code. For example an address update. – Shannon Severance Jun 15 '14 at 22:08
  • @ShannonSeverance - so you're saying that if "the security requirements of the data exceeds that of the source code", developers have to update the data directly? I think I'll run that past our security director tomorrow to get his take on it. I'll also run that past my wife who for business reasons is a lot more security-conscious than I am - just because I like to hear her laugh. :-) – Bob Jarvis - Слава Україні Jun 15 '14 at 22:32
  • @BobJarvis, no I am not saying that, if a company has a division of responsibilities, that should not be broken. But I have not met a source code control system where it would make sense to store an update script with customer confidential data. This sort programmer driven change is fall of problems. – Shannon Severance Jun 15 '14 at 22:41

0 Answers0