Sql & Veritabanı İle Uygulamalar İçin Veri Kayıt Denetim İzi Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Bu uygulamayı gerçekleştirmek için SQL uyumlu bir veritabanı yönetim sistemine (PostgreSQL, MySQL veya SQL Server) ihtiyacınız vardır. Örneklerimizde PostgreSQL sözdizimi ağırlıklı ilerleyeceğiz çünkü JSONB desteği, denetim izi kayıtları için oldukça esnektir.

• Veritabanı erişim yetkisi (DDL işlemleri yapabilmek için).
• Bir denetim tablosu (Audit Log Table) oluşturma yetkisi.
• Uygulama katmanında oturum (session) bilgilerini veritabanına taşıyabilecek bir yapı.

Adım 1: Denetim İzi Tablosunu Tasarlama

Denetim izi tutmak için en temel adım, değişiklikleri saklayacak merkezi bir tablo oluşturmaktır. Bu tablo, hangi tablonun, hangi satırının, ne zaman ve hangi işlemle (INSERT, UPDATE, DELETE) değiştiğini tutmalıdır.

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action_type VARCHAR(10) NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Bu tablo yapısında JSONB kullanımı, değişen verinin eski ve yeni halini esnek bir formatta saklamamıza olanak tanır. changed_by alanı, işlemi yapan kullanıcıyı belirtmek için kritik öneme sahiptir.

Adım 2: Veritabanı Tetikleyicileri (Triggers) ile Otomasyon

Denetim izini manuel olarak her uygulama fonksiyonunda yazmak hata payını artırır. Bunun yerine, veritabanı seviyesinde tetikleyiciler kullanmak, veritabanına gelen her türlü doğrudan müdahaleyi de kayıt altına almamızı sağlar.

