SQL Server Audit

Audit Nedir?

Bir SQL Server Database Engine instance ının veya tek bir veri tabanının denetlenmesi, Database Engine üzerinde meydana gelen olayların izlenmesini ve kaydedilmesini içerir.

Auditleri 2 tür olarak ayırırız:

1. Server Audit Specifications
2. Database Audit Specifications

SQL Server denetimi, sunucu düzeyinde meydana gelen olayların denetlenmesi için sunucu denetim özelliklerini(Server Audit Specifications) ve veri tabanı düzeyinde meydana gelen olayların denetlenmesi için veri tabanı denetim özelliklerini(Database Audit Specifications) içerebilecek sunucu denetimleri oluşturmanızı sağlar. Denetlenen olaylar, loglara veya denetim dosyalarına yazılabilir, yetki sahibi insanlar tarafından yapılan her türlü işlemin kayıt altına alınıp raporlanabilmesine olanak sağlar.

Öncelikle Audit işlemini göreceğimiz database i oluşturalım.

Resim -1

Sonrasında denetimi gerçekleştireceğimiz tablomuzu da oluşturalım.

Resim-2

Oluşturulan veri tabanı ve tabloyu Resim-3’de gözlemliyoruz.

 Resim-3

Sunucu düzeyinde denetleme oluşturmak için Security sekmesinin altında Audits klasörüne sağ tıklayıp yeni bir denetim oluşturuyoruz.

Resim-4

Audit Name: Oluşturacağımız Audite vereceğimiz adı belirtiriz. Yeni bir denetim oluştur denildiğinde bu kısım otomatik olarak oluşturulur ancak Audit Name kısmını ‘Audit_TestTABLE’ olarak düzenledim.

Queue Delay (in milliseconds): Audit verilerinin belirttiğimiz dosyamıza kaç ms de bir yazılacağını ifade eder. Varsayılan minimum değer 1000 ms dir. Auditimiz için de varsayılan değer ayarında bıraktım. Her bir saniyede bir verileri toplayıp yazma işlemini gerçekleştirir. 0 değeri senkronize teslimatı gösterir.

Audit Destination: Audit sonucu gidilecek yeri belirlediğimiz kısımdır. Bu kısmı File olarak belirledim. File dışında Security Log, Application Log a da Audit sonucu için hedef olarak belirleyebiliriz.

File Path: Auditin gideceği dosya yolunu belirttiğimiz kısımdır.

Maximum file size: Audit dosyasının ulaşabileceği en büyük dosya boyutudur. Bu boyutu MB, GB, TB cinsinden bir değer belirtebiliriz. (Varsayılan olarak Unlimited işaretli gelir ancak bu boyutu 1000 olarak sınırlandırdım.)

 Resim-5

 İlgili parametreleri belirledikten sonra OK deyip Auditimizi oluşturuyoruz.

Resim-6

Disable durumda oluşan Auditimiz üzerine sağ tık yapıp Enable Audit sekmesine tıkladığımızda Auditimiz aktif hale gelmiş oluyor.

Resim-7

Serverımız üzerinde Audit oluşturmuş olduk. Şimdi oluşturduğumuz TestDB altında TestTable tablosu üzerinde kim ne tür bir işlem yapmış onun takibini de yapmak için veri tabanı altındaki Security sekmesinde de Database Audit Specifications klasörüne sağ tıklayıp New Database Audit Specification oluşturmamız gerekmektedir.

Resim-8

Name: Database Audit Specification’I isimlendiriyoruz. New Database Audit Specification dediğimizde otomatik olarak bu kısım oluşturulur ancak ‘DatabaseAuditSpecification_TestTable’ olarak düzenledim.

Audit: Server düzeyinde oluşturduğumuz Audit’I buradan seçiyoruz.

Resim-9

Audit Action Type: Audit Action Type kısmında hangi aksiyon için denetim yapacağımızı belirliyoruz. Ben UPDATE işlemi için denetim yapılmasını deneyeceğim.

Resim-10

Object Class kısmında Object, Database, ve Schema seçenekleri karşımıza çıkıyor. Oluşturduğumuz TestTABLE tablomuz üzerinden bir denetleme yapacağımız için Object seçeneğini seçiyoruz.

Resim-11

Object Schema kısmında Object Name kısmında belirleyeceğimiz tablonun şemasını görüntüler.

Object Name kısmında denetlenecek nesnenin adını seçiyoruz. Noktalı butona tıkladığımızda, Select Objects penceresi açılmaktadır. Browser butonuna tıklayıp,

Resim-12

Burada denetimini yapmak istediğimiz tablomuzu seçeceğiz. ([dbo].[TestTABLE])

Resim-13

Principal Name kısmında denetlenen nesnenin yani TestTABLE ın denetimini filtreleriz. Object Name seçeneğindeki gibi noktalı butona tıklıyoruz. Karşımıza Select Objects penceresi çıktı. Burda da Browser butonuna tıklayıp,

Resim-14

Hangi kullanıcıları denetleyeceğimiz detayını biçimlendiriyor. Denetleme için public rolünü belirledim. Public rolü her kullanıcı için varsayılan bir roldür. Bu da bizim tüm kullanıcıları denetleyebilmemiz demektir. İlgili parametreleri belirledikten sonra Create Database Audit Specification penceresini OK butonuyla onaylıyoruz.

Resim-15

Database Audit Specification’ımız karşımıza çıkmaktadır.

Resim-16

