Audit trails and implementing SOX/HIPAA/etc, best practices for sensitive data

Question

I consider myself to be relatively proficient in terms of application design, but I've never had to work with sensitive data. I've been wondering about what the best practices were for audit trails and how exactly one should implement them. I don't have to do it right now, but it'd be nice to be able to confidently talk with a medical company if they ask me to do some work for them.

Let's say we have a "school" database, with 'teachers', 'classes', 'students' all normalized in a many-to-many 'grades' table. What would you log? Every insert/update on the 'grades table'? Only updates (say, a kid breaks in and wants to change grades, this should send up redflags)? Does this vary entirely based on how paranoid one wants to be? Is there a best practice?

Is this something that should be done in the database? (A trigger on each sensitive SELECT which inserts a row to an 'audit' table logging each query?) What should be logged? Is there functionality automatically built into Oracle/DB2 that do it for you? Should this be application side logic?

If anyone has any formal documentation/books on how to deal with sensitive data (not quite DoD "Trusted Computing" spec, but something along the lines of that :P), I'd appreciate it. I'm sorry if this question is terribly vague. I realize that this varies from application to application. I just want to hear your detailed experiences with dealing with sensitive data.

Answer 1

Oracle has a product called Oracle Audit Vault- DB2 probably has an equivalent.

You should start by prevention. The system should not allow invalid actions. Period. If the system allows 'dubious' actions that need to be monitored, that's "business logic", you are probably better of implementing like the rest of your business logic.

If you want to do something in your database, you can look into log shipping (terminology might differ from RDBMS to RDBMS). Basically, any DML operation is logged to a file. You can use this information for backups and point-in-time recovery, even for replication/HA/failover/etc. If you ship your logs to a separate, "trusted" system in an "append-only" (i.e. the log shipping process has privileges to create new log files, but not to modify existing information) fashion, you already have a primitive auditing functionality. If you do it in a secure way (i.e. authentication, non-repudiation), you probably even are quite close to "compliance" :-p

Of course, sifting through lots and lots of INSERT/UPDATE/DELETE statements is not the most sophisticated way to work.

Answer 2

The first thing to understand is the native auditing capabilities of your chosen DBMS. These vary in detail, but generally provide a way to configure which operations are audited, and provide secure storage for the audit records that they generate.

The next thing to understand is what you want to audit. In the case of HIPAA and SOX, for example, you are probably looking at PII - Personal Identifying Information. Remember the fuss made about people accessing Obama's phone records, or various celebrities medical records, or ... Those were caught because the system audited who read those records, and the audit analysis officer (AAO) spotted that the celebrity records were accessed by people who were not specifically authorized to do so. So, those systems must be logging who accesses each record, and spotting when the user who does so does not have an authentic business reason to do so. In these cases, it appears that the users had read authority for the records, so if their ordinary duties required them to look at the records, they could do so. But, when they were not required to do so, then they were abusing their power and appropriately sanctioned (up to and including losing jobs over it).

What this means is that you probably don't want to track who accesses the table of States which records the state code and full name (and assorted other bits of information about the state). There is nothing confidential about that list - it doesn't matter who reads it. Of course, almost no-one should write to it; the list of states does not change very often - but that can probably be handled by revoking update and delete permission on the table from everyone.

OTOH, you probably do want to record who accesses the records in medical histories (HIPAA), or who modifies the data in the accounting systems (SOX). You might or might not need to worry about who reads the accounting data; a lot of that can be dealt with by basic permissions (accounting staff have permission; IT staff do not). However, auditing is always an extra line of defense.

Bear in mind that audit records are no help whatsoever if they are never looked at. In general, auditing slows a system down (simply because it is doing more work when it writes audit records); it is important to understand how much it slows down before deciding to implement your auditing strategy. However, there are some things that are more important than application speeed, and one of those is keeping yourself and other staff members out of jail. Auditing can be necessary to ensure that happens.