1

This question already has an answer here:

Background

Various databases have custom implementations for auditing:

Problem

A number of problems exist:

  • There is no consistency (different commands, different log formats, etc.)
  • There does not appear to be an ANSI SQL standard
  • Some of the audits log to files, rather than a table or tables

Question

What ANSI SQL table auditing mechanism is:

  • database agnostic; and
  • tracks changes that can be queried using SQL (i.e., an audit table, not a log file)?

If no ANSI SQL standard for audit tables exists, then what alternatives exist for database-agnostic auditing (e.g., create a custom audit table) of CRUD operations against all tables in a schema?

marked as duplicate by Paul White Jan 22 '16 at 5:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Hibernate Envers? This is app layer though – Neil McGuigan Jan 21 '16 at 20:27
2

Triggers make an auditing mechanism, that satisfies your criteria.

  • they are present in the ISO/ANSI standard (ISO 9075)
  • they can be used to track row-level changes
  • they can save data to tables, hence can be queried using SQL

Only thing I do not understand in your question is "database agnostic" - what does it mean, precisely?

  • Triggers don't satisfy the "R" in "CRUD operations against all tables in a schema" – mustaccio Jan 17 at 13:36
  • @mustaccio good point. So it looks like there is no generic, standard solution! what a pity. – filiprem Jan 18 at 11:34
  • There can be no generic solution, as the point where you can intercept incoming statements and their execution status depends very much on the architecture of each particular DBMS. There are some solutions that attempt to be generic, such as Guardium Data Protection for Databases, but they still come with DBMS-specific access modules and obviously don't work with all DBMSes, only those they chose to support. – mustaccio Jan 18 at 13:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.