Daha önceki yazılarımızda, SQL Server 2008 özellikleri ile ilgili özet bilgiler vermiştik (SQL Server 2008'e Geçmek için 10 Neden, SQL SERVER 2008 YENİLİKLER – Bölüm 1). SQL Server 2008 ile gelen güçlü özelliklerinden birisi de loglama altyapısıdır. SQL Server 2008’de temel 3 tane loglama özelliği bulunmaktadır;
- Auditing
- Change Data Capture (CDC)
- Data Tracking (DT)
Not: Bu alternatiflerden DT tabloda versiyon bazında yapılan değişiklikleri tutma, online/offline uygulamalarda değişen kayıtları senkronize etme amacıyla kullanılmaktadır.
Bu yazımızda, Auditing özelliğine değineceğiz. SQL Server 2008 ile gelen Auditing altyapısı sayesinde, sunucu ve veritabanı seviyesinde yapılan her türlü işlemin logunun tutulup raporlanabilmesi sağlanmıştır. Sadece kayıt seviyesinde değil metadata seviyesinde yapılan işlemler de loglanabilir. SQL Server 2008’de audit oluşturma adımlarının genel şeması aşağıdaki gibidir;
Şekil 1. SQL Server 2008 Auditing1. Server Audit Nesnesinin Oluşturulması
Management Studio | T-SQL |
 | CREATE SERVER AUDIT [PanoramaAudit] TO FILE ( FILEPATH = 'D:\SQLDB' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF )WITH (QUEUE_DELAY = 2000 ,ON_FAILURE = CONTINUE) |
Yukarıdaki tabloda PanoramaAudit isimli bir server audit nesnesi yaratılmıştır. Kullanılan parametreler aşağıda açıklanmıştır;
- FILEPATH: Dosya adı değil, klasör yolu belirtilir. dosya adını, SQL Server kendi atar.
- MAXSIZE: Denetim dosyasının ulaşabileceği en büyük dosya boyutu.
- MAX_ROLLOVER_FILES: Kaç tane dosyanın kaydedileceği bilgisidir. 0 değeri sınırsız anlamına gelir.
- RESERVE_DISK_SPACE, MAXSIZE'da belirttiğiniz kadar alanı baştan ayırmak için kullanılır; eğer değeri ON ise, o zaman bu alan baştan ayrılır, eğer OFF ise, alan ihtiyaca göre ayrılır.
- QUEUE_DELAY: Eğer bu ayarın değeri 0 ise, denetim sırasında toplanan veriler, kayıt yerine (Event Log' lara veya dosyaya) eşzamanlı olarak kaydedilir. Eğer bu değer 1000 ise (ki bu değerler milisaniye bazındadır ve asgari değer 1000' dir) o zaman biriktirilen denetim verileri, kayıt deposuna her bir saniyede bir yazılır.
- ON_FAILURE ise iki değer alabilir, SHUTDOWN veya CONTINUE. Eğer kayıt dosyasında yer kalmadıysa veya diskinizde yer kalmadıysa, özetle; eğer denetim verileriniz kayıt deposuna kaydedilemiyorsa ve bu ayarın değeri de SHUTDOWN ise, o zaman SQL Server Instance’ ınız böyle bu durumda kapanacaktır.
“Server Audit” nesnesi tanımlandığında aktif değildir. Nesneyi kullanılabilir hale getirmek için üzerinde farenin sağ tuşuna tıklamanız ve "Enable Audit" öğesini seçmeniz veya T-SQL ile ilgili audit için aşağıdaki komutun çalıştırılması gerekmektedir;
ALTER SERVER AUDIT [PanoramaAudit] WITH(STATE=ON)2. Server Audit Specification Oluşturulması
Server Audit nesnesi oluşturulduktan sonra veritabanı seviyesinde Server Audit Specification nesnesi oluşturulur.
Management Studio | T-SQL |
 | CREATE DATABASE AUDIT SPECIFICATION Panorama_DB_Audit FOR SERVER AUDIT[PanoramaAudit] ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.TBLERCIL BY PUBLIC), ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.TBLERCILCE BY PUBLIC) WITH (STATE = ON) |
