Why should systems record, in the database, who has logged in and when? Compliance, security, auditability?

Question

Sorry to mis-tag this SQL, but you seem like an active bunch here. I'm a data warehouse developer, currently working for a company that has developed an in-house bespoke operational system that I am extracting data from into the data warehouse. Some users asked if I could include user logging information in the warehouse - who logged in and when. But the system does not store this information. I feel like it should. Are there compliance / regulatory rules saying it should? Are there good security / system reasons why it should?

Answer 1

The system should store an audit log somewhere. It is best if it is stored in a separate database to non-security data or otherwise kept separate.

The audit log is used to observe user behaviour as part of non-repudiation. It is the only way of policing the behavior of administrators and other users with few restrictions. Many organizations will require an audit log for compliance with SOX (Sarbanes-Oxley), HIPAA, PCI-DSS or supply-chain contracts.

The audit log should have some form of simple tamper detection, such as a HMAC. Operations to purge or backup old audit log entries should also add an entry to the audit log.

[Edit: Added more details on request]

Non-repudiation is a very fancy way of saying "You can't claim you didn't do it" (http://en.wikipedia.org/wiki/Non-repudiation). For example, if a user uploads troublesome data then claims it was not him or her, an audit log showing the fact that the user logged on when no one else was would be very useful.

An HMAC is a Hashed Message Authentication Code (see http://en.wikipedia.org/wiki/Hash-based_message_authentication_code or http://www.ietf.org/rfc/rfc2104.txt). This is a cryptographic algorithm where you have a secret number, append it to the data and take a hash. This might be difficult to calculate without libraries and is probably a bit ahead of where you want to be.

Answer 2

Some industries and countries do have regulation around auditing; SOX widened that considerably. However, as far as I know, there's no general legislation or regulation about this - which is probably a good thing.

However, the fact that users are asking for reports on this data suggests there's a need to collect it.

Other reasons for storing this sort of data is to understand usage (and possibly bill departments for that usage), detect bugs, support capacity management and monitor service quality. Most non-trivial enterprise applications need at least some of those capabilities, even if they don't require formal audit.

If data security is your concern - protecting your prices and customer lists - audit alone is nowhere near enough, and may well be counter productive. Tracking a data leak to an individual log-in is almost certainly going to be (nearly) impossible, and could easily end up accusing the wrong person.