Audit Data Modifications

Audit Data Modifications
This is one DBA’s tale of auditing his databases for modifications.  There will be many different ways that you can approach this task, and reach a solution.  Your solution may be similar to or different from mine.  I will explain how I reached my solution, and give you some good ideas of how to accomplish a similar task, for your own systems.  I must mention that my database houses financial data, and needs to be compliant with Sarbanes Oxley requirements.  I’m not fanatical about SOX, but have accepted its existence, and have tried to coexist with it, peacefully.  At the common denominator between what the auditor wants, and what I need to monitor, I think I found a happy medium.  I need to know what modifications occur in my databases, so that I can provide an auditable trail to the auditors, and I can reasonably assure myself that no unauthorized modifications occur on my system.  With SQL Server 2008, some more functionality will be built into the DB system to allow for auditing these types of modifications.  I look forward to seeing how this will affect my system, but for now, this system works with SQL 2000 and SQL 2005.  C2 auditing is always an option.  Third party applications provide another alternative solution.  I chose to create a home grown solution that allowed me the flexibility and control over monitoring. General Overview of the Audit Data Modifications System
My shop is under the watchful eye of Sarbanes-Oxley.  Not only do I, as a DBA, need to know that modifications occur in a controlled manner, modifications need to remediated and an auditable trail provided of my actions as well as other actions.  I may perform a task that updates the database, but was requested to do so by the Change Control process.  I may need to remove records that are inhibiting normal process flow in the system, but there should have been a business owner request for this as well.  I may be tracking down a bug in the system, but a Change Control process or bug tracking process must be followed in this circumstance as well.  First, let’s define what is meant by Data Modifications.  I refer too Insert, Update and Delete statements.  Not structural changes to database objects; Tables, Views, Stored Procedures, Functions.  Not job alterations.  Not modifications to users or logins.  This was rather hard to do, and differed from Database Changes I have previously written about, in that these are only data alterations.  Most are ok, but removing data from certain tables can negatively impact a system.  As well as remove data that is important. Next, I do not presume to dictate how your Change Control process functions.  Let’s assume that you do have some process in place, and that it is not only understood but followed.  If it is not followed, disciplinary actions can and should occur to those that breach the Change Control process. Next, the system needs to be minimally impacting, constant, and separate from the main server being monitored.  We want business to continue as usual, but we need an overshadowing system that will know of all modifications that occur on the monitored db.  I have chosen SQL Server  Trace to perform these actions.  Not the Profiler, though you can use this to do the same task, but in a manual way.  Trace allows me to decide what type of events I want to monitor, and then gather specific columns of information about these events. I can drill down to very specific events; even filter specific columns for specific data results.  This ability allows me to determine what my business needs are, and ignore things that I do not care to monitor.  The files generated by the Trace system are rather small, and can easily be used in a variety of ways once generated.  I use the files to keep as a backup, and import them to a system to report. Since we need to monitor each server, all day long, but want periodic results, I chose a window of 1 hour to be the largest period monitored.  Thus a trace will generate a file for a 1 hour block, and then start a new file.  Within each hour file, I can know fairly quickly of any breaches of process.  Instead of waiting until the next day to process a day’s worth of data.  So each hour a job fires off a trace, and then quits and repeats.  Also each hour, another job will import this trace file into a database for reporting. The way I decided to accomplish this task was as follows. 
1. I create a central Monitor Server with SQL Server installed on it. 
2. For each server I wish to monitor, a linked server will be created and tested. 
3. The Monitor Server will have a job that fires off, and reaches over to another server (via linked server), and starts a trace. 
4. The trace file will be stored locally, on the Monitor Server.  The ability to create a file will need to be configured and tested.
5. When the trace file has been finished, a process will import this data to a Data Modifications database for reporting.
6. Scrubbing of the data will occur, and data removed based according too business needs and rules.
7. Someone will review this data
8. Each item will be justified.  A ticket or other Change Control documentation will be associated with an approved action.
9. Non approved actions will be raised as potential breaches of the Change Control System.
10. Lots of yelling and screaming and finger pointing will occur, until the modification is understood and justified.
11. Notification Reports can be generated for the Auditors of interested parties. Justification
Email Reports
At one point, a simple email containing all the modifications was sent out to needed parties.  With many multiple servers to be monitored, this would prove to be a rather large email report.  A one by one email was not an option.  Reporting Services was implemented to produce a report, and thru a subscription, emails were sent out, with links to the report.  Soon, we embedded the report in the email, and this caused problems with too large of emails.  In the end, the data is simply reviewed by the reviewer in SQL. Linked Server Justification
There are always issues with linked servers.  I chose this easy method, because we can ensure security on the Monitor Server, disallowing anyone from accessing it, and being able to gain access to the monitored servers it monitors.  We control the password changes on the monitored servers, and can alter them at will.  We have a mix of SQL Server 2000 and 2005, and did not want to go the mail route on each server.  Centrally gathering the data is easily accomplished with a central Monitor Server, and linked servers is an easy way to accomplish this task.