Ancak Audit’de olduğu gibi Disable durumunda gelmiştir. Yine aynı şekilde üzerine gelip sağ tıkladığımızda Enable Database Audit Specification’ a tıklıyoruz.

Resim-17

Denetlememizde değişiklikleri görebilmek adına öncelikle oluşturduğumuz TestTABLE adlı tablomuza veri girişi yapıyoruz.

Resim-18

Girdiğimiz verileri Select sorgusu ile gözlemliyoruz.

Resim-19

UPDATE aksiyonu için denetlememizi oluşturduğumuzdan tablomuzda UPDATE komutumuzla işlemler yapıyoruz ki bunları Audit’imizde gözlemleyebilelim.

Resim-20

UPDATE ler sonucu değişiklikleri SELECT sorgusu ile gözlemliyoruz.

Resim-21

Bu değişiklikleri detaylı olarak gözlemleyebilmek için Audit_TestTABLE’ a sağ tıklayıp View Audit Logs sekmesine tıklıyoruz.

Resim-22

Tıkladıktan sonra Log File Viewer penceresi açılıyor. Buradan kimin ne zaman hangi scriptle UPDATE gerçekleştirmiş tüm detaylarıyla ulaşabiliyoruz.

Resim-23

Auditi SSMS ile oluşturmanın yanı sıra T-SQL ile oluşum aşamalarını da gözlemleyelim.

Aynı şekilde Audit oluşturacağımız veri tabanını ve tablomuzu oluşturuyoruz.

Resim-24

CREATE DATABASE TestDB

Resim-25

CREATE TABLE TestTABLE
Id int Primary Key identity(1,1) not null,
Name Nvarchar(30),
Surname Nvarchar(30),
Identity_Number Char(11)
)

Auditimizi master veri tabanında oluşturuyoruz.

FILEPATH ile dosya yolunu belirtiyoruz.

MAXSIZE, denetim dosyasının ulaşabileceği en büyük dosya boyutunu belirtir.

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, Audit verilerinin belirttiğimiz dosyamıza kaç ms de bir yazılacağını ifade eder. Varsayılan minimum değer 1000 ms dir. Auditimiz için de varsayılan değer ayarında bıraktım. Her bir saniyede bir verileri toplayıp yazma işlemini gerçekleştirir. 0 değeri senkronize teslimatı gösterir.

ON_FAILURE ise iki değer alabilir: SHUTDOWN ve CONTINUE. Bir denetim arızası, sistemin güvenliğini veya bütünlüğünü tehlikeye sokabileceği zaman SQL Server instance ının kapanması içindir. Kayıt dosyasında veya diskte yer kalmadıysa ve veriler işlenemiyorsa SHUTDOWN instance ı kapatır.

Resim-26

USE [master]
CREATE SERVER AUDIT [Audit_TestTABLE] TO FILE
(FILEPATH =N’C:\Audits\’,
MAXSIZE = 500 MB ,
MAX_ROLLOVER_FILES = 5,
RESERVE_DISK_SPACE =OFF )
WITH
(QUEUE_DELAY = 1000 ,
ON_FAILURE =SHUTDOWN )
GO
--Auditi Enable etmek için

ALTER SERVER AUDIT [Audit_TestTABLE]
WITH (STATE = ON);
GO

Server düzeyindeki Auditten sonra Database Audit Specification ı veri tabanında(TestDB) oluşturuyoruz. UPDATE aksiyonunu denetleyeceğiz.

Resim-27

USE [TestDB]
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_TestTABLE]
FOR SERVER AUDIT [Audit_TestTABLE]
ADD (UPDATE ON OBJECT::[dbo].[TestTABLE] BY [public])
WITH (STATE = ON)
GO

Aşağıda ise Audit Log detaylarını gösterecek olan scripti görüyoruz.

fn_get_audit_file, SQL Server’da bir sunucu denetimi tarafından oluşturulan denetim dosyasından bilgi döndürür.

Resim-28

SELECT * FROM fn_get_audit_file('C:\Audits\Audit_TestTABLE*.sqlaudit',DEFAULT, DEFAULT)

UPDATE yapılabilecek verileri tablomuza ekliyoruz.

Resim-29

INSERT INTO TestTABLE VALUES ('ARZU', 'DURAN', '12345678902')
,('BERNA','YILMAZ','24680121234')
,('BADE','AYDIN','56789013326')

Ardından UPDATE komutuyla işlem gerçekleştiriyoruz.

Resim-30

UPDATE TestTABLE SET Surname = 'YILDIZ' WHERE Id = 2

Bu aşamadan sonra yapılan aksiyonları da bu şekilde başlıca kolonlarla gözlemleyebiliyoruz.

Resim-31

SELECT 
event_time
,action_id
,succeeded
,session_id
,server_principal_name
,server_instance_name
,database_name
,statement
,file_name
FROM fn_get_audit_file('C:\Audits\Audit_TestTABLE*.sqlaudit',DEFAULT, DEFAULT)

Bu şekilde T-SQL ile de Audit oluşturma işlemimizi tamamladık.

Aşağıdaki tabloda Audit ile Server ve Database düzeyindeki aksiyonların kısaca ne tür denetim getirdiğini inceleyebiliriz.

SQL SERVER AUDİT ACTİON GROUP

Bu konuyla ilgili sorularınızı  alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

https://www.mshowto.org

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15

TAGs: Sql Server Audit Nedir?, Audit, Sql Server, Audit Nedir?, Audit Olusturma, Server Audit Specification, Database Audit Specification, Sql Server Audit Action Group, View Audit Logs, SSMS, SQL Server Management Studio