Wie heeft mijn database aangepast? SQL Change Data Capture vertelt het je

SQL Change Data Capture – In het huidige informatietijdperk wordt data in enorme hoeveelheden verzameld. IT-systemen hebben de toegang tot de data steeds sneller en makkelijker gemaakt. Hiermee is het echter ook steeds makkelijker geworden om de data te misbruiken.

In het kader van de wet- en regelgeving rondom de privacy wordt het risico rondom de beveiliging van data steeds groter. Gelukkig groeit ook het bewustzijn bij de ‘gebruiker’ steeds meer.

Vanuit de technische kant bekeken kan heel veel oneigenlijk gebruik voorkomen worden, bijvoorbeeld door de toegang tot de data zoveel mogelijk af te schermen. In de praktijk blijkt dit vaak erg lastig omdat een dergelijke beveiliging de operationele processen enorm kan frustreren. Als we niet kunnen voorkomen dat mensen toegang hebben tot de data, moeten we bijhouden hoe de data gebruikt wordt. We zijn dan in staat om ‘verdachte’ transacties te onderzoeken, zodat de juiste acties kunnen worden ondernomen.

In deze blog laat ik zien hoe je met behulp van de feature Change Data Capture wijzigingen in een SQL database kan monitoren. In dit voorbeeld gebruiken we de voorbeeld database AdventureWorks die door Microsoft gratis ter beschikking wordt gesteld. Zie de volgende link om deze database te downloaden: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Change Data Capture voor de database ‘enablen’

Voordat je Change Data Capture (CDC) voor een database kan gebruiken moet deze eerst worden ‘enabled’.

Open hiervoor SQL Management Studio en voer de volgende query uit:

EXEC sys.sp_cdc_enable_db

Change Data Capture voor een tabel ‘enablen’

De volgende stap is CDC enablen voor een specifieke tabel waarvan de wijzigingen gemonitord moeten worden.

Voer hiervoor de volgende query uit:

EXEC sys.sp_cdc_enable_table
@source_schema = N’Person’,
@source_name   = N’Address’,
@role_name = NULL,
@capture_instance = ‘cdc_Person’

Uiteraard moeten de verschillende variabelen worden aangepast voor de specifieke situatie.

Als het uitvoeren van deze taak gelukt is, worden er twee SQL Server agent jobs aangemaakt. Een capture job en een clean_up job:

Belangrijk aandachtspunt is dat CDC gebruikt maakt van deze SQL Server Agent Jobs. Als deze jobs (tijdelijk) niet werken zullen de wijzigingen ook niet geregistreerd worden.

Wijzigingen bekijken

Vóór de wijziging zijn de volgende waarden opgenomen voor één van de regels in de tabel Person.Address.

Vervolgens is het BankAccount nummer aangepast naar de waarde 987654321:

Doordat CDC is ingesteld voor deze tabel kunnen we via de volgende query de wijziging traceren:

DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn(‘cdc_Person’);
SET @end   = sys.fn_cdc_get_max_lsn();
SELECT __$start_lsn
, CASE
WHEN __$operation = 1 THEN ‘DELETE’
WHEN __$operation = 2 THEN ‘INSERT’
WHEN __$operation = 3 THEN ‘PRE-UPDATE’
WHEN __$operation = 4 THEN ‘POST-UPDATE’
ELSE ‘ONBEKEND’
END AS Operation
, [AddressID]
,[AddressLine1]
,[City]
,[StateProvinceID]
,[PostalCode]
,[BankAccount]
FROM cdc.fn_cdc_get_all_changes_cdc_Person(@begin, @end, N’all update old’)

Het resultaat van deze query is als volgt:

Je ziet zowel de oorspronkelijke regel als de aangepaste regel zodat elke wijziging precies te traceren is.

Afsluiting

Change Data Capture is een interessante feature om aanpassingen op essentiële tabellen in een database te monitoren. CDC heeft uiteraard wel een effect op de performance en opslagcapaciteit van de database. De feature moet dan ook met beleid worden ingezet alleen op de tabellen waar dit noodzakelijk voor is.