?
Solved

SQL audit table when someon has accessed information

Posted on 2009-07-01
10
Medium Priority
?
234 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 1200 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 800 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

801 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