Field Level Auditing using Change Data Capture - Part 1

  • David Balthrop

    SSC Veteran

    Points: 212

    Comments posted to this topic are about the item Field Level Auditing using Change Data Capture - Part 1

  • kmslogic

    SSC Rookie

    Points: 27

    How can you derive who made the field change after the fact?

  • tarek.omar

    Grasshopper

    Points: 15

    I will try this...feels tempting

    Tarek Omar

    Technical Manager / DBA

    myblog[/url%5D

  • Martin Nyborg

    SSC Enthusiast

    Points: 195

    In "normal" web application it is not the logged in user that access the database, but a single trusted nt user or a sql user in the connecting string.

    So where is the username coming from?

    Best Regards Martin Nyborg - Perfection is a process, not an end-point.

  • nadersam

    SSCertifiable

    Points: 6541

    Very nice feature, thanks for explaining it.

    But what if someone change data through SQL Management studio, how can i track that?.

    Thanks

  • rob.symonds

    SSC Veteran

    Points: 200

    Dammit, I got all excited there until I realised it was an Enterprise feature.

  • -=JLK=-

    SSCertifiable

    Points: 5265

    Nice article, thanks for the explanation and great examples. I'm quite tempted to change from my current auditng techniques to this as it would be much more efficient, however I do have one concern, how doe the CDC tables handle DDL changes? Our databases are constantly evolving (usually adding new fields, but some name changes). I use Powerdesigner to model the database and create change scripts, but it wouldn't affect the CDC tables, so would I have to drop the CDC affected tables and recreate them?

  • LSCIV

    Old Hand

    Points: 329

    kmslogic (11/1/2011)


    How can you derive who made the field change after the fact?

    When my team tried it we couldn't get CDC to capture the hostname of the server/workstation/laptop that made the change. This bit of info is a must for our environment. It's rare for someone to make an unauthorized change under their own credentials, it's usually a case of someone hijacking the application credentials to make the change. Not that it happens often, but we've caught all of those individuals thanks to our current tool (apex sql audit) capturing the hostname. Just my .02

  • ShawnTherrien

    Default port

    Points: 1481

    Martin Nyborg (11/1/2011)


    In "normal" web application it is not the logged in user that access the database, but a single trusted nt user or a sql user in the connecting string.

    So where is the username coming from?

    The SYSTEM_USER function returns the context of the user who is running the transaction. This is what I've used in the past to track who has made a change in a Trigger.

    Your application (ssms, web app, etc) is connecting using a SQL or domain account in it's connection string. This value is listed under the changed record.

    So your web app might show ModifiedBy: WebApplicationLogin and you in SSMS might show ModifiedBy: domain\MNyborg.

    With the application using a single account and using no impersonation, it's an extremely limited auditing feature, but in my experience it's nice to know if the application made a change or if a specific developer made a change. It would be amazing to get the specific user of the application who made the change, but my web development team doesn't want to deal with it.

  • ShawnTherrien

    Default port

    Points: 1481

    This article looks very promising.

    I've got my own trigger based CDC (field at a time) capture set up created by dynamic SQL pointed at a table.

    I would prefer to have this working so we don't have that complexity, but I recall we got nervous about DDL changes and my triggers handled that case more gracefully I recall.

    It's been about a year, but I think when you reconfigure a CDC on a table, it wipes out the audit log it's been keeping, so you have to be really careful about backing up that audit log when you add a new record. My triggers just won't record the new fields until I run my script on the table again, but the audit table always remains.

    It's been a while, I'm going to run some tests with this

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    unless there is a way to see who made the change this is useless. we have analysts who use SSMS or MS Access to make changes to data outside of applications. sometimes we even have apps make changes that we need to track down. we use audit tables and triggers

  • SQLRNNR

    SSC Guru

    Points: 281205

    Nice article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • David Balthrop

    SSC Veteran

    Points: 212

    yes, DDL changes are not captured for existing CDC change tables. The DDL statements are saved in the cdc.ddl_history table for what that is worth.

    When a DDL statement is made to a table being tracked by CDC, to capture the changes, you have to copy the existing change table data to a temp table, make the same DDL changes to that temp table, stop and restart CDC on the table to start fresh with the changes, copy the temp table data to the new, updated change table, and then reset the minimum LSN in the cdc.change_tables table. This is a script I recently used to updated a table that was using CDC:

    -- copy existing change data to a temp table

    SELECT * INTO cdc.dbo_contract_item_CT_Temp FROM cdc.dbo_contract_item_CT

    -- add changes to temp table

    ALTER TABLE cdc.dbo_contract_item_CT_Temp

    ADD [energy_benefit_savings] [int] NULL

    -- disable CDC on contract_item table

    EXEC sys.sp_cdc_disable_table

    @source_schema = N'dbo',

    @source_name = N'contract_item',

    @capture_instance = N'dbo_contract_item'

    GO

    -- re-enable CDC on contract_item, this will include the new column

    EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N'contract_item',

    @role_name = NULL, -- gating role if needed

    @filegroup_name = N'eContractsCDC', -- use same filegroup

    @supports_net_changes = 1

    GO

    -- insert temp table data into new change table

    INSERT INTO cdc.dbo_contract_item_CT

    SELECT * FROM cdc.dbo_contract_item_CT_Temp

    -- update start LSN to match temp table

    UPDATE cdc.change_tables

    SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_contract_item_CT_Temp)

    WHERE capture_instance = 'dbo_contract_item'

    -- drop temp table

    drop table cdc.dbo_contract_item_CT_Temp

  • Yang-703993

    SSC Eights!

    Points: 995

    Please don't confuse and misuse this feature. CDC is a good tool for data analysis purpose, but not auditing.

    If you are working on DW and trying to analyze user bbehave CDC help. if you are looking for auditing, CDC is not your choice.

    No Signature

  • jswong05

    Hall of Fame

    Points: 3503

    This audit only deals with someone directly made database connection and made changes, if that was a designated single app login, that has to be recorded by the app. We have one app that does so by recording an auditlog table. Reminder to archive and cleanup that because it can get big fast.

    Jason

    http://usa.redirectme.net

    Jason
    http://dbace.us
    😛

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply