4 ADIMDA AUDIT LOG AÇMA VE OKUMA

Bu yazıda SQL Server’da Instance ve Database temelli yapılan değişikliklerin denetlenmesi amacı ile kullanılan AUDIT’lerin nasıl kurulduğuna ve önemine değineceğiz. Öncelikle 2 çeşit audit olduğunu bilmeliyiz. Server Audit Specification (GÖRSEL-1) Sql server bazında hangi kullanıcıya hangi yetkilerin verildiğini, kimlerin login olduğu sql server’ın yönetilmesine dair yapılan işlemleri tuttuğumuz auditlerdir. Database Audit Specification (GÖRSEL-2) Database bazında yapılan insert, update, delete, execute veya select gibi işlemlerin kaydını tuttuğumuz auditlerdir.            GÖRSEL-1                                                                                            GÖRSEL-2 Audit(denetim) kayıtlarını çoğu kurum üçüncü parti bir yazılım ile yönetmektedir. Ancak biz burada SQL’in bize sağladığı imkanlar ile audit kayıtlarımızı saklayacağız. Örneğimiz üzerinde oluşturacağımız audit kayıtlarını GÖRSEL-3 görünen global audit tarafına […]

4 ADIMDA AUDIT LOG AÇMA VE OKUMA

Bu yazıda SQL Server’da Instance ve Database temelli yapılan değişikliklerin denetlenmesi amacı ile kullanılan AUDIT’lerin nasıl kurulduğuna ve önemine değineceğiz.

Öncelikle 2 çeşit audit olduğunu bilmeliyiz.

  1. Server Audit Specification (GÖRSEL-1)

Sql server bazında hangi kullanıcıya hangi yetkilerin verildiğini, kimlerin login olduğu sql server’ın yönetilmesine dair yapılan işlemleri tuttuğumuz auditlerdir.

  1. Database Audit Specification (GÖRSEL-2)

Database bazında yapılan insert, update, delete, execute veya select gibi işlemlerin kaydını tuttuğumuz auditlerdir.

           GÖRSEL-1           

                                                                                GÖRSEL-2

Audit(denetim) kayıtlarını çoğu kurum üçüncü parti bir yazılım ile yönetmektedir. Ancak biz burada SQL’in bize sağladığı imkanlar ile audit kayıtlarımızı saklayacağız. Örneğimiz üzerinde oluşturacağımız audit kayıtlarını GÖRSEL-3 görünen global audit tarafına bağlayacağız ve işlemlerimizi tamamlayacağız.

GÖRSEL-3

Şimdi örneğimize geçelim;

1. ADIM : GLOBAL AUDIT OLUŞTURMA:

Database ve server bazlı auditleri kayıt altına alabilmek için global düzeydeki oluşturulan auditlere bağlamamız gerekmektedir. Burada tek bir global audit dosya sistemine bağlayabiliriz veya database ve server düzeyindeki auditleri farklı global auditlere de bağlayabiliriz. Biz database ve server audit için iki farklı global audit dosya sistemi oluşturacağız. Birisine “SERVER_AUDIT” diğerine ise “DATABASE AUDIT” ismini vereceğiz.

Global auditleri oluşturmak için Security->Audit üzerinde sağ tık “New Audit” seçeneğine tıklıyoruz ve server audit için kullanılmak üzere global bir audit açıyoruz. 10 adet olacak şekilde 100 MB’lık dosyaları C:\AUDIT\SERVER AUDIT klasörü içerisinde saklayacak şekilde oluşturdum. Siz kendi sisteminiz için gereksinimlerinizi karşılayacak şekilde daha büyük/küçük dosya yapısı ile oluşturabilisiniz.

GÖRSEL-4

Sonrasında database audit için kullanılmak üzere farklı bir global audit açtık. Yine burada da kendi ihtiyaçlarımıza göre bir boyut ve dosya sayısı belirledik. Ben 200 MB’lık 10 dosya olacak şekilde belirledim.

GÖRSEL-5

NOT: Oluşturduğumuz global auditler içerisinde değişiklik yapmak istediğimiz de örneğin dosya sayısını veya boyutunu değiştirmek istediğimizde önce auditleri disable çekmeli sonrasında değişikleri yaptıktan sonra tekrar enable çekmeliyiz. Enable halde olan global auditler üzerinde değişiklik yapılamaz.

Güvenlik Notu: Audit dosyaları güvenlik gereği domain admin’in kontrol ettiği başka bir sunucuda bulunan bir klasör içerisinde tutulmalıdır. Ve bu klasörde; SQL Server’in servis hesabına dosya yoluna paylaşım verilmeli, insert ve modify yetkisi tanımlanmalıdır. Bu şekilde audit dosyalarının silinmesinden ve başka bir yere taşınmasından korunmuş oluruz.

Create audit bölümündeki başlıkları inceleyecek olursak; GÖRSEL-6’daki arayüz esasta audit dosya sisteminin yönetiminin nasıl yapılacağı ile ilgilidir. Hangi auditlerin tutulacağı bu arayüzde yapılandırılmaz.

 

GÖRSEL-6

Queue Delay: Kaç milisaniye de bir auditlerin kayıt altına alınacağını gösterir.

