Who's been selecting data from my table?!

[RupertGiles]

Hi,
Long time since I've been reading much here!

What I need to do is to audit not changes but access.

Ideally would like to do it for just one table (that has more sensitive data), of course I could log all logins and attempts. I figured my way would be better for
- performance, less writes
- easier for me to keep an eye on, as we will only be recording a small proportion of transactions.


Anyway look forward to any views/ideas, thanks

Giles

 

[mrdenny]

If you are using SQL Server 2005 or lower then pretty much your only option will be to setup a SQL Trace that runs automatically and have it log to a file on the SQL Server. Setup filters and objects correctly to log access to the table.

If you are using SQL Server 2008 then you can use the native auditing which comes with it which will log access to the table to files which you can then load into tables in a data warehouse.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[RupertGiles]

Sorry I'm stuck on 2k at the moment, forgot to mention it!

 

[SQLWilts]

There is no way to set a trigger to audit a select - so...
If the data is that sensitive, then deny direct select on the table. Then only allow access to the table via stored procedures - as part of the sproc, you can write audit info to an audit table, including trapping the login ID that accessed it.

 

[mrdenny]

That won't do anything for auditing what people who are members of the sysadmin fixed server role or db_owner fixed database role are doing which is usually required for an audit. Especially since SQL Tables can be edited by the DBA.

I'm assuming you are dealing with this for SOX, HIPPA, etc?

Setup SQL Profiler and script out the trace. Setup a procedure with the output of that so that the trace starts when SQL Starts.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[RupertGiles]

Hi Mr Denny,
This is required for the PCI DSS. It's a table that is storing some card details.

As far as my interpretation is, I need to be able to show who has accessed this table.

Thanks again

 

[mrdenny]

Then SQL Profiler will be your best option.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog