Database Activity Monitoring & Event Collection Options
‘During several recent briefings, chats with customers, and discussions with existing clients, the topic of data collections methods for Database Activity Monitoring has come up. While Rich provided a good overview for the general buyer of DAM products his white paper, he did not go into great depth. I was nonetheless surprised that some people I was discussing the pros and cons of various platforms with, were unaware of the breadth of data collection options available. More shocking was a technical briefing with a vendor in the DAM space who did not appear to be aware of the limitations of their own technology choices … or at least they would not admit to it. Regardless, I thought it might be beneficial to examine the available options in a little greater detail, and talk about some of the pros and cons here. Database Audit Logs Summary: Database Audit Logs are, much like they sound, a log of database events that have already happened. The stream of data is typically sent to one or more files created by the database platform, and may reside at the operating system level or may be contained within the database itself. These audit logs contain a mixture of system resource recordings, transactional events, user events, system events, and other data definitions that are not available from other sources. The audit logs are a superset of activity. Logging can be implemented through an agent, or can be queried from the database using normal communication protocols. Strengths: Best source for accurate data, and the best at ascertaining the state of both data and the database. Breadth of information captured is by far the most complete: all statements are captured, along with trigger and stored procedure execution, batch jobs, system events, and other resource based data. Logs can capture many system events and DML statements that are not always visible through other collection methods. This should be considered one of the two essential methods of data collection for any DAM solution. Weaknesses: On some platforms the bind variables are not available, meaning that some of the query parameters are not stored with the original query, limiting the value of statement collection. This can be overcome by cross-referencing the transaction logs or, in some cases, the system tables for this information, but at a cost. Select statements are not available, and from a security standpoint, this is a major problem. Performance of the logging function itself can be prohibitive. Older versions of all the database platforms that offered native auditing did so at a very high cost in disk and CPU utilization- upwards of 50% on some platforms. While this has been mitigated to a more manageable percentage, if not properly set up, or if too much information is requested from high transaction rate machines, overhead can still creep over 15% unless carefully deployed. Not all system events are available. Network Monitoring Summary: This type of monitoring offers a way to collect SQL statements sent to the database. By monitoring the subnet, network mirror ports or TAPS, statements intended for a database platform can be ‘sniffed’ directly from the network. This method will capture the original statement, the parameters, and the returned status code, as well as any data that was returned as part of the query operation. Typically an appliance-based solution. Strengths: No performance impact to the database host, combined with the ability to collecting SQL statements. On legacy hardware, or where service level agreements prohibit any additional load being placed upon the database server, this is an excellent option. Simple and efficient method of collecting failed login activity. Solid, albeit niche applicability. Weaknesses: Misses console activity, specifically privileged user activity, against the database. As this is almost always a security and compliance requirement, this is a fundamental failing of this data collection method. Sniffers are typically blind to encrypted sessions, although this is still a seldom used feature within most enterprises, and not typically a limiting factor. Misses scheduled jobs that originate in the database. To save disk space, most do not collect the returned data, and some products do a poor job of matching failed status codes to triggering SQL statements. “You don’t know what you don’t know”, meaning that in cases where network traffic is missed, mis-read or dropped, there is no record of the activity. This contrasts with native database auditing where some of the information may be missing, but the activity itself will always be recorded. OS / Protocol Stack Monitoring Summary: This is available via agent software that captures statements sent to the databases, and the corresponding responses. The agents are deployed either in the network protocol stack, or embedded into the operating system to capture communications to and from the database. They see an external SQL query sent to the database, along with the associated parameters. These implementations tend to be reliable, and low-overhead, with good visibility into database activity. This should be considered a basic requirement for any DAM solution. Strengths: This is a low-impact way of capturing SQL statements and parameters sent to the database. What’s more, depending upon how they are implemented, agents may also see all console activity, thus addressing the primary weakness of network monitoring and a typical compliance requirement. They tend to, but do not always, see encrypted sessions as they are ‘above’ the encryption layer. Weaknesses: In rare cases, activity that occurs through management or OS interfaces is not collected, as the port and/or communication protocol varies and may not be monitored or understood by the agent. System Tables Summary: All database platforms store their configuration and state information within database structures. These structures are rich in information about who is using the database, permissions, resource usage, and other metadata. This monitoring can be implemented as an agent, or the information can be collected by a remote query. Strengths: For assessment, and for cross referencing status and user information in conjunction with other forms of monitoring. Weaknesses: Lacks much of the transactional information typically needed.
