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. Şimdi bu yapıların ne olduğunu ve nasıl konfigürasyon yapıldığına değinmiş olalım.
Database Level Audit: 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 Veritabanı Audit veya Server Audit altında filtreleme işlemi yapılır.
1-Audit Name: Bu, oluşturulan SQL Server Audit için belirlenen isimdir.
2-Queue delay (ms) (Kuyruk Gecikmesi): 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 (Denetim Günlüğü Hatasında): 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 (Denetim Hedefi): 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ı Maksimum Limiti): Audit dosyasının boyut limitini veya kaç tane dosya oluşturulabileceğini belirler. Burada iki seçenek var:
Unlimited (Sınırsız): Limit yoktur.
Maximum files (Maksimum dosya sayısı): 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 (Maksimum Dosya Boyutu): 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 (Sınırsız)” 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 (Disk Alanı Ayır): 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 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.
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 Databases üzerinde işlem yapmak için Databases seçiyoruz.
Databases yapısı seçildikten sonra Browse.. sekmesinde de istediğimiz veritabanı seçilir.
Principal Name: Bu kısımda hangi kullanıcı üzerinde denetleme yapmak için ilgili kullanıcı seçilir. sysadmin üzerinde işlem yapmak istediğim için dbo kullanıcısını seçiyorum.
Sonuç olarak sysadmin kullanıcının AdventureWorks2012 verirabanı ü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 sysadmin(dbo) kullanıcısı ile AdventureWorks2012 veritabanına select çekelim 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.
Database üzerinde Audit yapımızı oluşturduktan sonra şimdi ise Server Level Audit kavramını örnek üzerinden yapmaya çalışalım.
Server Level Audit
İnstance altındaki Security tabından Audits kısmında New Audit.. sekmesine tıklanır. Gelen ekranda ne gibi konfigürasyonlar yapmamız gerektiğini yukarıda database oluşturduğumuz Audit ile aynı yapıda oluşturuyorum. Bu oluşturulmuş Audit sadece bu Server Level Audit yapısına özel bir yapı.
Audit oluşturduktan sonra AuditServer yapımız Enable yapılır.
Genel Audits yapımızı Enable ettikten sonra Server Audit Specifications bölümüne sağ tıklanır. New Server Audit Specification.. kısmına tıklanır.
Gelen ekranda Server bazında ne gibi denetleme yapmak istiyorsak ilgili eylem seçilmesi gerekmektedir.
Name kısmında Audit yapımıza bir örnek veriyoruz.
Audit kısmında ise oluşturmuş olduğumuz server Audit yapısını seçiyoruz. Database için oluşturduğumuz audit yapımızda seçilebilir. Bu yapı karışıklığa sebebiyet verecektir.
Audit Action Type kısmında server seviyesinde bir çok olayı takip ederiz. Microsoft sayfasında server serviyesinde audit’lerin ne işe yaradığını görebiliriz.
Bazı Audit Action Type kısmında bulunan kavramları açıklayalım.
- 1-AUDIT_CHANGE_GROUP: Bu, audit yapılandırmalarında yapılan değişiklikleri izler.(+)
- 2-BACKUP_RESTORE_GROUP: Bu grup, veritabanı yedekleme ve geri yükleme işlemlerini izler.(+)
- 3-BROKER_LOGIN_GROUP: Bu grup, Service Broker ile ilgili oturum açma işlemlerini izler. Service Broker, SQL Server’ın mesajlaşma ve iş akışı uygulamaları için kullanılan bir özelliğidir.
- 5-DATABASE_CHANGE_GROUP: Veritabanı üzerindeki yapısal değişiklikleri izler. Örneğin, bir veritabanının oluşturulması, değiştirilmesi veya silinmesi gibi işlemleri denetler. Ayrıca tablo oluşturma değiştirme, filegroup ve data file ekleme veritabanı üzerinde yapılan ayar değişikliklerinin hepsini izler.(+)
- 6-DATABASE_LOGOUT_GROUP: Veritabanından çıkış işlemlerini izler.
- 7-DATABASE_LOGOUT_GROUP: Veritabanına giriş işlemlerini izler.
- 8-DATABASE_OBJECT_ACCESS_GROUP: Veritabanındaki nesnelere (tablo, görünüm, prosedür vb.) erişimleri izler. Bu grup, belirli bir nesneye erişimin kim tarafından ve nasıl yapıldığını denetlemenizi sağlar.
- 9-DATABASE_OBJECT_CHANGE_GROUP: Veritabanındaki nesneler üzerinde yapılan değişiklikleri izler. Örneğin, bir tabloya yeni bir sütun eklenmesi ya da tablo yapısında değişiklik yapılması gibi işlemler.(+)
- 10-DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP: Veritabanındaki nesnelerin sahipliğinde yapılan değişiklikleri izler. Bir nesnenin sahipliği başka bir kullanıcıya devredildiğinde bu grup bunu denetler.
- 11-DATABASE_OBJECT_PERMISSION_CHANGE_GROUP: Veritabanı nesneleri üzerindeki izin değişikliklerini izler. Kimlerin hangi nesnelere ne tür izinlere sahip olduğunu belirlemek için kullanılır.(+)
- 12-FAILED_DATABASE_AUTHENTICATION_GROUP: Başarısız veritabanı kimlik doğrulama girişimlerini izler. Hatalı kullanıcı adı veya şifre ile yapılan giriş denemeleri bu grup tarafından denetlenir.(+)
- 13-LOGIN_CHANGE_PASSWORD_GROUP: Kullanıcı şifre değişikliklerini izler. Bu grup, kullanıcıların SQL Server üzerindeki şifrelerini değiştirme işlemlerini denetler.(+)
- 14-SCHEMA_OBJECT_ACCESS_GROUP: Şemadaki nesnelere yapılan erişimleri izler. Şema, veritabanında nesnelerin mantıksal gruplara ayrılmasını sağlayan bir yapıdır.
- 15-SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP: Şema nesneleri üzerindeki izin değişikliklerini izler. Şema altındaki tablolara, görünümlere ve diğer nesnelere verilen veya alınan izinleri denetler.
- 16-SENSITIVITY CLASSIFICATION CHANGE GROUP: Bu grup, veritabanındaki veri sınıflandırmalarında (örn. gizlilik düzeyleri) yapılan değişiklikleri izler. Hassas veri sınıflandırmalarını yönetmek ve izlemek için kullanılır.
- 17-SERVER_OBJECT_CHANGE_GROUP: Sunucu seviyesindeki nesneler (örn. tablolar, prosedürler) üzerinde yapılan değişiklikleri izler. Sunucudaki objelerin yapısal modifikasyonlarını denetler.
- 18-SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP: Sunucudaki nesnelerin sahipliğinde yapılan değişiklikleri izler. Bir sunucu nesnesinin sahipliği başka bir kullanıcıya devredildiğinde bunu izler.
- 19-SERVER_OPERATION_GROUP: Sunucu seviyesindeki genel işlemleri izler. Bu, SQL Server üzerinde yapılan çeşitli işlemleri kapsayabilir.(+)
- 20-SERVER_PERMISSION_CHANGE_GROUP: Sunucu seviyesindeki izinlerde yapılan değişiklikleri izler. Sunucudaki kullanıcı veya rollerin izinlerinin değiştiği durumlar izlenir.
- 21-SERVER_PRINCIPAL_CHANGE_GROUP: Sunucudaki “principal” (ana kullanıcı veya oturum açma) hesaplarındaki değişiklikleri izler. Bu hesaplar sunucu düzeyinde oturum açma ve güvenlik unsurlarını ifade eder.
- 22-SERVER_PRINCIPAL_IMPERSONATION_GROUP: Sunucu seviyesindeki kullanıcı kılığına girme (impersonation) işlemlerini izler. Bir kullanıcının başka bir kullanıcı hesabının kimliğiyle işlem yapma durumu denetlenir.
- 23-SERVER_ROLE_MEMBER_CHANGE_GROUP: Sunucu rollerindeki üye değişikliklerini izler. Bir sunucu rolüne yeni bir kullanıcı eklenmesi ya da bir kullanıcının rolden çıkarılması durumunda bu grup devreye girer.
- 24-SERVER_STATE_CHANGE_GROUP: Sunucu durumundaki değişiklikleri izler. Bu, sunucu başlatma, durdurma ya da yeniden başlatma gibi işlemleri kapsar.(+)
- 25-STATEMENT_ROLLBACK_GROUP: İptal edilen SQL ifadelerini (statement rollback) izler. Bir SQL komutunun geri alındığı (rollback) durumlarda bu grup kullanılır. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP: Veritabanına başarılı kimlik doğrulama (authentication) işlemlerini izler. Kullanıcıların başarılı bir şekilde giriş yaptığı oturumlar denetlenir.
- 26-SUCCESSFUL_LOGIN_GROUP: Başarılı oturum açma girişimlerini izler. Bir kullanıcının SQL Server’a başarıyla giriş yaptığı her durumu kaydeder. (+)
- 27-TRACE_CHANGE_GROUP: SQL Server izleme (trace) yapılandırmalarındaki değişiklikleri izler. İzleme, SQL Server üzerindeki olayları takip etmek için kullanılır.
- TRANSACTION_BEGIN_GROUP: Bir işlemin (transaction) başladığını izler. İşlem başladığında denetim kaydı oluşturur.
- 28-TRANSACTION_COMMIT_GROUP: Bir işlemin başarıyla tamamlanıp onaylandığı (commit) durumları izler. Veri tabanındaki değişikliklerin başarılı bir şekilde yazıldığını denetler.
- 29-TRANSACTION_GROUP: Genel işlem aktivitelerini izler. Veritabanında yapılan işlemleri denetlemek için kullanılır.
- 30-TRANSACTION_ROLLBACK_GROUP: Geri alınan (rollback) işlemleri izler. Başlatılan bir işlemin iptal edilip geri alınması durumlarını denetler.
- 31-USER_CHANGE_PASSWORD_GROUP: Kullanıcıların SQL Server üzerindeki şifre değişikliklerini izler. Şifre değiştirme işlemleri kaydedilir.
- 32-USER_DEFINED_AUDIT_GROUP: Kullanıcı tarafından tanımlanmış denetimleri izler. Kullanıcıların belirlediği özel denetim yapılandırmalarını takip eder. Bu eylem grupları, SQL Server’daki çeşitli olayları ve değişiklikleri izleyerek güvenliği artırır ve sorunların izlenmesini sağlar. Sunucu, veritabanı, kullanıcı izinleri ve işlemler gibi kritik alanlarda detaylı denetim sunar.(+)
Aşağıda göründüğü gibi birden fazla server seviyesinde Audit oluşturarak kontrol işlemi yapabiliriz.
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.
Gerçek sistem üzerinde server seviyesinde Audit yapımız.
Instance üzerinde database bazlı kullanıcıların permission bilgileribi kontrol etmek için aşağıdaki audit bölümüne eklenmesi gerekmektedir. DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SERVER_PERMISSION_CHANGE_GROUP audit türlerinin eklenmesi gerekmektedir.
İnstance altında bulunan kullanıcılar üzerinde ne yetkiler verilmiş ne yetkiler alınmış detaylı bir şekilde görebilirsiniz.
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.
Bu makalede Sql Server üzerinde Audit işlemini görmüş olduk. Başka bir makalede görüşmek dileğiyle.
Onlar – ” Darlıkta Ve Bollukta Da İnfak Ederler. ” Ali Imran / 134
