Logging uses of SELECT ?

Scenario:

-I'm brand new to SQL Server 2000

-I have a vendor supplied application running on SQLServer 2k.

-I need to log which of my users has seen which data (from a
particular table). It's a healthcare privacy thing.

-The application does not do this and the vendor has no interest in
implementing it, though it seems pretty easy if one has the app code.

So I don't control the application or its code but I need to catch
who's seen what. Note that I don't have to catch it all. In other
words, I don't need to catch every instance, only whether user X has
seen patient Y's data at all. So I'm talking about a fairly small data
set. The end result I'm heading toward is a case in which a patient
demands to know who's seen his health records. I go to a db table and
say "select username from <viewlogtable> where patient_id = XXX".

How do I do that? It'd be easy if I could it were possible to write a
select trigger, or if I had some hooks into the app, but as it is I am
stumped. This is trickier than logging updates and inserts...

Thanks,
JohnThere is no such thing as a SELECT trigger. You may be able to satisfy
the requirement by analyzing the SQL log. There are some third party
products that can be of use. Do a google on "Lumigent" for literature
on their product.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".

Unfortunately I can't see any way of doing that. There's no such thing as a
select trigger.

As another poster said, Lumigent may do this, but I doubt it since I don't
believe Selects are logged at all. (wouldn't make much sense.)

Another option is to run the SQL Profiler and setup a profile to look for
JUST selects, etc. This solution actually might work fairly well
short-term. And even long-term if you really tighten the profile down to
record ONLY what you need.

> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...

Really what the vendor should be doing is all access through stored procs.
Then the stored proc could handle security and handle logging.

> Thanks,
> John|||You would have to ensure that the users had to view that data by executing a
stored procedure, not with a direct SELECT statement.
I'm not sure how much control you have over the application, or if you can
modify it, but this is certainly a way of 'logging' SELECT statements
made against a table or set of tables. If you can, in fact, edit the
application then you can edit it to execute a procedure that simply inserts
into the log and selects as this <simplified>:

DECLARE PROCEDURE ViewPatientRecords
(
@.PatientID int
,@.UserID int
)
AS

INSERT INTO ViewLogTable(Patient_ID, Viewer_ID, View_Date)
VALUES(@.Patient, @.UserID, GETDATE())

SELECT *
FROM Patient_Record
WHERE Patient_ID = @.PatientID

"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".
> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...
> Thanks,
> John