CREATE OR REPLACE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_logs(table_name, record_id, action_type, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_logs(table_name, record_id, action_type, old_data, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_logs(table_name, record_id, action_type, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Bu fonksiyon, veritabanındaki her INSERT, UPDATE veya DELETE işleminde tetiklenir ve ilgili veriyi audit_logs tablosuna kopyalar. Böylece uygulama katmanında bir hata olsa bile veritabanı seviyesinde izleme garantilenmiş olur.

Adım 3: Tetikleyiciyi Tablolara Uygulama

Hazırladığımız fonksiyonu, izlemek istediğiniz her tabloya bir "Trigger" olarak eklemeniz gerekir. Bu sayede sisteminizdeki kritik tabloları (örneğin: users, orders) izlemeye başlayabilirsiniz.

CREATE TRIGGER audit_users_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_changes();

Bu komut, users tablosundaki her değişikliği otomatik olarak audit_logs tablosuna aktaracaktır. Test etmek için users tablosunda bir güncelleme yapın ve ardından audit_logs tablosunu sorgulayın.

Adım 4: Uygulama Katmanında Kullanıcı Bilgisini Aktarma

Veritabanı tetikleyicileri genellikle veritabanı kullanıcısını bilir, ancak uygulamanızdaki "giriş yapmış kullanıcıyı" bilmez. Bunu çözmek için oturum değişkenlerini kullanabilirsiniz.

-- Uygulama kodunuzda (örneğin bir PHP veya Python bağlantısında)
SET LOCAL app.current_user_id = 'user_123';

-- Fonksiyonunuzu güncelleyin
-- INSERT INTO audit_logs(..., changed_by) VALUES (..., current_setting('app.current_user_id'));

Bu yöntem, web uygulamanızdaki oturum bilgilerini veritabanı katmanına güvenli bir şekilde aktarmanızı sağlar. SET LOCAL kullanımı, sadece o anki işlem için değişkeni ayarlar ve işlem bitince temizler.

Adım 5: Denetim İzi Verilerini Sorgulama ve Analiz

Veriler biriktiğinde, bu verileri anlamlı raporlara dönüştürmeniz gerekir. Örneğin, belirli bir kullanıcının geçmişte yaptığı tüm değişiklikleri şu şekilde sorgulayabilirsiniz:

SELECT changed_at, action_type, table_name, old_data, new_data
FROM audit_logs
WHERE changed_by = 'user_123'
ORDER BY changed_at DESC;

Bu sorgu, sistem yöneticilerine şüpheli işlemleri tespit etme veya veri hatalarını geri alma (rollback) süreçlerinde büyük kolaylık sağlar.

Karşılaştırma: Denetim İzi Yöntemleri

Kritik Güvenlik Uyarısı: Denetim izi tabloları, veritabanınızın en hassas verilerini barındırır. Bu tablolara erişimi yalnızca veritabanı yöneticileriyle sınırlandırın ve log tablolarını düzenli olarak arşivleyip ana veritabanından ayırın. Aksi takdirde, log tablonuzun büyümesi veritabanı performansınızı ciddi şekilde düşürebilir.

Sıkça Sorulan Sorular

Denetim izi tablosu çok büyürse ne yapmalıyım?

Log tabloları zamanla büyüyecektir. "Partitioning" (Bölümleme) yöntemini kullanarak verileri ay bazında ayrı tablolara ayırabilir veya eski logları soğuk depolama alanlarına (S3 gibi) taşıyabilirsiniz.

JSONB kullanmak performansı etkiler mi?

Küçük ölçekli uygulamalarda etkilemez. Ancak çok yüksek trafikli sistemlerde, JSONB sütunları üzerinde indeksleme yaparken dikkatli olunmalı ve sadece gerekli alanlar indekslenmelidir.

Tetikleyiciler (Triggers) hata ayıklamayı zorlaştırır mı?

Evet, tetikleyiciler arka planda çalıştığı için hata anında iz sürmek zor olabilir. Her zaman tetikleyicilerinizin test ortamında kapsamlı bir şekilde test edildiğinden emin olun.

Uygulama katmanında loglama neden tek başına yetersizdir?

Uygulama katmanı, veritabanına doğrudan yapılan müdahaleleri (örneğin veritabanı yönetim aracı ile girilen SQL komutlarını) göremez. Bu yüzden veritabanı seviyesinde bir denetim şarttır.

Hangi verileri loglamalıyım?

Kritik tablolardaki tüm INSERT, UPDATE ve DELETE işlemlerini loglamalısınız. Sadece okuma (SELECT) işlemlerini loglamak veritabanını gereksiz şişirebilir.

Denetim İzi Verileri İçin Bölümleme (Partitioning) Stratejisi

Denetim izi tabloları, uygulama ömrü boyunca loglanan her işlemle birlikte hızla büyür. Bu durum, sorgu performansının düşmesine ve veritabanı yönetiminin zorlaşmasına neden olur. Büyük ölçekli sistemlerde Table Partitioning (Tablo Bölümleme) yöntemi, verileri zaman bazlı parçalara ayırarak performans kaybını engellemek için en etkili çözümdür.

PostgreSQL ile Zaman Bazlı Bölümleme

Aşağıdaki örnekte, denetim izi tablosunu aylık bölümlere ayırarak yönetmeyi görebilirsiniz. Bu yapı, eski verilerin arşivlenmesini veya silinmesini (data retention) çok daha kolay hale getirir.

-- Ana tabloyu oluşturma
CREATE TABLE audit_log (
    id SERIAL,
    table_name TEXT NOT NULL,
    operation_type TEXT NOT NULL,
    changed_at TIMESTAMP NOT NULL,
    user_id INT,
    old_data JSONB,
    new_data JSONB
) PARTITION BY RANGE (changed_at);

-- Ocak ayı için bölüm oluşturma
CREATE TABLE audit_log_2023_01 PARTITION OF audit_log
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Şubat ayı için bölüm oluşturma
CREATE TABLE audit_log_2023_02 PARTITION OF audit_log
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

Denetim İzi Verilerini Güvenli Bir Şekilde Arşivleme

Denetim izi verileri, yasal uyumluluk gereği (KVKK, GDPR gibi) belirli bir süre saklanmalıdır. Ancak bu verilerin veritabanı yedeğinde sürekli tutulması, yedekleme boyutlarını ve geri yükleme sürelerini olumsuz etkiler. Bu nedenle, belirli bir süreyi (örneğin 6 ay) aşan verilerin "Cold Storage" (soğuk depolama) alanlarına taşınması önerilir.

Otomatik Arşivleme İş Akışı

• Veri Ayıklama: Belirlenen süreyi aşan veriler SELECT sorgusu ile dışa aktarılır.
• Formatlama: Veriler genellikle CSV veya JSON formatında dışa aktarılır.
• Güvenli Depolama: Dışa aktarılan dosyalar şifrelenerek S3 bucket veya güvenli bir dosya sunucusuna taşınır.
• Temizleme: Veritabanındaki ilgili kayıtlar DELETE veya DROP PARTITION ile silinir.

Bu süreç, veritabanı sunucunuzun üzerindeki yükü hafifletirken, denetim ihtiyaç duyulduğunda verilerin geriye dönük olarak incelenebilmesini sağlar.

Profesyonel İpucu: Arşivleme işlemi sırasında verilerin bütünlüğünü korumak için, dışa aktarılan dosyaların SHA-256 hash değerlerini alarak bir "Bütünlük Kaydı" (Integrity Log) tutmanız, denetim süreçlerinde verinin manipüle edilmediğini kanıtlamanıza yardımcı olur.

Performans İzleme ve İyileştirme

Tetikleyiciler (Triggers) her INSERT, UPDATE veya DELETE işleminde çalıştığı için, veritabanı üzerinde ek bir yük oluşturur. Eğer sisteminizde saniyede binlerce işlem gerçekleşiyorsa, aşağıdaki optimizasyonları göz önünde bulundurmalısınız:

1. Asenkron Loglama: Tetikleyici yerine, veritabanı LISTEN/NOTIFY mekanizmasını kullanarak logları arka planda çalışan bir mikroservis ile işleyebilirsiniz.
2. İndeksleme Stratejisi: Denetim tablosunda en sık sorgulanan sütunlara (örneğin table_name ve changed_at) mutlaka indeks ekleyin.
3. Sadece Kritik Sütunlar: Tablodaki her sütunu loglamak yerine, sadece iş mantığı için kritik olan sütunları izleyin.

Sonuç

Sql & veritabanı ile uygulamalar için veri kayıt denetim izi oluşturmak, sisteminizin izlenebilirliğini ve güvenliğini en üst seviyeye taşır. Yukarıdaki adımları takip ederek, veritabanı tetikleyicileri ile otomatize edilmiş, güvenli bir denetim mekanizması kurabilirsiniz. Bir sonraki adım olarak, denetim izi verilerini görselleştiren bir dashboard (gösterge paneli) oluşturmayı veya loglarınızı merkezi bir log yönetim sistemine (ELK Stack gibi) aktarmayı düşünebilirsiniz.

Sorumluluk Reddi: Bu rehberdeki teknik uygulamalar, veritabanı üzerinde doğrudan değişiklik yapmayı gerektirir. Üretim ortamında (production) uygulamadan önce mutlaka yedek almalı ve geliştirme/test ortamlarınızda kapsamlı performans testleri gerçekleştirmelisiniz.