Tracking Reads by ROWID

GregTomkins

Active Member
We are discussing a scenario where we want to be able to log all database *reads* down to the level of the particular ROWID (eg. we want to be able to generate a report that lists every ROWID that was read during a given session).

We want to be able to do this without changing schema or existing code. Wrapping existing code in something would be OK, creating a different DB with perhaps different PROUTIL-type properties would be OK, and for this scenario we don't care about performance.

I was hopeful that the (relatively new) auditing features would support this, but a quick glance suggests they only track updates.

Any other ideas?
 

TheMadDBA

Active Member
First I have to ask why, because I am just curious that way :)

I am not aware of any way to do this that does not at least require adding a trigger (FIND) to the database schema.
 

TomBascom

Curmudgeon
Auditing does support "user events" (I may not have the name quite right) that you /could/ use for targetted FIND auditing but if you want general purpose, complete coverage with minimal disruption to code and/you will need to add FIND triggers.

I can imagine some possible issues with circular logic if the FIND triggers have to look anything up.

You say you don't care about performance. I can hardly wait to see how long that lasts ;)
 

GregTomkins

Active Member
It's not so crazy, MSSQL and Oracle both support this (although I don't know how well). I knew a guy who worked for the income tax people and apparently they audit all reads, eg. to prevent even DBA's from going and looking up your brother-in-laws tax files without good reason (although, that could very well be done in triggers, and no matter what you do, some DBA must be able to disable it...).
 

TheMadDBA

Active Member
Oracle is the king of auditing... you can control who can see what and/or track that at whatever level you want. They also have an add on that lets the DBAs do all the basic maintenance, but not actually see the data.

Performance on that is actually pretty decent, unless you turn it on for every table and column.
 

GregTomkins

Active Member
After poking around a bit I realized, you can put a bunch of 'ON FIND OF orders' type statements at the start of the session, those seem to capture everything that happens after that with no schema or downstream code changes. The term 'FIND' is misleading; it works for FOR EACH and dynamic queries as well (as far as I can tell).

Of course we use ON all the time in our GUI code but I didn't realize you can use it for DB operations as well.

This would be fragile in that sessions might neglect to do this, but in my case I only want it for specific sessions, so that's OK.
 

TomBascom

Curmudgeon
Those are called "session triggers". They can be handy for situations where you don't want to (or cannot) add a permanent trigger to the schema.

Yes, FIND triggers would be better named READ triggers.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
So at a high level what is your objective; auditing (which users went after which records) or perf (what logical I/O is our code doing)? And is this something you would do only in dev, or do you want an approach you can use in prod?

BTW, ROWIDs are impermanent. At least, they are not guaranteed to be permanent for the life of a record, despite what the docs may say. Example: a proutil tablemove moves a table from area A to area B; ROWIDs will change. Granted it doesn't happen often but ignoring that case means you risk invalidating an audit trail, if that's what you want to create.

And of course if a record is deleted from a table, eventually when a new record is written to that same block it can be assigned that same ROWID that once belonged to the deleted record. So in evaluating that "audit" data after the fact, how do you know if two separate reads of ROWID 1234 in table X were for the same record or for two different ones?
 

GregTomkins

Active Member
Well if you really want to know ...

Like everyone else I suppose, we have some core functions that call dozens of programs and update dozens of tables. Some of that code goes way back to Reagan era, so it's rarely changed and unfamiliar to many. So when faced with changing some areas, of course it is daunting to try to figure out what's going on.

So we were chatting about how useful it would be to be able to do some routine operation in the UI (eg. place an order, add a customer etc.) and then get a report/file listing every record that you accessed including all the relevant fields. I think this is 98% feasible.

I don't think we are vulnerable to the problems you mentioned (though we would otherwise be, so thank you for pointing them out) because we can log all the details, not just ROWID (I was simplifying there) in the trigger.

The 2% error factor would be: I don't think FIND triggers catch CAN-FIND, and also, they wouldn't help where there is logic conditional on a record that doesn't exist (eg. FIND x, IF NOT AVAILABLE x ... the code branches depending on the existence of X, but you would never know this from the trigger).
 
Top