Auditing web application user changes

Question

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?

Answer 1

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    | 
+----------+---------------------+-------------+---------+--------+ 

Answer 2

Well, I'll start by saying context is important. You haven't said whether your application is intranet or internet facing, nor how big your web user population is.

In an intranet based application, a viable option might be to use the web users own identity as the DB user for each request and do all your logging in the database. This only really scales if you're using some kind of single sign on or central auth server like Active Directory, however.

If that's not an option, then I'd probably start off by doing logging web user activity via the application logs, and then setting up Oracle's audit functionality (https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000), as that will probably perform better and more reliably than custom triggers.

Note that depending on which Java web stuff you're using, there are ways to make the logging easier than having to remember every time you write a function to add a call to your Audit class. Look into Aspect-Oriented programming or perhaps if your framework has a concept of the request pipeline simply log all web requests with user info that you need.

Finally, I'd look to some log aggregation like ElasticStack or Splunk to get all my audit logs in one place/format so that it's easier to identify malicious activity rather than having to hunt around.