The Old Joel on Software Forum: Part 3 (of 5) - Creating Secure Applications

Creating Secure Applications - How much info shoul

I inherited an application which is made up of six modules that access the same database backend. Wach of these modules log substancial information.

It is possible to follow a transaction from the 'submit' button to the database update.

At the moment we run a weekly Kron job that Gzips up the logs and deketes them on a scheduled basis.

I was wondering if there industries where the log files are kept for longer periods? i.e. do banks keep a log files for transactions? Is it necessary to follow a transaction all the way through or is the logs kept at the database level sufficient?

Ram Dass
Sunday, November 16, 2003

I don't know what the industry standards are, but I suggest you consider your domain. If it's a bank, then you probably want full auditability, meaning you log everything. On the other hand, if you're just tracking an inventory of twenty million widgets, it probably costs less to lose a widget every once in a while than to maintain a perfect log of everything.

Mike Swieton
Sunday, November 16, 2003

Financial institutions are required by law to keep a complete audit for a long time (some places require 3, some require 7; Regulations vary). Microfiche is still a popular storage format - it takes more room compared to digital/magnetic media, but (a) It wasn't that way until a few years ago, (b) banks are slow to move, (c) in most environments, they outlast magnetic media significantly, and -- not less important -- (e) a microfiche reader is helpful, but you can also read them with any microscope or good magnifying glass.

The BBC had a project two decades ago to scan and preserve old books; They used a supposed-to-last-for-eternity optical format. Last year, they had very hard time reading these optical disks, because they weren't commercially successful, and they weren't able to find any reader. Eventually, they were able to read it, and transferred it to CD rom. Needless to say, the original paper books haven't really deteriorated much in these 20 years.

But I digress.

Quantify "substantial" - a 120GB disk can be bought for ~$100 these days. If a gzipped copy can fill that much disk space in less than a quarter, then you might need to find a reasonable filtering / retention policy. If not, spend $200/quarter to buy two disks (one for backup), and store it on them. (Assuming that -- if you delete all files every few months, two copies will be enough to keep you feeling well - just make sure they're stored in independent places).

The log doesn't make it secure, though. How can you tell the logs weren't tampered with? That the timestamps are accurate? If you want your logs to be secure, you need much more work - the paranoid practice is setting up a write-only log host, lots public key crypto, and a hardcopy devices that also prints the logs.

And the only way to really secure it is disconnect it from the net. Preferably also the mains outlet (And some would claim that you also need to run your PC through a capable shredder, then bury it in cement).

Ori Berger
Monday, November 17, 2003

I've done a lot of capital markets trading systems. We kept stuff for seven years. And yes, independent auditors do come around and check. They ask things like "Can I see the audit trail for transactions 20394858766, 20394858912, 20394859043? They were all done on March 7 three years ago." Not being able to supply data within a couple of days is probably the single best best way to cut your career short in a bank.

Typically we had an audit log table in the database tracking all user and system changes to every transaction. This log was purged to flat file for every transaction that had matured six months earlier. The purge was run nightly or monthly, depending on transaction volume. The flat files were backed up to tape and stored offsite as part of the 7 year requirment. But as Ori points out, keeping it around on disk is virtually free and it definitely surprises the auditors when you can answer their question in five minutes versus the day and a half they are expecting it to take to locate and load the right tape.

Typical audit log would be:

timestamp, user_id, txn_id, event_type, description

Entries would when the trade was created, when it was approved by the trading floor, when it was transmitted to the back-office, when it matured, and any kind of editing or changes done in the meantime. The description would contain old and new values of any fields changed.

Also included is anything having to do with financial calculations, security, and access. (All user setup, user login, failed login, change password type stuff.)

System level stuff like "user clicked on 'Submit' button", "servlet sent trade to app server", etc would not be present in the audit log. That would be in system logs, of course, but there is no requirement to keep them around.

Potentially, they can also ask "Please show us the log of Tom Bishop's account being deactivated when he left for vacation last year." Bank officers are required to leave the premises for two consecutive weeks a year, and by extension, have no access to systems during that time. Enforcement is spotty, I've never actually been asked for it, but I'd treat it as a requirement and build it in anyway.

Jim S.
Monday, November 17, 2003