Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

auditing data changes, inexpensively Expand / Collapse
Author
Message
Posted Friday, September 12, 2008 10:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
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



Post #568710
Posted Friday, September 12, 2008 10:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:05 AM
Points: 2,897, Visits: 5,981
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
Post #568721
Posted Friday, September 12, 2008 10:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
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.



Post #568728
Posted Friday, September 12, 2008 10:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #568741
Posted Friday, September 12, 2008 11:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:00 PM
Points: 33,204, Visits: 15,354
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
Post #568778
Posted Sunday, September 14, 2008 1:10 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 18, 2014 11:11 AM
Points: 596, Visits: 1,687
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.



Post #569205
Posted Sunday, September 14, 2008 1:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:00 PM
Points: 33,204, Visits: 15,354
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
Post #569208
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse