Data READ Auditing

Question

Data READ Auditing

Source-NH

SSCarpal Tunnel

Points: 4165
More actions
September 27, 2007 at 11:24 am

#125857

Okay, here's the deal ....
C2 auditing produces reams of unnecessary info. Flip side is there isn't a SELECT trigger on a table. Does anybody out there know of a way to track who is accessing records from a given table? Profiler aside....
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yep your right...select statements don't produce a loggable item, so there's no way to track who reads a table, unless you set up a profile.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

noeld SSC Guru Points: 96590 More actions · Answer 2

Rich (9/27/2007)
Okay, here's the deal ....
C2 auditing produces reams of unnecessary info. Flip side is there isn't a SELECT trigger on a table. Does anybody out there know of a way to track who is accessing records from a given table? Profiler aside....
Thanks in advance.

If you can change the design so that access to the tables is only performed through stored procedures you will have your work done easily. On the other hand if that is not possible unless you use an sql trace you are out of luck.

* Noel

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 3

Thanks for the quick responses. It wasn't what I wanted to hear, but it was what I expected to hear.

Mike Good SSCertifiable Points: 7415 More actions · Answer 4

Server traces don't have to involve "profiler" ... I'm probably being nitpicky though? You could readily write something like this, ensure your server trace always running, periodically dump output to files, then import files to sql tables & process--all without ever opening profiler.

We use Idera SQLcompliance manager for this. It's not all that cheap, but seems to do good job w/out much overhead, lot of end-user features. It is trace-based. We definitely monitor SELECTs.

noeld SSC Guru Points: 96590 More actions · Answer 5

sql trace does not add too much overhead .... depending on what are you catching and what are you filtering. My Servers get sometimes over 5000 batch/sec rates and if I was to log all that I am possitive I would be dead by now 😉 Now if your rates are not any where near that and you can filter sufficiently then you are a happy camper. Good Luck!

* Noel

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

I have been looking into this myself.

I was reading the info on Lumigent Audit DB. The marketing info claims it does job, but I was trying to find if anyone is actually using it. The web site gives me the feeling it would be big bucks also.

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 7

we looked at both lumigent and idera. We ended up getting Idera's compliance manager, but I guess it's a matter of opinion. Idera was cheaper and did everything we needed it to do. It's been working great for us.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Does Idera's compliance manager allow you to log someone who just connected to a database or just selected data without changing anything?

noeld SSC Guru Points: 96590 More actions · Answer 9

From Experience I know Lumingent is BIG $$$ but I am not sure it captures "reads" though.

* Noel

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 10

Is the argument against traces that they're too hard to set up or they're too complex? Or is the reporting side?

Setting up a server side trace should be easy and let each server itseld. Then you can easily roll those up with some SSIS packages or simple file transfers into a central server.

Mike Good SSCertifiable Points: 7415 More actions · Answer 11

I agree. Not sure there is an argument against, it was just the way Rich originally phrased things "Profiler aside..."