Auditing: Change Data Capture(CDC)

Overview:

Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version.

Change data capture records insert, update, and delete activity that applies to a SQL Server table. if you want to store the audit information about the UPDATE, INSERT, DELETE operations then enable the SQL CDC on that table.

Note:  SQL Server writes-

• One record for each INSERT, 
• One record for each DELETE statement and 
• Two records for each UPDATE statement, (the first record reflect the data before the change and the second record reflect the data after performing the change).

Change Data Capture Data Flow:

Enabling Change Data Capture(CDC):

Before enabling the Change Data Capture on a specific table, a member of the SYSADMIN fixed server role must first enable the database for change data capture, using the sys.sp_cdc_enable_db system stored procedure, as shown below:

-Enable CDC on Database.

USE [CDC_Audit]

GO

EXEC sys.sp_cdc_enable_db
GO

-Check CDC status on Database.

Select name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1

-After enabling the SQL CDC at database level, there are six tables are automatically created under system tables, as shown below.

-Some system stored procedures are also created automatically, as shown below.

-Enabling CDC on Table.

USE [CDC_Audit]
GO
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo', 
@source_name   = N'Auditor_Info', 
@role_name     = NULL, 
@filegroup_name = NULL, 
@supports_net_changes = 0
GO

-After enabling the SQL CDC at table level, two Jobs (capture and cleanup) are created automatically under SQL Agent jobs.

-To check CDC status on Table.

SELECT name, is_tracked_by_cdc

FROM sys.tables
WHERE is_tracked_by_cdc = 1

Auditing DML Statement After Enabled CDC Features: 

Here you will use DML statement to check how these changes reflected after CDC enabled on Table.

-For INSERT Statement:  Insert one row into CDC enabled table to check how this change will be audited.

INSERT INTO [CDC_Audit].[dbo].[Auditor_Info]
VALUES(1002,'Satish','Mumbai')

The modified data will be written into [cdc].[dbo_Auditor_Info_CT] table in our case. You can find this table in your System Table under CDC enabled database.

Here you can select all information with Operation Type 2 condition and you will get complete details about INSERT operations.

SELECT * from [CDC_Audit].[cdc].[dbo_Auditor_Info_CT]

WHERE __$operation = 2    --SELECT

-For UPDATE Statement: Update one row into CDC enabled table to check how this change will be audited.

UPDATE [CDC_Audit].[dbo].[Auditor_Info]

SET [Auditor_City] = 'CANADA' WHERE Aud_ID = 1002

You can track the UPDATE statement using trough operation type 3 and 4.

Operation type = 3, will display the data before the update.

Operation type = 4, will display the data after the update.

SELECT * from [CDC_Audit].[cdc].[dbo_Auditor_Info_CT]
WHERE __$operation = 3 or __$operation = 4   --UPDATE

-For DELETE Statement: Delete one row from CDC enabled table to check how this change will be audited.

DELETE [CDC_Audit].[dbo].[Auditor_Info]
WHERE Aud_ID = 1002

You can track DELETE records using trough Operation type 1.

SELECT * from [CDC_Audit].[cdc].[dbo_Auditor_Info_CT]
WHERE __$operation = 1    --DELETE

Disabling Change Data Capture(CDC):

-Disable the CDC on table level:

USE [CDC_Audit]

GO
EXEC sys.sp_cdc_disable_table  
@source_schema = N'dbo', 
@source_name   = N'Auditor_Info', 
@capture_instance = N'dbo_Auditor_Info', 
GO

-To check CDC status on Table.

SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE is_tracked_by_cdc = 1

-Disable the CDC on database level:

USE [CDC_Audit]
Go
Exec sys.sp_cdc_disable_db

-To check CDC status on Table.

Select name, is_cdc_enabled from sys.databases where is_cdc_enabled = 1

Recommendation:

You can also query the function to check the modified data. In Our case function name is : cdc.fn_cdc_get_all_changes_dbo_Auditor_Info

This enumeration function is created at the time that a source table is enabled for change data capture.The function name is derived and uses the format  cdc.fn_cdc_get_all_changes_capture_instance where capture_instance is the value specified for the capture instance when the source table is enabled for change data capture. 

Permission Required : sysadmin fixed server role or db_owner fixed database role.

It uses the function cdc.fn_cdc_get_all_changes_Auditor_Info to report all the currently available changes for the capture instance Auditor_Info.

When the 'all' row filter option is specified, each change has exactly one row to identify the change. 

DECLARE @from_lsn binary(10), @to_lsn binary(10); 

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Auditor_Info'); 
SET @to_lsn   = sys.fn_cdc_get_max_lsn(); 
SELECT * FROM cdc.[fn_cdc_get_all_changes_dbo_Auditor_Info] (@from_lsn, @to_lsn, N'all')

When the 'all update old' option is specified, update operations are represented as two rows: one containing the values of the captured columns before the update and another containing the values of the captured columns after the update.

DECLARE @from_lsn binary(10), @to_lsn binary(10); 

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Auditor_Info'); 
SET @to_lsn   = sys.fn_cdc_get_max_lsn(); 

SELECT * FROM cdc.[fn_cdc_get_all_changes_dbo_Auditor_Info] (@from_lsn, @to_lsn, N'all update old')

In the next article, we will discuss about more option in regards to SQL auditing. 

                                                             Stay Tuned!!!!!

               

--By Satish K....

Stay In Touch: