locked
SQL Server Audit - column argument does not appear to work RRS feed

  • Question

  • I have been tasked with implementing auditing, for PCI Compliance reasons.  The SQL Server Audit feature has turned out to be a great solution.  However, I am coming across a major problem that I am hoping someone can help me with.

    I need to limit the auditing to only specific columns.  The 'Alter Database Audit Specification' MSDN page indicates you can limit to column name:

    ALTER DATABASE AUDIT SPECIFICATION audit_specification_name
    {
        [ FOR SERVER AUDIT audit_name ]
        [ { { ADD | DROP } ( 
               { <audit_action_specification> | audit_action_group_name } 
                    ) 
          } [, ...n] ]
        [ WITH ( STATE = { ON | OFF } ) ]
    }
    [ ; ]
    <audit_action_specification>::=
    {
          <action_specification>[ ,...n ]ON [ class :: ] securable [ ( column [ ,...n ] ) ] 
         BY principal [ ,...n ] 
    }
    
    <action_specification>::=
    {
            action [ ( column [ ,...n ] ) ]
    }
    
    column

    Column name (if applicable) on the securable.

    When I try to use the column argument, however, I get the following error back:

    Msg 33211, Level 15, State 1, Line 0
    A list of subentities, such as columns, cannot be specified for entity-level audits.


    My database audit specification looks like this:

    GO

    USE database

    CREATE DATABASE AUDIT SPECIFICATION audit_name
    FOR SERVER AUDIT Audit
      ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE ON orders (order_cc_encrypted) BY public),


    Has anyone been able to make this work and/or is there some workaround that I can put in place, if this is not possible?  Any guidance would be greatly appreciated.

    -Chris E

    Friday, September 14, 2012 7:05 PM

Answers

  • Hi, as far as I know, we need to audit the hole table if we want to audit some columns. Do these columns contain confidential information rather than other columns? If so, we can create a new table to store these confidential columns, and create another table to store other columns. In this way, we can audit the confidential table with SQL Server Audit, and don’t need to worry about other columns.

    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by cennis82 Wednesday, October 3, 2012 7:33 PM
    Monday, September 24, 2012 6:43 AM

All replies

  • Hi cennis82,

    According to the MSDN online document, Database-level audit actions (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE and REFERENCES) do not apply to Columns. We can enable Change Data Capture to capture the change occurs with specific table column. For example, we have a database named “test”, and there is a table named “IDNameTable” under “dbo” schema, this table has a column named “ID” as its primary key, and column “name” is the column we want to monitor, and there are some other column: column2, column3… We can execute the following command to enable Change Data Capture for table “IDNameTable". And then a table named “cbc. dbo_IDNameTable_CT” will generated under “System Tables”.

    USE test;
    GO
    
    EXEC sys.sp_cdc_enable_db
    GO
    
    
    EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo'
      , @source_name = N'TableName'
      , @role_name = N'cdc_admin'
      , @captured_column_list = N'ID,name' 
    GO

    When change occurs with column “name”, a record will be recorded into table “cbc. dbo_IDNameTable_CT”. For more detail information, please refer to the following document:

    SQL Server Audit Action Groups and Actions:
    http://msdn.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

    Enable and Disable Change Data Capture (SQL Server):
    http://msdn.microsoft.com/en-us/library/cc627369.aspx

    sys.sp_cdc_enable_table (Transact-SQL):
    http://msdn.microsoft.com/en-us/library/bb522475.aspx


    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by cennis82 Monday, September 17, 2012 2:59 PM
    • Unmarked as answer by cennis82 Monday, September 17, 2012 2:59 PM
    • Edited by Allen Li - MSFT Tuesday, September 18, 2012 8:03 AM
    Monday, September 17, 2012 6:11 AM
  • Will the Change Data Capture functionality capture selects on the monitored column?
    • Edited by cennis82 Monday, September 17, 2012 2:47 PM
    Monday, September 17, 2012 2:42 PM
  • Hi, Change Data Capture functionality will not capture the select operation on the column, it only capture the change occurs with the column, for example: insert, update, delete. If you want to audit the “select” option, we can use SQL Server audit feature to audit the hole table, for example:

    USE master;
    go
    
    CREATE SERVER AUDIT [AUDITName]  
    TO FILE   
    (  FILEPATH = 'D:\SQLData'  
       ,MAXSIZE = 100 MB  
       ,MAX_ROLLOVER_FILES = 2147483647  
       ,RESERVE_DISK_SPACE = OFF  
    )  
    WITH  
    (  QUEUE_DELAY = 2000  
       ,ON_FAILURE = CONTINUE  
    )
    
    use DatabaseName;
    go
    
    CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
    FOR SERVER AUDIT AUDITName
       ADD (SELECT ON TableName BY public)
    
    and use Change Data Capture to captures the change occurs with special column.

    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 18, 2012 10:22 AM
  • Thanks for the response Allen.  Unfortunately, the customer doesn't want to do an audit on the whole table, so I may need to think about another method of capturing selects on this column.  At this point, I'm thinking a trigger may be the answer, but will post my resolution here.
    Wednesday, September 19, 2012 1:47 PM
  • Not sure if this will help with this particular project but have you looked at what LOGbinder SQL can do for your SQL Server auditing.  You can check that out at http://www.logbinder.com/products/LOGbinderSQL/default.aspx

    And if you're setting up auditing, Randy Franklin Smith has a free SQL Auditing wizard.  It's here towards the bottom of the page:  http://www.ultimatewindowssecurity.com/sqlserver/default.aspx

    Like I said, this might not be of any help with what you're doing now but if you're involved in the SQL Server auditing I'm sure it will be help down the road.

    Wednesday, September 19, 2012 3:50 PM
  • Thank you bjvista, I'll definitely check these out.
    Wednesday, September 19, 2012 4:13 PM
  • Hi, as far as I know, we need to audit the hole table if we want to audit some columns. Do these columns contain confidential information rather than other columns? If so, we can create a new table to store these confidential columns, and create another table to store other columns. In this way, we can audit the confidential table with SQL Server Audit, and don’t need to worry about other columns.

    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by cennis82 Wednesday, October 3, 2012 7:33 PM
    Monday, September 24, 2012 6:43 AM
  • Allen, this is what I am going to suggest (another table with just the columns(s) that need auditing), if needed.  At this point, we've moved forward with the auditing on the table, and will evaluate performance over the next several weeks to determine impact, if any.
    Wednesday, October 3, 2012 7:32 PM