- Audit : Hangi server audit nesnesinin kullanılacağı bilgisidir.
- Action Type : Hangi işlem için audit yapılacağı bilgisidir.
- Object Class: Seçebileceğiniz üç tane tip vardır. Bunlar; Database, Object ve Schema.
- Object Schema : Hangi schema için yapılacağı
- Object Name : Audit çalıştırılacak nesnenin adıdır.
- Principal: Hangi kullanıcılar için audit yapılacağı bilgisidir.
3. Audit Log İzleme
Audit logları, T-SQL kodu ile veya Management Studio üzerinden izlenebilir. Aşağıdaki tabloda, PanoramaAudit nesnesinin oluşturduğu log kayıtlarının sorgulanması için kullanılan T-SQL sorgusu gösterilmiştir.
USE master GO -- get the audit file DECLARE @filepattern VARCHAR(300) DECLARE @folder VARCHAR(255) DECLARE @auditguid VARCHAR(36) SELECT @auditguid = audit_guid,@folder = log_file_path FROM sys.server_file_audits WHERE name = 'PanoramaAudit' SELECT @filepattern = @folder + '*_' + @auditguid + '*' -- view the results SELECT a.name AS Action,c.class_type_desc AS ObjectType,f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type JOIN sys.dm_audit_actions a ON f.action_id = a.action_id AND c.securable_class_desc = a.class_desc WHERE f.action_id <> 'AUSC' ORDER BY event_time DESC,sequence_number |
| SELECT | TABLE | sa | dbo | TBLERCIL | UPDATE [tblercIL] set [TXTAD] = @1 WHERE [BYTKOD]=@2 |
| UPDATE | TABLE | sa | dbo | TBLERCIL | UPDATE [tblercIL] set [TXTAD] = @1 WHERE [BYTKOD]=@2 |
| SELECT | TABLE | sa | dbo | TBLERCIL | SELECT TOP (500) BYTKOD, TXTAD, TRHSONISLEMTARIHI, TXTSONISLEMHOST, TRHILKISLEMTARIHI, LNGILKKULLANICIKOD, LNGSONKULLANICIKOD FROM TBLERCIL WHERE (BYTKOD = @Param1) |
| SELECT | TABLE | sa | dbo | TBLERCIL | UPDATE TOP (500) TBLERCIL SET TXTAD = @TXTAD WHERE (BYTKOD = @Param1) AND (TXTAD = @Param2) AND (TRHSONISLEMTARIHI = @Param3) AND (TXTSONISLEMHOST = @Param4) AND (TRHILKISLEMTARIHI = @Param5) AND (LNGILKKULLANICIKOD = @Param6) AND (LNGSONKULLANICIKOD = @Param7) |
| UPDATE | TABLE | sa | dbo | TBLERCIL | UPDATE TOP (500) TBLERCIL SET TXTAD = @TXTAD WHERE (BYTKOD = @Param1) AND (TXTAD = @Param2) AND (TRHSONISLEMTARIHI = @Param3) AND (TXTSONISLEMHOST = @Param4) AND (TRHILKISLEMTARIHI = @Param5) AND (LNGILKKULLANICIKOD = @Param6) AND (LNGSONKULLANICIKOD = @Param7) |
| SELECT | TABLE | sa | dbo | TBLERCIL | SELECT TOP (500) BYTKOD, TXTAD, TRHSONISLEMTARIHI, TXTSONISLEMHOST, TRHILKISLEMTARIHI, LNGILKKULLANICIKOD, LNGSONKULLANICIKOD FROM TBLERCIL |
Aynı şekilde Management Studio kullanılarak da loglar izlenebilir;
Şekil 2. Management Studio aracılığı ile logların izlenmesi
Seçkin KARABACAKOĞLU, Serap PARLAK, Deniz KILINÇ
Yorumlar
Yorum Gönder