Suppression accidentelle de ligne : comment retrouver le coupable ?

Jeudi, octobre 6, 2011
By David BAFFALEUF in SQL Server (dbaffaleuf@capdata.fr) [59 article(s)]

C’est vrai qu’il y a des outis, Change Data Capture et tout l’arsenal des Database Audit Specifications. Mais pour retrouver qui a supprimé les lignes dans la table T1 le 17 septembre dernier entre 12h00 et 14h00 il y a encore plus simple, pour peu qu’il y ait des backups de transactions.

La fonction qui tue:

Dans un article précédent, nous avons couvert une fonction table valuée fort intéressante qui permet de relire à l’intérieur d’une sauvegarde de journal: fn_dump_dblog(). Nous allons nous en servir pour connaître cette fois l’identité du coupable. La table concernée par le DELETE est la table T1, il faut commencer par récupérer sa valeur de allocation_unit_id:

select allocation_unit_id from sys.allocation_units AU inner join sys.partitions P
on P.hobt_id = AU.container_id where P.object_id = object_id('T1')
allocation_unit_id
---------------------
72057594043301888

Ensuite, rechercher dans les backups transactionnels des opérations de suppression de lignes sur cet objet:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT) where Operation  = 'LOP_DELETE_ROWS' and AllocUnitId = 72057594043301888

Current LSN             Operation          Transaction ID AllocUnitId         
----------------------- ------------------ -------------- --------------------
00000025:00000032:0003  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0005  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0006  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0007  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0008  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
...

On récupère le LSN de la transaction pour retrouver le BEGIN TRAN correspondant:  0000:00000a9d:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
 ,[Begin Time] ,[Transaction Name] ,[End Time] ,[Description]
 ,[Transaction SID]
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where Operation  = 'LOP_BEGIN_XACT' and [Transaction ID] = '0000:00000a9d'

Current LSN             Operation                       Transaction ID AllocUnitId        
----------------------- ------------------------------- -------------- --------------------
00000025:00000032:0001  LOP_BEGIN_XACT                  0000:00000a9d  NULL                

 Begin Time               Transaction Name                  Description                                  
------------------------ --------------------------------- -----------------------------------------------
2011/10/05 15:21:50:510  tralalala                         tralalala;0x75a770d73d6bf54bb0dc07725254ae57    

Transaction SID
------------------------------------
0x75A770D73D6BF54BB0DC07725254AE57

Remarque intéressante, [Transaction SID] (en rouge) correspond au SID de l’utilisateur dans la base…

Le dénouement…

Il ne reste plus qu’à dégainer la requête sur sys.database_principals:

select name from sys.database_principals where sid = 0x75A770D73D6BF54BB0DC07725254AE57
name
-------------------
user1

A+

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags: ,

2 Responses to “Suppression accidentelle de ligne : comment retrouver le coupable ?”

  1. Salut David,

    Intéressant ton article.

    Je dirais juste pour mettre un peu de zizanie :) … que ceci n’est valable que si le sid de l’utilisateur ne change dans le temps. Tu me diras que le SID ne change pas tous les 4 matins …

    Les audits ont le mérite de graver cette information dans le marbre ..

    Merci pour ton article

    ++

    #18665
  2. En effet, je ne vois pas comment modifier le SID (on ne peut pas le faire via ALTER LOGIN, il faudrait rebooter en -m et faire la modif à la main sous DAC). Reste la suppression / recréation du login1.

    Souvent les audits ne sont pas en place au moment où le problème survient, c’est donc une technique primitive et avec les moyens du bord qui vaut ce qu’elle vaut. Merci pour ton commentaire ! A+

    #18666

Leave a Reply