Audit change in Java + Spring + Oracle application

Navigation
2

I have a task to change an audit implementation in an application in which I work.

The audit requirement is to know the history of changes, and the execution of certain tasks by a particular user. (Initial State -> Modification 1 -> ... -> Modification N)

The problem is that the current implementation saves the state of the serialized Java object in a CLOB field in the database, and thus we have some problems:

  • The application has been in production for more than 3 years, and several changes have been made over the course of of that time, and thus the Java Object was modified, but no maintenance was done on the database for the saved object to have those changes. (Eg a new field added in a class).
  • Reverting something to the "Home State", or returning some version in time, whose Java Object no longer reflects the running code is not an easy task.
  • The simple fact of wanting to know the contents of the Object in these conditions requires that I retrieve the SVN version of the class which the object was generated, and this can be difficult because I do not know exactly in which commit of code that object was saved.
  • It is impossible to view the contents of CLOB by simply querying the database.

    • Discussing with others, we come up with some possible implementations:

    • Serialize the Java object in JSON (which would easily discover the code version used by the number of JSON attributes, and it would be humanly possible to read the content in the database).
    • Another solution I found would be to use an audit framework that is easily integrable with Spring: Audit4J . It has a customizable layout, is also humanly readable, and has several features that are very interesting. audit4j

    I would like to know in this case what would be the best practices for performing an audit implementation that is: Humanly readable (database), low maintenance, and problems between code versions not so dramatic.     

    asked by anonymous 19.01.2015 / 13:46

    2 answers

    1

    Audit frameworks can be very useful at this time, but there are other ways to create an audit scheme. In the places where I worked, always fall into one of the three options below:

    • Bench Triggers: Create a "shadow" table, like the source table, and whenever there is a change in the data, the trigger saves the old data in the shadow. The good thing is that it would be transparent to day-to-day applications, but you would have to be careful when you maintain the live table.

    • Event Log: Create a database schema that historically saves the data. Something like an event table, which holds an ID that groups all the changed data. Example: Person table has five fields, when changed, creates an ID 1 event, with date, which contains 5 key value elements (column name, column value). This event log can be populated via trigger, or even via application.

    • Audit frameworks: there you have to see how each one behaves, and whether it fits your needs.

    29.01.2015 / 18:15
    1

    It has not been clear whether changes in object states should be identified. For example, who changed a particular attribute at any given time?

    My response will continue assuming you do not need this information (who changed it?), just knowing the old values, new values, and the change date.

    Once in a project, we created an architecture where no record was erased, but overwritten.

    All tables had a Boolean attribute hidden to determine whether a particular record was active on the system. And another attribute versao_anterior that references the ID of the previous state, if necessary.

    Example: a table to keep customer records, after entering the data of a customer we would have: 

    id | nome | telefone | versao_anterior | hidden | data_insercao |
1  | João | 99999999 | null            | false  | 09/02/2015

    After editing the data of the 1-John client, your table would read: 

    id | nome | telefone | versao_anterior | hidden | data_insercao |
1  | João | 99999999 | null            | true   | 09/02/2015
2  | José | 12345678 | null            | false  | 12/02/2015
3  | João | 88888888 | 1               | false  | 13/02/2015

    Notice that only João's phone changed from 02/02 to 02/02. In the meantime, another record was inserted in the database without any problem (id 2).

    In this way, for each instance of the client entity it is possible to trace all its states, their changes and the dates of each of them.

    Just go to listing the records while versao_anterior is different from null.

    This type of approach is useful when system data can not be lost.

    The disadvantage is:

    • is not stored from who started the change
    • Tables with multiple records that are changed at all times can become very large in a short time.

    I hope I have helped.

        
    13.02.2015 / 21:20
    UPDATE, field with placeholder query failed to execute How do I pass a function as a parameter in Action Script?