How to log uses of SELECT

Hello,
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>