SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auditing select statements


Auditing select statements

Author
Message
Kazoo1989
Kazoo1989
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 93
We write medical applications where knowing who views a specific patient's information is important. We have traditionally audited this type of information through calls made by middle tier routines to audit procedures in the backend. The method is not perfect as it cannot track events that occur through the database only.

In reading about the audit features of SQL 2008 we saw that it is possible to audit select statements. We set that up on a test table and it works but the output includes the bind variable in the where clause. Our example looks like this:

SELECT [columns] FROM [dbo].[patient] WHERE [patient_id]=@1

The "@1" is a problem. It doesn't define in the audit log which row was accessed.

Is there a way to customize what actually goes into the log? Is there another audit event or approach using the new audit features that could track that?

Thanks
cherie j sheriff
cherie j sheriff
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 29
It depends on what auditing tool you are using as to what information you will see when you read the log information. You can see the values of any variables passed in a select statement, but then I am using Iderra's tools (Compliance Manager) which is more robust. The only time this becomes an issue is when the developers write an application where all the connections to the database are made with some bogus login user. You can buy managment tools for that as well to see who is connecting (ports, IP & MAC addresses and LdAP usernames), but you are talking a lot of money for that much control. This also means performance managment also.

You can try using RedGate's SQL Restore to view the audit log and it will tell you the absolute values. This is a free tool last time I checked.
Kazoo1989
Kazoo1989
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 93
As per the initial post we are using the audit tool provided with SQL Server 2008 (enterprise). We are unlikely to be able to use a 3rd party tool so were hoping to find a solution based on the built-in.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25692 Visits: 1917
I believe the Audit object only records the exact SQL statement. I would have to test more to be sure.

K. Brian Kelley
@‌kbriankelley
jgnewberry
jgnewberry
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 13
I'm having the exact problem. Did you ever come up with a solution?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search