On Audit Log Faileure: Audit log alınma işleminde bir hata alındığı takdirde yapcağı işlemi seçer. Eğer continue seçerseniz log alınamadığı durumlarda sistem çalışmaya devam eder. Fail operation seçeneğini seçersek loglar alınamadığında o esnada yapılan transaction’ı iptal eder. Shut down server dersek log alınamadığında sql server servisini durduracaktır.

Audit destination: Auditlerin tutulacağı yolu gösteriririz.

Audit File Maximum Limit: Maksimum audit dosya sayısını gösteririr. Maximum rollover files seçeneğini seçersek oluşturduğumuz kadar dosya üzerinden bir döngü ile audit kayıtlarını yazmaya devam eder.  Audit kayıtlarınız dolduğu zaman en eski kayıtların üzerinde yazarak devam edecektir. Eğer kritik audit kayıtlarınız var ise audit dosya hesaplamanızı iyi bir şekilde yapmanız gerekiyor ya da maximum files’ı unlimited’a auditlerin tutulduğu kayıtların tutulduğu yerde sürekli disk artırımına gidebilirsiniz bunu sistem ihtiyaçlarınıza göre planlamanız gerekmektedir. Maximum files ile dosya sayısını belirleriz.

Maximum file size: Oluşan audit dosyalarının boyutlarını belirleriz.

Şimdi gelelim Database ve Server bazında audit oluşturmaya ve onları global auditlere bağlamaya.

2. ADIM : DATABASE BAZINDA AUDIT OLUŞTURMA

Herhangi bir database’in altında Security-> Database Audit Specification üzerinde sağ tık “New Database Audit Specification” seçeneği ile database bazlı audit oluşturuyoruz. Bu işlemleri auditleri açmak istediğiniz tüm databaseler için ayrı ayrı yapmanız gerekmektedir.

Name kısmına VeritabanıAdı_Audit olacak şekilde isimlendiriyoruz. Audit seçeneğini global’de olan önceden oluşturduğumuz DATABASE_AUDIT’e bağlıyoruz ki kayıt altına alınabilsin. Audit Action Type üzerinden hangi işlemleri kayıt altına alacağımızı seçiyoruz. Ben bu örnekte insert, update ve delete işlemlerini seçiyorum. Object Class’ta da database seçip, Principal Name kısmında dbo’yu seçiyoruz. Burada DBO’yu seçmek önemli; sysadmin dahil tüm kullanıcıların yaptığı işlemleri kayıt etmek amacıyla dbo seçilir.

   GÖRSEL-7

 

Eğer always on sistem çalışıyorsanız primary sunucuda oluşturacağınız her database audit, secondary node’lara da otomatik olarak oluşacaktır.

3. ADIM: SERVER BAZINDA AUDIT OLUŞTURMA

Instance altında Security-> Server Audit Specification üzerinde sağ tık “New Server Audit Specification” seçeneği ile server bazlı audit oluşturuyoruz.

Name’e genel olarak ServerAudit verdim ve bu sefer global’de oluşturduğum SERVER_AUDIT’E bağladım. Audit action type olarakta istinasız tüm işlemleri kayıt altına almak gerekir. Hangi işlemin ihtiyaç olup olmayağını bilemeyiz işimizi sağlama almak adına hepsini tek tek seçelim. Ben örnekte(GÖRSEL-8) ekrana sığması amacıyla 5 tane gösterdim.  Ben alt kısma kolaylık olması için tüm audit action type’ları create eden scripti bıraktım.

GÖRSEL-8

Eğer always on sistem çalışıyorsanız server bazlı auditleri her node için ayrı ayrı oluşturmanız gerekmektedir.

 

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [SERVER_AUDIT]
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (DATABASE_LOGOUT_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OPERATION_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DBCC_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (FULLTEXT_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (LOGOUT_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (TRACE_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON)
GO

 

 

ENABLE ETMEYİ UNUTMAYIN!

Oluşturduğumuz tüm auditler default olarak disable halde oluşacaktır. O sebeple enable etmeyi unutmayın.

4. ADIM : AUDITLERİ OKUMA İŞLEMİ

Global security üzerinde sağ tık view audit log’a tıklayarak okuyabiliriz. Ancak loglarımız büyüdükçe buradan okuma işlemi yavaş olmaktadır.

GÖRSEL-9

Daha hızlı okuma yapabilmek için aşağıdaki örnek scriptte olduğu gibi dosya yolu ve adını vererekte log üzerinde daha hızlı okuma yapabilirsiniz.

SELECT * FROM 
sys.fn_get_audit_file('C:\AUDIT\DATABASE AUDIT\DATABASE_AUDIT_D15818FB-5DF6-4B76-968F-11CF3C2E079E_0_133423670120770000.sqlaudit',default,default)

Dosya sisteminde benim gibi birden fazla audit dosyanız varsa aynı dosya yolundaki tüm auditleri okumak için yıldızı kullanabilirsiniz.

SELECT * FROM 
sys.fn_get_audit_file('C:\AUDIT\SERVER AUDIT\*.sqlaudit',default,default)

 

SONUCA GELECEK OLURSAK;

Audit kayıtlarının olduğu diskin doğru bir şekilde yönetilmesi gerekmektedir. INSTANCE yoğunsa hızlı bir şekilde auditler büyür. Eğer select işlemlerini de tutmak isterseniz audit dataları hızlı bir şekilde büyük boyutlara ulaşabilir. Bu konuda disk yönetimi konusunda dikkatli olunmalıdır.

Database ve server auditlerinin “Audit Action Type” bölümününde ihtiyaçlarınıza göre belirlemek isterseniz detaylarını öğrenmek için linkteki detayları inceleyebilirsiniz: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16&redirectedfrom=MSDN

 

Benzer Yazılar

SQL SERVER SERViS RESTART HATASI

SQL Server 2 hafta önce

Bu haftaki yazımızda karşılan bir hata üzerindeki; logları ve çözümünü anlatacağım. Aşağıdaki GÖRSEL-1’de görüldüğü üzere SQL servisini restart ettiğimiz sırada bir hata ile karşılaşıyoruz. Servis running state’e geçemiyor. “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details” şeklinde bir uyarı veriyor. Hatanın çözümüne doğru ilerlerken farklı servis hesaplarıyla veya “Local System” hesabı ile restart etmeye çalıştığınızda servis ilginç bir şekilde ayağa kalkıyor. Ancak Always on sistem çalışıyorsanız farklı servis hesaplarını kullandığınızda always on size haberleşme izni vermiyor. Aynı hesabın şifresi ile ilgili sorunlar olduğu düşünüp hesabın şifresini de değiştirdiğiniz de yine sonuç alamıyorsunuz. Burdan yola çıkıldığında sıkıntı servis hesabında gibi görünüyor olabilir ancak çözüme geçildiğinde regedit üzerinde yapacağımız bir işlem ile sorunu çözüyoruz. Servis hesabının kaydının olduğu regedit kaydını siliyoruz. Regedit üzerindeki servis hesap bilgisi güncellendiğinde sorun çözülmüş olmakta. GÖRSEL-1  Servis restart edildiğinde SQL’in verdiği Error […]

SQL’DE İKİ NODE’UN RESOLVING DURUMA DÜŞMESİ VE ÇÖZÜMÜ

SQL Server 1 ay önce

Bu yazımızda failover olma işlemi esnasında karşılaşılan bir durumdan kısaca bahsedeceğim. Kısa bir yazı olacak ama önemli olduğunu düşünüyorum. Bazen failover olmak istediğinizde cluster secondary’e node’a failover olamaz, hem secondary hem de primary node’unuz resolving durumuna geçer. Bu durumla daha çok otomatik failover olma durumlarında karşılaşılır çünkü sistem failover’a aslında hazır değildir ancak cluster bunu bir şekilde bilemez. Failover olma gerçekleşemez bir anlamda sql cluster askıda kalır ve hiçbir sunucu da sql engine çalışmaya devam edemez. (GÖRSEL-1) GÖRSEL-1 GÖRSEL-1 üzerinde gördüğünüz üzere availability group resolving duruma düşer. Availability replica’lar üzerinde de gördüğünüz üzere primary ve secondary tüm node’lar resolving state’e düşer. Böyle bir durumunda iki farklı çözüm yolumuz var;   Çözüm: ikinci node’a sunucu restart’ı atmak. Bu noktada secondary sql node’a servis restart atmak işe yaramıyor. Zaten db’ler iki taraflı resolving modda. O sebeple ancak sunucu restart atıldığında cluster ayakta olan sunucuyu görüyor ve askıda kalma durumundan ilk başta primary […]

Query Store Nedir?

SQL Server 1 ay önce

Query Store ile birlikte execution planın seçimi ve bu sürecin performansa etkisini anlayabiliriz. SQL Server içerisinde bulunan Query Store özelliği, çalıştırılan sorguların execution planını ve bu sırada oluşan istatistiklerini otomatik olarak yakalar. Böylece query plan değişikliği ile oluşan problemleri de hızlı ve kolay şekilde fark edebiliriz. Elinizde bulunan bir sorguya ait query plan zamanla değişebilir. Bunun birçok sebebi vardır. Tablo yapısına yeni bir column eklenmesi Veri tipinin değiştirilmesi Sorgularda yeni parametrelerin eklenip çıkarılması Verilerde, schemalarda veya sorgu parametrelerindeki değişiklik Burada önemli olan ise bazen bu değişimler sorgunun yavaş çalışmasına neden olur. Query Store ile beraber bu yavaşlığın kök nedenine inmek daha kolay oldu. Ayrıca query store sayesinde ilgili sorguya ait read-write bilgileri ve cpu tüketimi bilgilerine de erişebilirsiniz. Query Store’u veritabanı seviyesinde aktif edebiliyoruz. Veritabanı üzerine sağ tıklayarak properties diyoruz ve Query Store sekmesine geliyoruz. Operation Mode alanından Read Write’ı seçiyoruz. Böylelikle Query Store gerekli bilgiyi toplayabilir ve size ilgili sonuçları […]

0 Yorum

Yorum Yaz

Rastgele