auditing data changes, inexpensively

Question

auditing data changes, inexpensively

Indianrock

SSC-Insane

Points: 20333
More actions
September 12, 2008 at 10:23 am

#154724

For now I'd like to avoid buying a tool. Need to log inserts, updates, deletes by a small group of developers who access production data outside of the GUI built for our customers. Triggers on specific fields could miss some. Tracing changes by a named group of domain users, limited to Insert/Update/Delete actions by any tool other than our GUI would do it, but I'd have to make sure it wasn't overloading the production hardware. A table on another server, loaded with such trace info, could be queried, emailed and purged on a regular basis.
Any better ideas or inexpensive ( under $400 say ) tools you've used for this?
thanks,
Randy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

You said triggers wouldn't work, how's come? It's free and there are plenty of examples of ways to do it rather well.

Another option would be to capture a profiler trace filtering all events by user name and perhaps application name. granted there's a performance hit for these things it should get you into the right ballpark.

Sure, could someone come along and stop your profiler trace, absolutely, but if you set up your security correctly you can mitigate many of those types of issues.

Is this for compliance with a specific company, national, industry regulation? If so you may need to talk to your legal folks and see exactly what they need so that you can give it to them.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

I was thinking triggers were tied to specific fields in specific tables. With a large relational database, hundreds of tables, if a user makes a change to one field in one record wouldn't it be hard to set up triggers for all of that?

Yes I should get more detail because if this is a client audit requirement or SAS 70 type requirement it will have to be bulletproof.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Take a look at these articles and their discussions. Lots of data and options for auditing.

http://www.sqlservercentral.com/articles/Auditing/63247/

http://www.sqlservercentral.com/articles/Auditing/63248/

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

Those are good articles. Profiler will work if you can limit how the developers access (meaning trace their logins).

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

Those are great articles, thanks. Since I only need to audit a handful of employees ( everyone else would be using the GUI front end or it would be an automated process loading data ) I'd rather not deal with log files or an additional database on the production hardware.

I think you can get pretty granular with profiler as far as only logging transactions by a named group of people or "anything except application A or application B". I still need to find out if this is for some legal requirement where the ability to stop the trace by a production support developer ( those guys are given sysadmin rights against my wishes ) would totally void the audit approach.

I think I've only run profiler from management studio where obviously if the workstation or server I'm remoted into is restarted the trace stops. I'll have to see how to have the trace run ( server side? ) in a way that only ( sysadmins? ) can stop it. My guess is it would satisfy the audit requirement to identify data changes if only 3 or 4 people could stop the trace.

Otherwise I'm guessing it would have to be something purchased specifically for this purpose that is virtually fullproof.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

I think you'd want to set the trace to run at startup and then log to a table in a database that very, very few people (and not the DBAs) have access to.

You can always log everything from group A, and then delete stuff that has application X in it if you need to. I'd set an alert on the trace stopping, perhaps even shutting down SQL Server if you need it. Not perfect, but it will work.