| Author |
Topic |
|
alexkreyn
Starting Member
9 Posts |
 Posted - 2007-08-29 : 16:42:11
|
| different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists)I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements... 1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?)2.If somebody know where we can take good parser which will help with this job? Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-29 : 16:56:21
|
| Just out of curiosity, why do you need to track every select or every statement run by developers?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
alexkreyn
Starting Member
9 Posts |
Posted - 2007-08-29 : 17:09:18
|
| I want users to create hook-up reports theirself...They can use any data from database they need...But they should not open some private data without special need... The same concept people use in internet...You can open any site while you are in the office, but your boss want to grab all pornolinks you opened within working day in order to...watch them himself:)) |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-29 : 19:19:08
|
quote:
Originally posted by alexkreyn
I want users to create hook-up reports theirself...They can use any data from database they need...But they should not open some private data without special need... The same concept people use in internet...You can open any site while you are in the office, but your boss want to grab all pornolinks you opened within working day in order to...watch them himself:))
Well we dont get to browse any porn at work, though we do have access to internet. We cant view any online streaming. You can have trace procedures running every 30 secs or so and dumping the results into a table that you can query later.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-08-30 : 07:29:16
|
| simple....remove direct read access to the database....when the user come looking for info/report....formalise them by writing them as SP's...with params if necessary and then have the SP's log their exeuction...mgmt can review the logs. |
|
|
|
alexkreyn
Starting Member
9 Posts |
Posted - 2007-08-30 : 09:29:09
|
| The mgmt can review the trace too...It is not an issue...The problem is how to automate this process? The mgmt do not want every day to look trace. They want notification email what tables were touched, what columns were displayed and who run that query? To do this I need to go through trace every night and look select statements...Long and not reliable process. Even more. Suppose I have view from five- six tables linked together...When I say select * from view the data came from phisical table, so I want to know what table was touched and which column was displayed without been involved in sql statements overview...Like trigger for select statement(doesn't exist)...As soon as SQL server has a request to display data from table the trigger on this table should say: User A try to see my data from column a...Other words how to create event handler on display table data?Thanks for any help |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 09:48:50
|
| "Other words how to create event handler on display table data?"I don't think you can (unless there is some 3rd party monitoring software).SQL Profiler is the only way I can think of ... and then lots of work to Parse and Collate - e.g. VIEWs but also if they call an Sproc that in turn uses some Tables / Views.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 21:26:07
|
| Or use third party sql auditing tools. |
|
|
|
alexkreyn
Starting Member
9 Posts |
Posted - 2007-08-31 : 10:23:23
|
quote:
Originally posted by rmiao
Or use third party sql auditing tools.
Ok do you know any tool like that? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-31 : 15:08:45
|
| Lumigent's Audit DB is one. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-03 : 16:33:01
|
| you can run a job that retrieves information from master..sysprocesses and schedule every n-minutes...you just need to do a lot of filtering, probably use the datestamp to check for occurrences--------------------keeping it simple... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-03 : 21:44:45
|
| May unable to capture all activities that way. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-04 : 03:56:24
|
| you can enable C2 auditing that comes with sql server...._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 15:05:56
|
MAY being the keyword.. if there's a will and you don't have the money to spend, there's a wayquote:
Originally posted by rmiao
May unable to capture all activities that way.
--------------------keeping it simple... |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2007-09-11 : 01:55:35
|
| The best way out is to gather the data of profiler into a table, and write dml statements to massage the data and create a report of it. But that would be lot of work. The last resort if you can afford for third party tools is but an auditing tool.RoyalSher.*********The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
|
Track select statements.