Database Auditing Case Study – Messyware comes to life

This post is going to switch from general observations to a specific field study around data auditing. Many people have questions on what is a typical data auditing project. I have taken an actual enterprise project around database activity auditing and abstracted out the confidential details.

Who had the problem?

In a Fortune 500 company, this was the responsibility of a VP-level Database Manager. The Database Manager represented IT within a vertical business unit that made a large percentage of revenue and profit for the overall company. Security, compliance and internal audit were responsible for framing the requirements.

What was the immediate problem?

In this particular company, internal audit findings required that unauthorized modifications to the data had to be monitored. For this organization, critical data was being managed out of a number of databases, including Sybase, Oracle and SQL Server. By the way, this is typical of many large institutions – they are rarely a single database shop because different applications have naturally evolved on different databases. The DB Manager team worked with internal audit and the security group to list specific audit items. Four main database activities were part of the audit requirements –

1. Identify any and all insert/update/delete activity that occurs from any unapproved application  
2. Capture all privileged activity
3. Ensure high risk “read-only” accounts are closely monitored and effectively narrowed down or disabled
4. Monitor and eventually migrate/consolidate certain critical data (tables)

Interestingly, this was not the only project that impacted the DB manager. There were three other projects initiated by security & forensics (concerned about data theft from databases), internal audit (regarding data retention) and BPO (for offshore security for databases) that added new requirements to the first project. While these projects were not immediate, the database management group wanted to make sure that the data auditing approach they adopted could be flexible and multi-purpose to handle these projects over time.

What are the challenges in the auditing?

The project requires developing a number of capabilities which I list next –

Inventory & classification – The first step requires identifying approved database applications. Obviously a one-time manual listing is not a great idea. An automated tool to generate actual application activity and the schema being accessed would be nice for starters. A mechanism to use these reports to select unapproved applications would be nicer.

Monitoring, filtering & logging – Monitoring insert/update/delete activity from unapproved applications would require logging all insert/update/delete activity and then pruning out access by a set of applications. Logging at this level has its own challenges – depending on whether it is Sybase or Oracle or SQL Server application; different selectivity knobs need to be turned on to get this level of granularity. Performance of the database can be impacted if the application offers low selectivity and all events need to be logged. Ideally a transparent way to monitor and log database activity without impacting databases would be great. A policy-based way to say which activities to collect would be efficient.

Data management – Once logging is done, the next problem is figuring out what to do with the data created. If the data is created within the database systems (in tables and on disk), it has to be periodically cleaned up otherwise it can impact the database. If real-time alerts are to be generated the data has to be processed quickly. (In our example, certain unapproved activity required alerting tied to SIM environments so security teams could watch and investigate quickly). Ideally a way to collect and process data outside the databases would be desirable – it makes for a secure, manageable data repository.

Data correlation - All activity that is collected should be normalized and consolidated between Sybase, Oracle and SQL server into a single activity data model. Their individual logs look quite different, so this is required step if a single consistent event reporting framework has to be created across databases.

Data Reporting & Alerting – Reports must be generated to capture both summary-level information (as in how many accesses have been seen over the past day, or month), to detailed audit trail (specific event with time, commands, application, data being accessed, user, etc.). About 60 days of events should be stored on-line so reports and investigation can be quickly done. Reports should be escalated to appropriate stakeholders – in this case, the DB Manager actually reads the unapproved access alert report each morning. (I have heard, though not personally confirmed, that someone actually got fired on the basis of these reports, because they used the fire-call/privileged account ID inappropriately.)

Data archiving – The event data being created is compliance-related – so it must be preserved for a long time. This data should be archived (in this case, onto a NAS system that the enterprise uses for all retention) so it can be used and retrieved at a later point. While the auditors will normally only rely on the reports and certification that auditing is happening, the archived data can be useful for forensics in the event of a breach.

How to deal with messyware?

Now you can see what is involved in a typical data auditing project. It is messy stuff and not a single point-task. Multiply this work by the number of databases. Now multiply this by the number of different enterprise drivers (in this case – we collected events for internal audit, but imagine privacy folks asking for data read information, or customer audits asking for BPO application activity audits, etc. ). We have not even needed a reasonable level of analytics – that is usually a key requirement for data theft detection. This is why seasoned DBA’s and security personnel who are used to writing in-house tools are now recognizing that data auditing is a broad interdisciplinary problem. It deserves standalone security and data management capabilities that can span across applications. In other words, it calls for a platform that will look at things holistically (as a project rather than a single task), achieve efficiency and automation, and ultimately reduce the risk of breaches and non-compliance. In the example I listed here, the DB Manager switched from doing internal development around native triggers to a third party data auditing system. Like with any new system, he invested energy in learning and adopting the data auditing platform. I hear he is reasonably happy about keeping auditors at bay.  I hope to interview him one of these days.

Article by Mr. Sri krishna, expert in the field of audit