SQL Server'da Tabloyu Kim Sildi? Bulmanın Üç Yolu ve Sınırları

Sabah 09:12. Gece raporu boş geldi, ekip kanalına tek bir satır düştü: "Orders tablosu yok." Birkaç dakika içinde ikinci soru gelir ve asıl zor olan odur: kim sildi, ne zaman sildi? Bu yazı SQL Server'ın bu soruya bugün hangi kaynaklarla cevap verebildiğini, her kaynağın neyi göremediğini ve bir dahaki sefere cevabın hazır olması için önceden neyin kurulması gerektiğini adım adım anlatır.

Önce: Silinen Tablo mu, Silinen Satırlar mı?

İlk iş olayın türünü ayırmaktır, çünkü iki durumun izi farklı yerlerde kalır. DROP TABLE şemayı değiştirir: tablo nesnesi yok olur. DELETE ya da TRUNCATE TABLE ise veriyi siler, tablo yerinde durur. Tablo görünüyor ama içi boşsa ikinci durumdasınız. Aşağıdaki üç yoldan ilki yalnız birinci durumu görür; diğer ikisi ikisini de görebilir.

Yol 1: Varsayılan İzleme (Default Trace)

SQL Server kurulumla birlikte arka planda hafif bir izleme çalıştırır. Bu izleme nesne oluşturma, silme ve değiştirme olaylarını; olayı yapan oturum açma adını, makine adını, uygulama adını ve zamanı kaydeder. SQL Server Management Studio'daki "Schema Changes History" raporu da bu izlemeyi okur. Önce açık olup olmadığına ve dosyanın nerede durduğuna bakın:

SELECT id, path, max_size, max_files, start_time
FROM sys.traces
WHERE is_default = 1;

Satır gelmiyorsa izleme kapatılmıştır ve bu yol size cevap vermez. Satır geliyorsa aşağıdaki sorgu tablo adına göre olayları listeler. fn_trace_gettable verilen dosyadan başlayıp sonraki dosyaları okur; sys.traces en yeni dosyayı gösterdiği için sorgu klasör yolunu alıp log.trc ile en eski dosyadan başlatır. EventClass 46 oluşturma, 47 silme, 164 değiştirme olayıdır. Her işlem iki satır üretir (başlangıç ve kesinleşme); EventSubClass = 1 yalnız kesinleşen işlemi bırakır.

DECLARE @path nvarchar(260) =
  (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX(N'\', REVERSE(path)), 260)) + N'log.trc'
   FROM sys.traces WHERE is_default = 1);

SELECT t.StartTime, e.name AS EventName, t.DatabaseName, t.ObjectName,
       t.LoginName, t.NTUserName, t.HostName, t.ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN sys.trace_events AS e ON e.trace_event_id = t.EventClass
WHERE t.EventClass IN (46, 47, 164)
  AND t.EventSubClass = 1
  AND t.ObjectName = N'Orders'
ORDER BY t.StartTime;

Sınırları. Varsayılan izleme beş dosyayı 20 MB'lık parçalar halinde döndürür; yoğun bir sunucuda bu, birkaç saatlik geçmiş demektir ve olay o pencerenin dışındaysa iz yoktur. Yalnız şema olaylarını tutar: DELETE ve TRUNCATE görünmez. Bir sistem yöneticisi sp_configure 'default trace enabled', 0 ile kapatabilir. SQL Trace altyapısı Microsoft tarafından kullanımdan kaldırılacak özellikler arasında duyurulmuştur; bugün çalışır ama yeni bir kontrolün temeli olmamalıdır.

Yol 2: İşlem Günlüğü

Her değişiklik önce işlem günlüğüne yazılır ve günlükteki her işlemin başlangıç kaydı, işlemi başlatan oturumun güvenlik kimliğini (SID) taşır. Belgelenmemiş fn_dblog fonksiyonu bu kayıtları okur. Aşağıdaki sorgu silme ve tablo düşürme işlemlerini, zamanını ve oturum açma adını getirir:

SELECT [Transaction ID], [Begin Time], [Transaction Name],
       SUSER_SNAME([Transaction SID]) AS LoginName
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_BEGIN_XACT'
  AND [Transaction Name] IN ('DELETE', 'DROPOBJ', 'TRUNCATE TABLE')
ORDER BY [Begin Time];

Belirli bir işlemin hangi tabloya dokunduğunu görmek için aynı [Transaction ID] değerine sahip satırlarda AllocUnitName kolonuna bakılır.

Sınırları. Fonksiyon belgelenmemiştir ve Microsoft tarafından desteklenmez; sonucu bir ipucu olarak kullanın, tek kanıt olarak değil. Kayıtlar yalnız günlük yeniden kullanılmadıkça durur: basit kurtarma modelinde bir kontrol noktasından (checkpoint) sonra, tam kurtarma modelinde günlük yedeği alındıktan sonra o bölüm silinebilir. Yoğun bir üretim sunucusunda günlüğü taramak ağırdır; mümkünse yedekten geri yüklenmiş bir kopyada çalıştırın. Gösterdiği isim oturum açma adıdır: işi uygulama hesabı yaptıysa göreceğiniz uygulama hesabıdır, kişi değil.

Yol 3: Önceden Kurulmuş SQL Server Audit

İlk iki yol olaydan sonra iz arar. Güvenilir cevap, olaydan önce kurulmuş bir denetimden gelir. SQL Server Audit, SQL Server 2016 SP1'den bu yana bütün sürümlerde veritabanı düzeyinde kullanılabilir. Aşağıdaki örnek, şema değişikliklerini ve dbo şemasındaki her DELETE ifadesini kim çalıştırırsa çalıştırsın dosyaya yazar:

USE master;
CREATE SERVER AUDIT ChangeTrail
  TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 50)
  WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT ChangeTrail WITH (STATE = ON);

