4 ms·
I think a key requirement for an audit capability is that the audit records cannot be modified. Using a trigger allows you to add an audit capability onto exis
by GartzenDeHaes 4y ago
I think a key requirement for an audit capability is that the audit records cannot be modified. Using a trigger allows you to add an audit capability onto existing tables in a way that prevents the application and the developers from accessing it. Typically you would also have special controls on the audit tables to log access and/or keep the DBA's out too (if you have dedicated audit staff, for example).
If you're designing the system from scratch, you might want to consider a journal and ledger design. It's an insert-only database design where all changes are added as new rows in a journal table and a current roll-up is kept in a ledger table. This design also has a nice side effect of giving you the ability to recover from application data errors.
Edit: audit is about proving that something did or didn't happen, so low level logging is usually appropriate, and required if you're doing federal stuff.
- mnming 4y agoHmmm, with row level logging, do you usually supply the row level log directly to auditor? It feels like a lot heavy duty work for auditor. Or perhaps, my misunderstanding is that I conflate the term audit log with something else? I thought audit log is like "Bob has logged in at time XXX from location YYY".
- GartzenDeHaes 4y ago"Bob has logged in ..." sounds like an event log to me. I think logging using DB triggers is usually for compliance requirements, such as FISMA. IT auditors sometimes want a dump of the database, or read-only access, or screenshots of the controls related to creating and securing the logs.