Bu makalede Sql Server Audit oluşturma konusunu ele almış olacağım. Sql Server’da kullanıcıların yaptığı işlemleri kayıt altına almak için Audit oluşturulur. SQL Server Audit özelliğini kullanabilmemiz için SQL Server’ın Enterprise sürümünü kullanıyor olmamız gerekmektedir. İki çeşit Audit yapısı vardır. Bunlar Database Level Audit ve Server Level Audit olarak karşımıza çıkmaktadır. Bu makalede Database Level Audit yapısının ne olduğunu ve nasıl konfigürasyon yapıldığına değinmiş olalım.
Bu Audit yapısı veritabanı düzeyinde kullanıcıları denetleme işlemleri için kullanılır. Database Level Audit kısmı oluşturmadan önce Audit yapımızı oluşturalım.
Her Audit yapısı oluşturmak için başlangıç olarak aşağıdaki işlemler yapılır.
SSMS arayüzünde Security sekmesi altında bulunmaktadır.
Audits sekmesine sağ tıklayıp New Audits diyiyoruz. Neden bu işlemi yaparız. Çünkü bir denetim işlemi başlatmadan önce Audit kayıtlarının nerede nasıl ve hangi ayarlara göre saklanması gerektiğinin ayarlanması gerekmektedir.
Gelen ekrandaki kavramların ne olduğuna değinelim. Aşağıdaki ekran resminde veritabanında veya serverda yapılan işlemleri izlemek için tanımlama yaptığımız ekranı görmekteyiz. Burada genel bir Audit yapısı oluşturulduktan sonra Database Audit yapısının konfigüre edilmesi gerekmektedir.
1-Audit Name: Bu, oluşturulan SQL Server Audit için belirlenen isimdir.
2-Queue delay (ms): SQL Server Audit kayıtlarının ne kadar süreyle (milisaniye cinsinden) kuyrukta tutulacağını belirler. Kuyrukta bekleyen veriler her 1 saniyede bir yazılmaktadır. Eğer veriler kuyrukta uzun süre bekletilirse, Audit yükünün azaltılması amaçlanabilir.
3-On Audit Log Failure: SQL Server’da bir Audit yazarken hata oluştuğunda yapılacak işlemi belirtir. Üç seçenek var:
- Continue: Log alınmadığı durumda Alert çalışmaya devam eder.
- Fail Operation: Log alınmadığı durumda SQL Server transaction’ı durdurur.
- Shut down server: Log alınmadığı durumda SQL Server kapanır. Buradaki amaç audit yapımız olmazsa işlem yapılamaz anlamındadır. Yapılan her işlemin bir denetimden geçmesi gerekmektedir. Çok güvenlikli ortamlarda kullanılan bir yapı olarak karşımıza çıkmaktadır.
4-Audit Destination: Audit sonuçlarının nereye yazılacağını belirler. File seçerek .sqlaudit uzantılı bir dosyada verilerimizi tutabiliriz. Audit sonuçlarını local’de bulunan bir dosya üzerine kaydetmek, denetime takılan tüm kullanıcıların sysadmin’de dahil Audit dosyamızı okumasına sebebiyet verecektir. Uzak sunucularda da sql server servis hesabının sadece insert ve modify yetkisinin olması gerekmektedir. Veritabanı yöneticisi herhangi bir müdahalede bulunamaması içindir. Yukarıdaki resimde Audit yapısı için başka bir sunucuda klasör oluşturup SqlUser servis hesabına okuma yetkisi sadece verilmelidir.
5-Path: Denetim dosyasının kaydedileceği dizini veya dosya yolunu belirler.
6-Audit File Maximum Limit: Audit dosyasının boyut limitini veya kaç tane dosya oluşturulabileceğini belirler. Burada iki seçenek var:
- Unlimited: Limit yoktur.
- Maximum files: Audit için belirli bir dosya sayısı limiti ayarlanabilir. Görselde 2.147.483.647 değer varsayılan maksimum değerdir.
7. Maximum file size: Bu bölümde oluşturacağımız Audit dosyalarının boyutunun sınırlanıp sınırlanmayacağını belirleriz. Görselde “Unlimited” seçeneği işaretlenmiş. Maximum files kısmını seçerek dosya bazında sınırlama yapabiliriz. Bir audit oluşturduktan sonra gözlemle bu değerin verilmesi gerekmektedir.
8-Reserve Disk Space: Audit dosyası için belirli bir disk alanının önceden ayrılmasını sağlar.
Not: Audit yapılandırmasında “Maximum Rollover Files” ayarı belirlenmişse, eski dosyalar silinerek yeni dosyalar oluşturulur. Yani belirlemiş olduğumuz limite ulaşması durumunda herhangi bir durmaya ve sıkıntıya sebebiyet vermez. Bu bölümün seçilmemesi sonucunda ilerleyen zamanlarda alınacak hatayı ilgili makaleden görebilirsiniz.
Bu ayarların doğru yapılandırılması, SQL Server’da yapılan işlemlerin düzgün bir şekilde izlenmesi ve denetlenmesi açısından önemlidir.
Filter kısmı oluşturduğumuz Audit yapısının alt filtrelemesidir. Sorgularımızda ki where ifadesi diyebiliriz.
Audit oluşturduktan sonra aktif edilmesi gerekmektedir
Yukarıda genel Audit konfigürasyonu yaptıktan sonra şimdi ise veritabanı bazında nelerin audit’lerini tutmak istiyorsak ilgili veritabanında Audit filtrelemesi yapılmaktadır.
İlk olarak Audit oluşturacağımız veritabanının Security kısmında Database Audit Specifications kısmı seçilir.
Database Audit Specifications kısmına sağ tıklanıp New Database Audit Specification.. seçilir.
Gelen ekranda ne gibi konfigürasyonlar yapılır değinelim.
- Name: Bu kısımda Audits’e bir isim veriyoruz.
- Audit: Hangi Audits üzerinde çalışmasını istiyorsak ilgili Audit seçilir.
- Audit Action Type: Bu kısımda birden fazla seçenek karşımıza çıkmakta burada hangi eylemi takip etmek istiyorsak o seçilir. Ben Select ifadesini seçiyorum.
Belirtilen tablolar üzerinde update işlemi yapılıp yapılmadığını takip etmek için Update ifadesi seçilmektedir.
Yukarıdaki işlemde hangi tablolarımızın üzerinde update işlemi yapıldığını görmek için aşağıdaki veritabanımıza sağ tıklayıp report kısmından görülebilir.
Principal Name kısmında herhangi bir kullanıcı seçilir ilgili kullanıcının tablonun üzerinde yaptığı işlemler loglanmaktadır. Burada veritabanı altında bulunan kullanıcılar filtrelenebilir.
Yukarıdaki diğer bölümleri açıklayacak olursak:
Object Class: Bu bölümde Database , Object ve Scheme yapısından birini seçiyoruz.
Object Name: Bu kısımda 3 noktaya tıklanır. Object Types.. kısmında ne üzerinde işlem yapmak istiyorsak o yapı seçilir. Biz sadece Tablo üzerinde işlem yapmak için tabloların seçilmesi gerekmektedir..
Principal Name: Bu kısımda hangi kullanıcı üzerinde denetleme yapmak için ilgili kullanıcı seçilir.
Sonuç olarak belirtilen kullanıcının veritabanı üzerinde yapacağı select sorgularına kayıt altına almış olacağız.
İlgili Audit yapısını oluşturup ve oluşturduktan sonra aktif etmek için Database altındaki Security kısmında Database Audit yapısını resimde görüldüğü gibi Enable yapısını çekmemiz gerekiyor.
Not: AlwaysOn yapılarında Database altında oluşturulan Audit secondary sunucusundaki database altında da otomatik bir şekilde oluşturulur. Server Audit yapılarında her sunucu için manuel oluşturulması gerekmektedir.
Şimdi uygulamalı bir şekilde belirtilen kullanıcı ile veritabanında update işlemi yapalım. Bakalım Audit oluşacak mı?
Paylaşım klasöründe Audit yapımızın oluştuğunu görmüş oluyoruz. İlerleyen bölümlerde Audit dosyalarımızı nasıl okuduğumuza değinmiş olacağız.
Yukarıdaki Audit yapımızı oluşturduktan sonra Audit oluşturduğumuz klasör altında yapının geldiğini görmüş oluyoruz.
Yukarıdaki Audit oluşumlarından sonra bu Audit’leri okumak için aşağıdaki komut kullanılmaktadır. İlk olarak database işlemi için oluşan Audit yapımızı okuyalım.
SELECT * FROM sys.fn_get_audit_file (
'\\S3\audit\\AUDIT23_E68E7C47-9C2E-4504-B33E-ACD0A8ECF963_0_133734125292290000.sqlaudit' , default, default)
--where statement like '%yunustest%'
Aşağıdaki select sorgumuzda ise server için oluşturduğumuz Audit yapımızı okuduğumuzda bize ne gibi işlemlerin yapıldığını söylemiş oluyor.
SELECT * FROM sys.fn_get_audit_file (
'\\S3\auditserver\\AuditServer_63523A32-6B54-436B-BA67-A49251601E7C_0_133734143403200000.sqlaudit' , default, default)
-- where statement like '%yunustest%'
Okuma işlemi ilgili Audit’e sağ tıklanıp View Audit Logs’da yapılabilir.
Not: Audit nesneleriyle ilgili bir değişiklik yapmak istersek ilgili Audit’i disable durumuna getirmeniz gerekmektedir. Enable Audit’lerde değişiklik yapılamaz.
SELECT
database_name as VeriTabani,
action_id AS IslemTuru,
statement AS YapilanIslem,
event_time as IslemZamani,
server_principal_name AS IslemYapanKullanici,
target_server_principal_name AS IslemYapılanKullaniciAdi
FROM sys.fn_get_audit_file('M:\YEDEK\AUDIT\YETKI_AUDIT\YETKI_TAKIBI_AUDIT_8C390C6C-8BFD-4B06-86D3-DE0BBBA51D9E_0_133906623462890000.sqlaudit', NULL, NULL)
WHERE action_id !='AUSC'
ORDER BY event_time DESC;SAKINCALARI & RİSKLERİ
1. Performans Sorunu
- Çok fazla olay loglanırsa, sistem performansını düşürebilir.
- Özellikle “SELECT” gibi sık yapılan işlemleri loglamak risklidir.
2. Disk Alanı Tükenmesi
- Log dosyaları büyür, arşivlenmezse disk dolabilir.
- Özellikle hedef FILE tipi ise yönetilmesi gerekir.
3. Yönetimsel Karmaşıklık
- Kimin neyi logladığı, hangi olayın neden loglandığı zamanla karışabilir.
- Doğru yapılandırılmazsa, gürültü (noise) yaratır.
4. Gizlilik Riski
- Audit loglarında kullanıcı adları, IP’ler, hassas sorgular görülebilir.
- Audit loglarına erişen kişinin yetkisi sorgulanmalıdır.
5. Audit Kayıtlarını Silebilme
- Yeterli izinlere sahip biri, audit loglarını silebilir → Denetlenme zinciri kırılır.
- Bu yüzden audit loglarının ayrı, güvenli diskte tutulması önerilir.
Not: Sql server audit loglarında kimin nasıl bağlandığını görmek istiyorsak oluşturduğumuz audit uzantısı içerisinde LPC ve TCP/IP ifadelerini görmemiz sorgumuzun nasıl bağlandığını görebiliriz. LCP protokolü uygulama veya kullanıcı sql server ile aynı makinada çalıştığını göstermektedir. TCP/IP ise başka bir sunucuda yapılan bağlantıdır.
Bu makalede Sql Server üzerinde Database Audit işlemini görmüş olduk. Başka bir makalede görüşmek dileğiyle.
“De ki: Ey Rabbim! İlmimi artır.” Taha-114
