Web Application Auditing – User Changes Logging

auditjavaloggingweb-applications

I have a web application written in Java, Oracle, and AngularJS. I want to save log in database when user changes, creates or updates something.

There are two way of implementing this:

1. Auditing tables via Triggers

Cons: On database level I don't have user information who makes changes, so I have to save userName in every table I make any change.

For example: my table will look like this:

my_table {
     field_1, 
     field_2, 
     ...,
     userName
)

2. Adding event logging functionality in Web application API functions

For example, when user changes data I have to call both functions: changeObject and AuditUserChange.

In addition to this I have to add trigger on the table to save audit log when somebody (support specialist) makes any change directly in database tables.

Which strategy should I use? Which option is better?

Best Answer

Question: Do you ever change any of this data by means other than the Web application? (Do you ever need to "fix" data?).

If the answer is "Yes" then use triggers.

The username needs to be accessible to the database - oracle's client_identifier is a good option, but you have to code the client application to pass it through.

Then you might create a table that largely duplicates the audited table, like this: 

create table main_table 
( field1 varchar2( 1 char ) 
, ... 
, field9 varchar2( 9 char ) 
); 

create table main_audit
( username varchar2( 256 ) 
, change_date date 
, change_type varchar2( 1 char ) 
, field1 varchar2( 1 char ) 
, ... 
, field9 varchar2( 9 char ) 
);

Your trigger then copies values from one table to the other, using the old: and new: pseudo tables. (Change Type might have values for Insert and Deletes and, for Updates, two values representing the "before" and "after" images of the record (what the values were is often as important as what they are now).

A Sequence of changes might look like this: 

select * from maim_audit ; 
+----------+---------------------+-------------+---------+--------+ 
| username | change_date         | change_type | field1  | field9 | 
+----------+---------------------+-------------+---------+--------+ 
| bob      | 2017-09-19 08:00:06 | 1 (Insert)  | BMW     | 325    | 
| arnold   | 2017-09-19 10:53:05 | 2 (Before)  | BMW     | 118    | 
| arnold   | 2017-09-19 10:53:05 | 3 (After)   | Peugeot | 107    | 
| jeff     | 2017-09-19 11:15:00 | 4 (Delete)  | Peugeot | 107    | 
+----------+---------------------+-------------+---------+--------+
Related Topic