Masters Of SQL

SQL Server ile ilgili bilgiler, hayata dair paylaşımlar ve birazda eğlence tabii...
Deadlock’sız, Blocking’siz, Contention’sız günler diliyoruz :)
Sysadmin sizinle olsun

Nedir Bu SQL Server AUDIT? (Bölüm 2)

Merhabalar :)

Audit Yazı dizimizin 2.bölümü ile karşınızdayız. Bir önceki bölümde "Audit nedir?", "Nasıl kurulur?" sorularının cevabını vermeye çalıştık.

Yazımızın 1.bölümüne gitmek için : Nedir Bu SQL Server AUDIT? (Bölüm 1)

Bu bölümüzde, Auditi’imizin analizini yapmaya çalışacağız. İlk olarak bakalım bir önceki bölümümüzde kurduğumuz Audit’imizi kurabilmiş miyiz? Bunun için; fn_get_audit_file fonksiyonu, Audit’imizin için en sık kullanacağımız fonksiyon olabilir. Fonksiyona, Audit’imizin Path’ini vererek aşağıdaki gibi çalıştırıyoruz.


    
select * from fn_get_audit_file ('D:\Audit\*.sqlaudit',default,default)

Bu fonksiyon sayesinde Audit’imize düşen logları görebiliriz. Sorguyu kendimize göre düzenleyerek, daha okunabilir bir hale getirebiliriz.
    
select event_time, object_name, statement, database_name, class_type, session_server_principal_name, server_principal_name, action_id, succeeded, session_id 
from fn_get_audit_file ('D:\Audit_Deneme\*.sqlaudit',default,default) 
order by 1 desc

Tablodaki kolonları inceleyecek olursak, yapılan işin ne zaman yapıldığını, hangi statement'ın çalıştığını, kim tarafından yapıldığını, yaptığı işin başarılı olup olmadığı gibi soruların cevabını bulabiliriz.

DMV lerle inceleyecek olursak;
    
select * from sys.dm_audit_class_type_map

fn_get_audit_file fonksiyonundan dönen class type seçeneklerini inceleyebilmemize olanak sağlayan bir DMV.

    

select * from sys.server_audits
select * from sys.server_audit_specifications
select * from sys.server_audit_specification_details

DMV leri sayesinde, auditlerimizin detayını görüntüleyebiliyoruz. Şimdi, Auditimizi test etme zamanı:) Bakalım, gerçekten de loglayacak mı?
Çalışma amaçlı bir DB oluşturuyorum.
    
CREATE DATABASE BAKALIM_OLCAKMI

Audit'imize bakalım. İstediğimiz şekilde loglamış mı?
    
select event_time, object_name, statement, database_name, class_type, session_server_principal_name, server_principal_name, action_id, succeeded, session_id 
from fn_get_audit_file ('D:\Audit_Deneme\*.sqlaudit',default,default) 
order by 1 desc

Bu seferki denememizde tablo create, alter, drop işlemlerini deniyoruz.
    
--Tablo create 
USE [BAKALIM_OLCAKMI]
GO

CREATE TABLE [dbo].[Audit_Deneme](
	[column_1] [int] NOT NULL,
	[column_2] [int] NULL
) ON [PRIMARY]

--Tablo alter(kolon ekleme)
ALTER TABLE dbo.Audit_Deneme
	ADD deneme int NULL

--Tablo drop 

DROP TABLE dbo.Audit_Deneme

Az evvelki scriptimizi yeniden çalıştırıyoruz. Bakalım loglamış mı?


Şimdi de yeni bir login oluşturup, daha sonrasında drop ederek loglayacak mı bakalım :)
    
--Login create
USE [master]
GO
CREATE LOGIN [AuditDeneme] WITH PASSWORD=N'12345', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Login drop
DROP LOGIN [AuditDeneme] 
GO

Aynı şekilde, sizler de SP, function, Index create ederek, alter ya da drop yaparak deneyebilirsiniz. Logladığını göreceksiniz :)

Geldik bir yazımızın daha sonuna. Umarım sizlere faydalı olabilmişizdir. Bir sonraki yazı dizimizde görüşmek üzere. Soğuklar da geliyor, kendinize iyi bakın, Yoda’nın tabiriyle “DBA hastalanmaz”. Geçen sene beni nasıl şartladıysa “DBA hastalanmaz” diye kendime telkin ede ede hastalanmadım da gerçekten :D 

Sağlıklı, bol motivasyonlu günleriniz olsun :)


Nedir Bu SQL Server AUDIT? (Bölüm 1)

Merhaba güzel insanlar, görüşmeyeli nasılsınız? Bugün bir Audit yazısıyla karşınızdayız. Aradığınızı bulmanızı, bulamadığınızı sormanızı temenni eder, iyi okumalar dilerim :)

Gelelim Audite :)

SQL Server dünyasına 2008’de giren Audits özelliği, security anlamında DBA’lere güzel avantajlar sağlayan bir özelliktir. Örneğin; server üzerinde kim database oluşturmuş, kim database silmiş, kim bir değişiklik yapmış, yeni oluşturulan bir login bilgisi, incelemek istediğimiz bir tabloya kim kayıt eklemiş, kim kayıt silmiş tarzında sorularımıza cevap bulabileceğimiz, veri tabanımızda belirlediğimiz çerçevede güvenliğimizi sağlayabilmemize olanak sağlayan, 2012 de filter seçeneği ile filtreleme de yapabildiğimiz ekstra yeni özelliklerle hayatımıza dahil olan, incelenmesi gereken bir özelliktir.

Ne dersiniz? Beraber yapalım mı basit bir Audit örneği :)

Security’nin altındaki Audits’e tıklarak “New Audit” diyoruz.


Karşımıza Create Audit ekranı çıkıyor.


Bu ekranda,
Audit name kutucuğunda, oluşturacağımız Audit’e isim vermemiz gerekiyor.
Queue delay kutucuğu default değeri 1000 ms olarak geliyor. Bunun anlamı, biriktirilen verilerin, kayıt yerindeki dosyamıza her bir saniyede bir yazılacağını ifade etmektedir. Bu değer’i 0 yaparsak, eş zamanlı yazılacağı anlamına gelir.
On Audit Log Failure seçeneğinde seçeneğinde Audit’imizin fail olması durumunda ne olacağını seçmemizi istiyor. “Shut Down Server” a dikkat etmenizi tavsiye ederim. Kaş yaparken göz çıkarmayalım :P
Audit Destination’da Audit’imizin nereye yazılacağını seçmemiz gerekiyor. 

Seçeneklerimiz File, Security Log ve Application Log. Ben, D’nin altında açtığım Audit klasörünün altına yazmasını istediğim için file’ı seçiyorum.


Audit File Maximum Limit kutucuğunda ulaşabileceği maximum file sayısını belirleme imkanı sunuyor bize sevgili SQL Server.
Filter seçeneği, aslında bizim yazdığımız query’lerdeki where kısmı. Buraya nasıl bir filtreleme yapmak istiyorsak, o şekilde yazabiliyoruz. Örneğin; ben içinde truncate geçen statementları loglamamasını istedim. 



İster OK’a basarak, Audit’imizi oluşturabilir, istersek Sol üst köşedeki Script’e tıklayarak, penceremizde açılan scripti çalıştırarak Audit’imizi oluşturabiliriz.

    
USE [master]
GO

CREATE SERVER AUDIT [Audit_DDL]
TO FILE 
(	FILEPATH = N'D:\Audit'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

WHERE statement not like '%truncate%'
   
Auditimizi oluşturduk. Fakat defaultta oluşturduğumuz Audit disable gelir. Bu sebeple Auditimizin üzerine sağ tıklayarak enable duruma getiriyoruz.


Auditimizi oluşturmuş olsak da halen hedefimize ulaşmış sayılmayız. Şimdiki adımımızda, Server bazlı obje değişikliklerini, yeni oluşturulan loginlerin loglamasını hedeflediğimiz bir server bazlı Audit oluşturarak, bu Auditimizi kullanmasını sağlamalıyız. Bunun için, Server Audit Specifications’a sağ tıklayarak New Server Audit Specification… a tıklıyoruz.

Karşımıza çıkan pencerede, neleri loglamasını istiyorsak seçiyoruz. Ben yeni oluşturulan veya silinen bir database i loglaması için Database Change Group, server düzeyinde obje değişikliklerini yakalaması için Schema Object Change Group u ve Login create- drop larını yakalayabilmek için Server Principal Change Group u seçiyorum. Siz isterseniz, kendi ihtiyaçlarınıza göre farklı kombinasyonlar yapabilirsiniz.


Aynı şekilde, pencereden Ok a basarak veya sol üst köşeden aldığımız Script ile Server Bazlı Auditimizi oluştururuz.
USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Server_DDL]
FOR SERVER AUDIT [Audit_DDL]
ADD (DATABASE_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
   
Yine, enable etmek gerekiyor tabi :) Audit oluşturma işlememiz tamamlandı. Auditimizin kontrolleri bir sonraki bölümümüzde :) Bakalım Auditimiz olmuş mu? 

Auditimizi DMV ve function ile nasıl incelendiğimizi anlattığımız 2.bölüm için: Nedir Bu SQL Server AUDIT?(Bölüm 2)