Solved

SQL audit table when someon has accessed information

Posted on 2009-07-01
10
223 Views
Last Modified: 2012-05-07
Im trying to find a way to audit a table and check when someone access specific information on it.

Looks like trigger are made for DML. UPdate Inserts and deletes.

Can someone direct me in the right path to create some kind of mechanism to write to a table/log or an alert when someone access specific data on a table. I am mainly concerned on columns. If anyone is able to run a query against it, I need to have it logged.

Thanks.
0
Comment
Question by:quippee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 24755224
The only way to do this is to write stored procedures that return the data and track when people run them...otherwise you really need to restrict this sort of thing through your security setup.  There is no trigger for when data is read.
0
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 200 total points
ID: 24755399
Another option is to use SQL Profiler, but you do not want it running all the time on your production server
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755446
I suspect the reason you're asking thisproblem because your users are able to run any SELECT statements they want on your DB, right?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:quippee
ID: 24755448
We have secured the data, but we need to audit the table and have some sort of report to proof is doing what its suppose to be doing. I have enabled SQLprofiler for that table only, but if Im going that route I have to mine the data from the traces.

Chapmandew, the data is already being access by store procedures but if we have some kind of security breach we would like to know if anyone else is accessing it outside the SP. I guess there is no easy way without 3rd party tools.

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755470
no way to do it, really...other than throughprofiler, and that is NOT a solution to this.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755493
The only way to "ensure" that if someone were to get in, that they wouldn't be able to access the data....this can be done carefully through testing and tweaking your security setup.
0
 

Author Comment

by:quippee
ID: 24914038
Thanks. I will start playing with SQL profiler and see how that goes. its only one table. So I will give it a shot. security wise we are good to go. I just need to prove that is working lol.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24914069
Now reason for a B grade here....
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24914940
If you are going to start playing with SQL Profiler, which is what I suggested, why a "B" grade then?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 74
Can Unique column have more than one Null? 8 61
Getting max record but maybe not use Group BY 2 36
denied execute as 13 32
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question