Add option to create audit entries only on real data changes

[ogobrecht]

Hi Connor,

first of all: Thank you very much for this project. I like it.

You have thought about pretty much everything one can think of when needing such a tool - including an option to decide whether to save OLD and NEW values or only the OLD ones, which is the default.

My issue: minimize audit entries further by add only an entry when a real change occurs.

Background: Sometimes there are tables in a model that gets regular updates by a business process. For example, update every day via job all application users with the data from an LDAP server to deactivate inactive ones also in the application. Such processes normally do not check, if an update is necessary or not - they simply update the data. This can lead to many unwanted audit entries.

I can hear you say: Then please do the check in your business logic...

But when I do it there I need to do it possibly multiple times in different places...

So, the questions are:

• Do you think, it might be useful to add an option (defaults to false, g_only_changed_data?) which adds logic to check if we need an audit entry or not?
• Will this work with your bulk inserts?

Thank you again for your time on this project.

Best regards Ottmar

[connormcd]

Hi Ottmar, Thanks firstly for catching the varchar2(10).  Obviously I've never hit anyone with any large schema names :-) Whilst I like the idea of only logging a row if it constitutes a "genuine" change (at least as an option) - it does raise the question - should the general header record still be logged in such a circumstance? If no, then we've lost any record of even of the intent to change the data, but if yes, then you have the premise of a header record that appears to have no children (which could easily be interpreted as a data corruption in the audit). Curious on your thoughts on that. Cheers,Connor McDonald

[ogobrecht]

Hi Connor,

thanks for your quick answer.

I think for consistency the general header record should not be saved. I would instead add a new table (AUDIT_SUPPRESSED_UPDATE_ENTRIES?) with at least the columns TABLE_NAME, HOST, OS_USER, SUPPRESSED (primary key on all except the last one) and increase SUPPRESSED column value by the number of suppressed entries (for bulk updates you need only one update and need to count this in the bulk processing). Do it this way you can see that there are suppressed log entries and who is doing such updates while you save much space for auditing.

Does this makes sense? Let me know...

Best regards Ottmar

[connormcd]

Anything the "increases suppressed" suggests an update to me...and update means increased risk of contention. In particular, in a typical mid-tier environment, you'd expect HOST/OS_USER to be fixed for many incoming users.

But I'll give it some more thought.

Thanks

[ogobrecht]

Hi Connor,

that was only my first thought on the topic to not lose the information that there were updates without data impact. If you want to have it per end-user then you need to take more attributes into account, clear. If you do not care about the details and want only to see, that there were updates without effects then it might be ok to save fewer attributes. I think it depends on what you want to achieve and that can be difficult to figure out for all use cases of your tool.

For example in APEX I don't want to know which end-user was the one who did such updates - my main interest would be which code causes such updates and can we do better to prevent such updates at all. The end-user needs to use what is given in the app. And there is also the problem of tracking users in the time of GDPR. Maybe we should also add the columns MODULE and ACTION? APEX uses this nicely...

In the end, we must find the balance between "can be implemented and is useful for 80% of the people" and "will fit any use case" (which seems to be impossible).

Would be nice, if we can have a solution to the problem. The space savings can be huge - depending on how the system is used. And as a plus this would be a outstanding feature compared to other solutions.

Best regards Ottmar

[connormcd]

MODULE and ACTION are already captured in AUDIT_HEADER.

I'll keep you posted on thoughts/progress.

[ogobrecht]

Thanks again for your time Connor. Forget my argument regarding GDPR - we are here in the context of an audit solution, no idea what I was thinking at that moment. Yes, MODULE and ACTION are captured, but not in the case when we suppress the entries and want to know how many were suppressed. I meant to add these columns to the possibly new table (AUDIT_SUPPRESSED_UPDATE_ENTRIES?) and also have all columns in this table in the primary key except the SUPPRESSED column. Too many details resulting in too many entries in this table - so we need to keep the balance between saved audit entries with no changed data and the details of the savings to have a record of that for the auditors and for further investigation. Maybe you have a totally different idea - I am looking forward to hearing about your progress and ideas.