New post
Question
· Dec 8, 2015

Auditing Attempted Transactions / No Duplicate Journaling

#Security #Caché

There seems to be little point to journal audit db updates; why rollback an audit entry for an attempted update?

I’m working on some auditing code and I have a few questions (feel free to respond regarding 2016.1 or later).

  1. I’ve noticed that there is little point of journaling updates to an audit database, as the Audit log is essentially an additional journal. Is there any reason not to disable journaling for audit log updates?
  2. I’d like to audit even transactions that get rolled back, preferably with information that the rollback occurred (this could be a second audit entry). Is there a way to get notification of a rollback that would work for both auditing of SQL and Objects transactions?
  3. If I do disable journaling (within a transaction), and a rollback occurs, how can I prevent ROLLFAIL errors?

Thanks,

Derek

Discussion (5)0
Log in or sign up to continue
Derek Day · Dec 8, 2015

Aaron makes important points -- So it is absolutely necessary to journal updates to Audit tables, unless the recovery procedure reads the system journals (might work for an Object Syncrhonization based auditing mechanism -- does not work for simpler auditing frameworks).

How would one audit an attempted transaction that is then rolled back? It would seem that the auditing transaction would need to be atomically committed prior to the real transaction (or in a separate transaction/process), and then the audit data could be amended by the main transaction to show that it is committed. If it gets rolled back, then the committed entry would also be rolled back and the audit log would show the story.

From Aaron Wassall:

Hi Derek, 

 

1.      I’ve noticed that there is little point of journaling updates to an audit database, as the Audit log is essentially an additional journal. Is there any reason not to disable journaling for audit log updates?


I think the reasons not to disable journaling for the audit database are the same reasons that apply to all databases.

 

Startup recovery after a crash will restore journals in order to apply journaled data to the databases that wasn't previously applied because Caché crashed before the write daemon could write it.  If your audit database has journaling turned off, this is a situation where you can lose data.  For example:

 

-  journaling is turned off for the audit database

-  there is a login failure and then the system immediately crashes --> this data didn't make it to the database

-  startup recovery would apply this data during journal restore, but it wasn't journaled, so it doesn't get applied

-  this data is lost forever

 

Another reason is disaster recovery.  The best, supported way to fully recover from a disaster is to restore from a backup and apply journal files.  But if the audit database isn't journaled, then applying journal files does nothing.  You have lost all of the audit data between the time of the most recent backup and now.

 

Best, 

Aaron

 

Aaron Wassall

Support Specialist

0 0
Ray Fucillo · Dec 11, 2015

Hi Derek,

Audit records are not be rolled back if they were inserted in a transaction that happened to roll back.  (Note that I am assuming we're talking about the standard system auditing that either gets inserted implicitly or by calling $system.Security.Audit() explicitly.)

The records are journaled, but not flagged as being in-transaction, and so they are skipped by rollback.  Note that the same applies to entries created by the standard error log: ^%ETN and the Log() method of the exception object.

So, there's no need to call DISABLE^%NOJRN, though even if you did, it would not cause <ROLLFAIL> error.

As for getting this to automatically happen any time there's a rollback, there's nothing at the system level that provides that.  I believe there are various triggers within Objects and SQL, but I'm sure you know more about those than I do.

Ray

0 0
Derek Day · Dec 11, 2015

I am actually talking about application level auditing, but your response is still relevant to the discussion.

It sounds as though I'll need to find a way to do some of the auditing outside a transaction (perhaps in a separate job).

There is an object specific %OnRollback() callback (mostly used for stream optimization and integrity), but I don't think that this is exposed to SQL, so it is not available as a general purpose mechanism. So, I think I still need a two step audit process:

  1. Do the main auditing work somehow outside of the transaction (maybe in a separate job?) setting a commit flag set to false.
  2. Inside the transaction update the audit entry (or create a second one) setting the commit flag to true

Since rollbacks are exceptions, it would be much more elegant if the application could register a rollback handler. Would an enhancement request here make sense?

0 0
Ray Fucillo · Dec 11, 2015

Well, applications can audit to the audit database using $system.Security.Audit().  That would provide the rollback protection described and also the other benefits that the CACHEAUDIT database affords.

I think it would be difficult to form a general purpose rollback handler that would be simple enough to be the most usable solution.  Rollbacks aren't necessarily like exceptions.  Speaking very generally, there's two main cases, I think:

 a. The transaction is in a function you're writing. You already have complete control so you don't need a special handler.  

 b. The transaction is in the function you're calling in a function or that function calls. Remember transactions can be nested and there could be many transactions inside the callee.  Your code doesn't know whether the callee's rollback was expected or not, and doesn't really have any context around what is being rolled back, so I'm not sure how you'd have a sensible handler - it would be very non-local.  With any function you call, In the end, your code is going to get some indication of success, and if callers of a particular function have some need to know whether rollback was performed, then I suspect it's specific to that function and up to it to return the necessary info to its callers in a way that is appropriate within the interface it provides.

0 0
Derek Day · Dec 11, 2015

I am interested in hearing from other application developers who are using $System.Security.Audit() to audit data change events.

I agree that rollback handling would be difficult to make general purpose.

To summarize:
1. A generic rollback handler wouldn't be practical to implement or to use.

2. We would need a mechanism like $System.Security.Audit() or ^%ETN, or we'd need a log daemon process to do auditing for us so that the logging would occur outside of the application's normal transactions.

3. We might as well use $System.Security.Audit(), since that has been documented as a mechanism available to applications including the ability to create user-defined audit events.

0 0