USE SalesDb;
CREATE DATABASE AUDIT SPECIFICATION SchemaAndDeletes
  FOR SERVER AUDIT ChangeTrail
  ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  ADD (DELETE ON SCHEMA::dbo BY public)
  WITH (STATE = ON);

Olay anında dosyalar şöyle okunur. client_ip ve application_name kolonları SQL Server 2017 ve sonrasında vardır:

SELECT event_time, action_id, server_principal_name,
       client_ip, application_name, database_name, object_name, statement
FROM sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE object_name = N'Orders'
ORDER BY event_time;

Dikkat edilecekler. DELETE ... BY public herkesi kapsar; uygulamanın kendi silmeleri de yazılır, bu yüzden dosya boyutunu ve saklama süresini baştan planlayın. ON_FAILURE = CONTINUE denetim yazılamazsa işin sürmesine izin verir; FAIL_OPERATION işi durdurur, SHUTDOWN sunucuyu kapatır. Hangisinin seçileceği bir kurum kararıdır. Denetim dosyalarının bulunduğu klasöre veritabanı yöneticisinin yazma ve silme yetkisi olmamalıdır; yoksa iz, izlenen kişinin elindedir. Dosyalar düzenli olarak SIEM'e ya da ayrı bir depoya taşınmalıdır.

Üç Yolun Karşılaştırması

"Kim" Sorusunun İkinci Yarısı

Üç yolun üçü de aynı şeyi söyler: hangi oturum açma adı, hangi makineden, ne zaman. Bu, sorunun yarısıdır. Oturum açma adı paylaşılan bir uygulama hesabıysa kişi hâlâ bilinmez. Kişi bilinse bile iki soru açık kalır: bu iş bir talebe dayanıyor muydu, birisi onaylamış mıydı? Hiçbir veritabanı izi "neden" sorusunu cevaplamaz, çünkü neden veritabanının dışında, bir talepte ya da bir konuşmada durur.

SQL Change Guard bu ikinci yarıyı yalnız kendi üzerinden giden iş için cevaplar. Ürünün üzerinden çalıştırılan bir değişiklikte talep kaydı, talebi kimin açtığını, gerekçeyi, kimin onayladığını, onaylanan betik metnini, hedefte hangi SQL hesabıyla çalıştığını ve hedef sunucunun bildirdiği oturum numarasını taşır. Bu oturum numarası ve zaman, yukarıdaki denetim kayıtlarıyla eşleştirmeyi mümkün kılar. Ürün dışından, örneğin doğrudan SQL Server Management Studio ile yapılan bir silmeyi ise ürün görmez ve engellemez; o iş için yukarıdaki yollar geçerlidir.

Sık Sorulan Sorular

Varsayılan izleme kapalıysa ne yapabilirim?

Geçmiş için işlem günlüğüne bakabilirsiniz; kayıtlar hâlâ duruyorsa oturum açma adı oradadır. İleri dönük olarak izlemeyi sp_configure 'default trace enabled', 1 ile açabilirsiniz, ama asıl çözüm SQL Server Audit kurmaktır.

Azure SQL Database'de varsayılan izleme var mı?

Hayır. Azure SQL Database'de SQL Trace desteklenmez. Orada aynı soru Azure SQL Auditing ile cevaplanır ve onun da olaydan önce açılmış olması gerekir.

DDL tetikleyicisi yazmak yetmez mi?

Bir DDL tetikleyicisi DROP_TABLE olayını bir tabloya yazabilir ve hızlı bir çözümdür. Ama tetikleyiciyi yetkili bir kullanıcı devre dışı bırakabilir ve yazdığı tablo silinebilir. SQL Server Audit ise dosyayı veritabanının dışına yazar; bu yüzden kanıt olarak daha güçlüdür.

Kimin sildiğini bulmak tabloyu geri getirir mi?

Hayır. Kim sorusu ile geri getirme ayrı işlerdir. Tablo, tam kurtarma modelinde olaydan hemen öncesine bir zaman noktası geri yüklemesiyle ayrı bir veritabanına alınıp oradan kopyalanır. Yedeğin hangi soruyu cevapladığı ve hangisini cevaplamadığı için ilgili yazıya bakın.