I am now switching gears to talk about some of the ‘detective’ measures that help with forensic analysis of transactions and activity. The preventative measures discussed previously are great for protecting your system from known attacks, but they don’t help detect fraudulent misuse or failure of business processes. For that we need to capture the events that make up the business processes and analyze them. Our basic tool is database auditing, and they provide plenty of useful information.

Before I get too far into this discussion, it’s worth noting that the term ‘transactions’ is an arbitrary choice on my part. I use it to highlight both that audit data can group statements into logical sequences corresponding to particular business functions, and that audit trail analysis is most useful when looking for insider misuse – rather than external attacks. Audit trails are much more useful for detecting what was changed, rather than what was accessed, and for forensic examination of database ‘state’. There are easier and more efficient ways of cataloging SELECT statements and simple events like failed logins.

Usually at this point I provide a business justification for auditing of transactions or specific events, and some use cases as examples of how it helps. I will skip that this time, as you already know that auditing is built into every database; and captures database queries, transactions, and important system changes. You probably already use audit logs to see what actions are most common so you can set up indices and tune your most common queries. You may even use auditing to detect suspect activity, to perform forensic audits, or even to address a specific compliance mandate. At the very least you need to have some form of database auditing enabled on production databases to answer the question “What the &!$^% happened” after a database crash. Regardless of your reasons, auditing is essential for security and compliance.

In this post I will focus on capturing transactions and alterations to the database. What type of analysis you do, how long you keep the data, and what reports you create are secondary. I am focusing on gathering the audit trail rather than what do with it next. What’s critical is here understanding what data you need, and how best to capture it.

All databases have some type of audit function. The ‘gotcha’ is that use of database auditing needs careful consideration to avoid storage, performance, and management nightmares. Depending on the vendor and how the feature is deployed, you can gather detailed transactional information, filter unwanted transactions to get a succinct view of database activity, and do so with only modest performance impact. Yes, I said modest performance impact. This remains a hot-button issue for most DBAs and is easy to mess up, so planning and basic tests form a bulk of this phase.

1. Benchmark: Find a test system, gather a bunch of queries that reflect user activity on your system, and run some benchmarks. Turn on the auditing or tracing facility and rerun the benchmark. Wince and swear repeatedly at the performance degradation you just witnessed. Aren’t you glad you did this on a test system? You have a baseline for best and worst case performance.
2. Tune:
   1. Select Audit options. Oracle, SQL Server, DB2, and Sybase have multiple options for generating audit trails. Don’t use Oracle’s fine-grained auditing when normal auditing will suffice. Don’t use event monitors on DB2 if you have many different types of events to collect. Which auditing option you choose will dramatically affect performance and data volumes.
   2. Examine the audit capture options, and select only the event types you need. If you only care about user events, don’t bother collecting all the object events. If you only care about failed logins and changes to system privileges, filter out meta-data and data changes.
   3. Examine buffer space, tablespace, block utilization, and other resource tuning options. Audit data are static in size, so their data blocks can be set to ‘write-only’, thus saving space. For audit trails that store data within database tables, you can pre-allocate table space and blocks to reduce latency from space allocation.
   4. Rerun the benchmarks and see what helps performance. Generally these steps provide significant performance gains. No more cursing the database vendor should be needed.
3. Filter: Get rid of specific actions you don’t need. For example, batch updates may fall outside your area of interest, yet comprise a significant fraction of the audit log, and can therefore be parsed out. Or you may want to audit all transactions while the databse is running, but not need events from database startup. In some scenarios the database can filter these events, which improves performance. If the database does not provide this type of row-level filtering, you can add a WHERE clause to the extraction query or use a script to whittle down the extracted data.
4. Implement: Take what you learned and apply it in the production environment. Verify that the audit trail is being collected.
5. Ad-hoc Analysis: Review the logs periodically, focusing on failures of system functions and logins that indicate system probing. Any policy or report that you generate may miss unusual events, so I recommend occasional ad hoc analysis to detect anything hinkey.
6. Record: Document the audit settings as well as the test results, as you will need them in the future to understand the impact of increased auditing levels. Communicate use of audit to users, and warn them that there will be a performance hit due to regulatory and security requirements. Create a log retention policy. This is necessary – even if the policy simply states that you will collect audit trails and delete them at the end of every week, write it down. Many compliance requirements let you define retention however you choose, so be proactive. You can always change it in the future.

More advanced considerations:

• Automate: I recommend that you automate as much of the process as you can, once you have done the initial analysis and configuration. Collection of the audit trail, filtering, purging old records, and long-term storage are all tasks that can be automated through simple scripts and cron jobs.
• Integrate: Reporting services, event management, and change management are all services that help automate security tasks based on audit data.
• Review: Periodically review log tuning and filtering to determine if the settings are still appropriate. Most organizations collect more data over time rather than less, but who knows? You may want to run some sanity checks on the performance benchmarks every now and again, as vendors make improvements or offer new options.

The standard audit capabilities provided by database vendors provides ample information for compliance reporting, but in cases where SELECT statements are not captured, they are of limited use for security reviews. So in the next post I will go over event analysis to discuss other data collection options and essential events to evaluate.

Remember: if you are a DBA, and people within your company are requiring that you provide them with log files, this is a good thing. It means that they are the ones who will need to review transactions for suspicious activity. They can delve through the reports. You may have more work in setting up the reports and auditing options, but overall this is a good tradeoff.