PCI Compliance...

• Thomas J. Theobald

  SSC Journeyman

  Points: 77

  More actions

  April 28, 2006 at 1:26 pm

  #93899

  ...seems to require logging access to card number fields.  Which unfortunately includes reads. 

  Does anyone have any suggestions about how to address logging of column access within a table through a Select statement? 

  Thanks in advance -

   

    T

  ---

  Kindest Regards,

  Thomas J. Theobald
  midwayusa.com

  Umpteen zillion products and counting for your favorite shooting sports...(and all run with SQL Server and Delphi)...come visit us at the website above!

• May 1, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Andrew Stanton

  Ten Centuries

  Points: 1115

  More actions

  May 2, 2006 at 2:55 pm

  #635464

  Steve Jones had an article about selective auditing a few days ago. Check it out,

  http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart4selectiveauditing.asp

  Another option may be to go DOD and turn on C2 auditing,  

  http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

  Or use Oracle, (probably the best option, not knocking MS, but I dont think even 2005 has FGA equivalent)

  http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html

  Or a monitoring product

  http://www.datanomad.com/

• Mike-263299

  Ten Centuries

  Points: 1169

  More actions

  June 6, 2006 at 7:33 am

  #641971

  Could you create a trace (to emulate Profiler) to do it?

  What about Notification Services?

• Mike-263299

  Ten Centuries

  Points: 1169

  More actions

  June 6, 2006 at 10:19 am

  #642076

  Actually, using SQL 2005 I think I'm on the verge of figuring this out using an Event Notification.

  However, I'm stuck.  I'm trying to use the trace event AUDIT_DATABASE_OBJECT_ACCESS_EVENT , but can't figure out how to specify which table.column that I want to audit.  So far I haven't found a thing online except for BOL, which just tells me the columns that are available to this trace.  Anyone know what to do with this event and how to get it up and running?  Below is the way I created the Event Notification:  (steps are commented so you can do it step by step, etc...)

  If anyone can push me in the right direction I would really appreciate it.  This would be a huge win and would basically allow us to use SQL2005 for PCI systems instead of going to Oracle...

  Thanks!

  Mike

  *************************************************************

  --Create Table dbo.EventLog (eventinfo XML)

  --Alter database adventureworks set enable_broker

  /*

  Create Procedure dbo.EventLogProc

  AS

  DECLARE @message_body XML

   ;WAITFOR(

      RECEIVE TOP(1)

      @message_body=message_body

      FROM EventLogQueue

    &nbsp, TIMEOUT 2000 ;

   IF @@rowcount=0

    RETURN

   INSERT INTO dbo.EventLog(eventinfo)

   VALUES (@message_body)

  */

  /*Create Queue EventLogQueue

  WITH STATUS = ON,

  ACTIVATION (

   PROCEDURE_NAME = dbo.EventLogProc,

   MAX_QUEUE_READERS = 5,

   EXECUTE AS SELF)

  */

  /*

  CREATE SERVICE EventLogService

  ON QUEUE EventLogQueue

  (

   [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]

  )

  */

  /*

  CREATE ROUTE EventLogRoute

  WITH SERVICE_NAME = 'EventLogService',

  ADDRESS = 'LOCAL'

  */

  Drop Event Notification EventLogNotification ON SERVER

  CREATE EVENT NOTIFICATION EventLogNotification

  ON SERVER

  FOR AUDIT_DATABASE_OBJECT_ACCESS_EVENT

  TO SERVICE 'EventLogService', 'Current Database'

  select * from Production.Product

  select * from dbo.eventlog