SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


auditing data changes, inexpensively


auditing data changes, inexpensively

Author
Message
Indianrock
Indianrock
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8983 Visits: 2460
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



Luke L
Luke L
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14791 Visits: 6148
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

For better help with performance problems please read this
Indianrock
Indianrock
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8983 Visits: 2460
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
GSquared
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106309 Visits: 9730
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
Steve Jones
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250738 Visits: 19814
Those are good articles. Profiler will work if you can limit how the developers access (meaning trace their logins).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Indianrock
Indianrock
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8983 Visits: 2460
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
Steve Jones
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250738 Visits: 19814
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search