Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auditing select statements Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 24, 2014 10:12 AM
Points: 4, Visits: 78
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
Post #649326
Posted Tuesday, February 3, 2009 3:41 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 31, 2009 1:07 PM
Points: 50, 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.
Post #649415
Posted Tuesday, February 3, 2009 5:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 24, 2014 10:12 AM
Points: 4, Visits: 78
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.
Post #649453
Posted Wednesday, February 4, 2009 2:07 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
I believe the Audit object only records the exact SQL statement. I would have to test more to be sure.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #650371
Posted Saturday, September 5, 2009 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 1:45 PM
Points: 7, Visits: 13
I'm having the exact problem. Did you ever come up with a solution?
Post #783382
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse