Hi there
I'm working on a project that uses DB triggers to audit changes made to the data on the db by the application.
For every table that is getting audited there is a journal table containing all the information of the original table plus additional auditing information.
Example:
Code:
USER_TABLE:
USER_ID
USER_NAME
USER_ADR
PERPETRATOR
USER_TABLE_JN:
JN_OPERATION
JN_ORACLE_USER
JN_DATETIME
JN_NOTES
JN_APPLN
JN_SESSION
USER_ID
USER_NAME
USER_ADR
PERPETRATOR
GROUP_TABLE:
GROUP_ID
GROUP_NAME
PERPETRATOR
GROUP_TABLE_JN:
JN_OPERATION
JN_ORACLE_USER
JN_DATETIME
JN_NOTES
JN_APPLN
JN_SESSION
GROUP_ID
GROUP_NAME
PERPETRATOR
The trigger copies the content of the changed row into the journal table adding the additional auditing information like operation_type (INS, UPD, DEL), timestamp, oracle_user, etc.
The state of the DB at any given time in the past can be reconstructed from the journal tables. The application can check who did which changes to which data on which date and what time.
The problem I have is getting the application_user into the PERPETRATOR column of many-to-many relationships.
For example if we have users and groups the join table would look like this:
Code:
USER_GROUP_JOIN_TABLE:
USER_ID
GROUP_ID
PERPETRATOR
USER_GROUP_JOIN_TABLE_JN:
JN_OPERATION
JN_ORACLE_USER
JN_DATETIME
JN_NOTES
JN_APPLN
JN_SESSION
USER_ID
GROUP_ID
PERPETRATOR
The mapping for hibernate would look something like this:
Code:
<hibernate-mapping>
<class name="User" table="USER_TABLE" dynamic-update="false" dynamic-insert="false">
<id name="id" column="USER_ID" type="java.lang.Long"/>
<property name="name" type="java.lang.String" update="true" insert="true" column="USER_NAME"/>
<property name="adr" type="java.lang.String" update="true" insert="true" column="USER_ADR"/>
<property name="perpetrator" type="java.lang.String" update="true" insert="true" column="PERPETRATOR"/>
<set name="groups" table="USER_GROUPS_JOIN_TABLE" lazy="false" inverse="false" cascade="none" sort="unsorted">
<key column="USER_ID"/>
<many-to-many class="Group" column="GROUP_ID" outer-join="auto"/>
</set>
</class>
</hibernate-mapping>
Since join tables are not represented as java objects there is no (easy) way of setting the PERPETRATOR column on join tables.
Has anybody ever solved anything similar?
My idea to solve this problem would be to extend the AbstractCollectionPersister and write my own generateInsertRowString() and generateDeleteRowString() methods to insert the PERPETRATOR info. To use MyCollectionPersister instead of the default BasicCollectionPersister add persister="MyCollectionPersister" to the set tag:
Code:
<hibernate-mapping>
<class name="User" table="USER_TABLE" dynamic-update="false" dynamic-insert="false">
<id name="id" column="USER_ID" type="java.lang.Long"/>
<property name="name" type="java.lang.String" update="true" insert="true" column="USER_NAME"/>
<property name="adr" type="java.lang.String" update="true" insert="true" column="USER_ADR"/>
<property name="perpetrator" type="java.lang.String" update="true" insert="true" column="PERPETRATOR"/>
<set name="groups" table="USER_GROUPS_JOIN_TABLE" lazy="false" inverse="false" cascade="none" sort="unsorted" persister="MyCollectionPersister">
<key column="USER_ID"/>
<many-to-many class="Group" column="GROUP_ID" outer-join="auto"/>
</set>
</class>
</hibernate-mapping>
Is this the correct way to do it or is there a more elegant way?
Thanks for any input/comments.
Tom
Login
FAQ
Search
