February 3, 2009 at 1:54 pm
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
February 3, 2009 at 3:41 pm
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.
February 3, 2009 at 5:51 pm
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.
February 4, 2009 at 2:07 pm
I believe the Audit object only records the exact SQL statement. I would have to test more to be sure.
K. Brian Kelley
@kbriankelley
September 5, 2009 at 1:29 pm
I'm having the exact problem. Did you ever come up with a solution?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply