1

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?

Improve this question
1
  • Hibernate Envers? This is app layer though
    – Neil McGuigan
    Commented Jan 21, 2016 at 20:27

1 Answer 1

Reset to default
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?

Improve this answer
3
  • Triggers don't satisfy the "R" in "CRUD operations against all tables in a schema"
    – mustaccio
    Commented Jan 17, 2019 at 13:36
  • @mustaccio good point. So it looks like there is no generic, standard solution! what a pity.
    – filiprem
    Commented Jan 18, 2019 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
    Commented Jan 18, 2019 at 13:58

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