Table auditing in sensitive environment (including tracking user/ip data from web based app)

Talk Type: 
45 Minute Talk
Best practices
Business and Case studies
Technical Level: 
Creative Commons - No Commercial or Modification

Working in financial services industry, it's important to track data changes automagically...without conscious effort. It's also important to be able to attribute those changes to specific users (and in some cases IP addresses). Using a database for combined purposes (sitting behind web application accessible to thousands of different users) as well as for a plethora of batch processes and even individual database users with ability to manipulate data makes this difficult to do.

For the past 7 years, we have utilized a useful technique to log ALL changes to tables (updates, deletes, inserts) including the user who performed, IP address it was performed from (even if external user via web application and a database connection pool), table, column changes and specific id elements that tie to higher level constructs for useful reporting.

Using a combination of techniques including automatic catalog based trigger functions, use of temporary tables (manipulated via connection pool functionality) and some other nifty tidbits, we have managed to log some 2 billion rows of infinitely useful audit logs that have been used time and time again to not only research who did what when, but also to reconstruct 'oops' moments right down to the smallest details (and reverse them). While our database only contains only a handful of actual database users, the logs contain information tied to all of our external user ids as well. This is critical in a whole range of audit, research, compliance and certification scenarios.

No developer interaction is required. Triggers are updated whenever we do DDL changes and new tables/columns simply 'start' auditing. Auditing is automatic and not easily circumventable. While higher-level audit records can and are used for a macroscopic record trail, these low level results provide a safety net we have found to be invaluable.

While this technique (and it's necessary evil overhead) isn't right for every scenario, it has merits in certain industries and applications.