SQL Server’da Kullanıcı Oturumlarını İzleme: IP Adresi ve Login Zamanı ile Kullanıcı Oturumlarının Raporlanması

tarihinde gönderilmiş tarafından

SQL Server yöneticileri, veritabanı güvenliği ve performans yönetimi için kullanıcıların sunucuya en son ne zaman giriş yaptıklarını, hangi IP adreslerinden bağlandıklarını ve oturum açma saatlerini bilmek isteyebilir. Bu bilgiler, hem kullanıcı aktivitelerinin izlenmesinde hem de güvenlik ihlallerinin önlenmesinde önemli rol oynar. Bu yazıda, SQL Server’da kullanıcıların oturum açma bilgilerini nasıl sorgulayacağımızı ve bir rapor haline getirebileceğimizi detaylandıracağız.

1. SQL Server’da Oturum Açma Bilgilerini Anlama

SQL Server, kullanıcıların oturum açma bilgilerini DMV’ler (Dinamik Yönetim Görünümleri) aracılığıyla sağlar. sys.dm_exec_sessions ve sys.dm_exec_connections DMV’leri, kullanıcıların oturumları, bağlantı bilgileri, IP adresleri ve login zamanları hakkında bilgi verir. Bu DMV’lerden elde edilen veriler yalnızca aktif oturumları kapsadığı için, sunucu yeniden başlatıldığında veya kullanıcı oturumu kapandığında bu bilgiler kaybolur.

Birden fazla kullanıcı SQL Server’a eriştiğinde, aşağıdaki soruları yanıtlayabilmek önemlidir:

  • Hangi kullanıcılar şu anda oturum açmış durumda?
  • Her bir kullanıcının sunucuya en son ne zaman bağlandığını nasıl öğrenebiliriz?
  • Kullanıcıların hangi IP adreslerinden bağlandığını nasıl görebiliriz?

Bu bilgileri elde etmek için SQL Server’da bazı sorgular yazarak, veritabanı güvenliğini ve kullanıcı etkinliklerini izleyebiliriz.

2. Kullanıcıların En Son Login Zamanını Sorgulama

Kullanıcıların en son ne zaman SQL Server’a login olduklarını öğrenmek için sys.server_principals ve sys.dm_exec_sessions tablolarını kullanabiliriz. SQL Server’a tanımlı tüm kullanıcıların listesini sys.server_principals tablosu üzerinden alabiliriz. Ancak oturum bilgileri yalnızca aktif oturumları içerdiğinden, geçmiş oturum verilerini görmek istiyorsak SQL Server Audit (Denetim) gibi bir yapı kurmamız gerekir.

Aşağıdaki sorgu, SQL Server’da tanımlı olan tüm kullanıcıların en son login zamanını göstermektedir:

SELECT 
    sp.name AS KullaniciAdi,
    MAX(s.login_time) AS SonLoginZamani
FROM 
    sys.server_principals sp
LEFT JOIN 
    sys.dm_exec_sessions s ON sp.sid = s.security_id
WHERE 
    sp.type IN ('S', 'U') -- 'S' SQL kullanıcıları, 'U' Windows kullanıcıları
GROUP BY 
    sp.name
ORDER BY 
    SonLoginZamani DESC;

Sorgu Açıklaması:

  • sys.server_principals: SQL Server’daki tüm kullanıcıları listeler.
  • sys.dm_exec_sessions: Yalnızca aktif oturumları listeler.
  • LEFT JOIN: Sunucuda tanımlı ancak şu anda aktif olmayan kullanıcıları da listelemek için kullanılır. Oturum açmamış kullanıcıların SonLoginZamani değeri NULL olarak gelecektir.

Bu sorgu, sunucuda tanımlı tüm kullanıcıları listeler ve oturum açmış kullanıcıların en son login zamanını gösterir.

3. Kullanıcıların IP Adreslerini ve Login Zamanlarını Raporlama

Kullanıcıların SQL Server’a bağlanmak için kullandıkları IP adreslerini görmek, güvenlik açısından oldukça önemlidir. Özellikle uzaktan erişim sağlanan sunucularda, kullanıcıların hangi IP’lerden bağlandığına dair bir rapor oluşturmak, yetkisiz erişimlerin önlenmesine yardımcı olabilir.

Aşağıdaki sorgu, oturum açmış kullanıcıların kullanıcı adlarını, IP adreslerini ve login zamanlarını listelemektedir:

SELECT 
    s.login_name AS KullaniciAdi,
    c.client_net_address AS IP_Adresi,
    s.login_time AS LoginZamani
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
ORDER BY 
    LoginZamani DESC;

Sorgu Açıklaması:

  • sys.dm_exec_sessions: Oturum açmış kullanıcıların login zamanlarını içerir.
  • sys.dm_exec_connections: Oturum açan kullanıcıların bağlantı bilgilerini, özellikle client_net_address (kullanıcının IP adresini) verir.
  • JOIN: session_id üzerinden yapılan bu birleşim, her kullanıcının IP adresi ve login zamanını tek bir satırda gösterir.

Bu sorgu, kullanıcıların SQL Server’a erişim sağladıkları IP adreslerini ve oturum açma zamanlarını raporlamaktadır.

4. Senaryo: SQL Server Kullanıcı Oturumları Raporu Hazırlama

Bir veritabanı yöneticisi olarak, güvenliği artırmak ve kullanıcı aktivitelerini takip etmek için haftalık olarak bir kullanıcı oturum raporu hazırlamanız gerektiğini düşünelim. Bu raporda şunlar yer almalı:

  • Sunucuya login olan kullanıcıların kullanıcı adları
  • Her kullanıcının sunucuya en son login zamanı
  • Her kullanıcının bağlantı kurduğu IP adresleri

Yukarıdaki iki sorguyu birleştirerek, kullanıcıların en son oturum açma zamanlarını ve IP adreslerini gösteren tam bir rapor hazırlayabiliriz:

WITH UserLogins AS (
    SELECT 
        sp.name AS KullaniciAdi,
        MAX(s.login_time) AS SonLoginZamani
    FROM 
        sys.server_principals sp
    LEFT JOIN 
        sys.dm_exec_sessions s ON sp.sid = s.security_id
    WHERE 
        sp.type IN ('S', 'U')
    GROUP BY 
        sp.name
),
ActiveUserSessions AS (
    SELECT 
        s.login_name AS KullaniciAdi,
        c.client_net_address AS IP_Adresi,
        s.login_time AS LoginZamani
    FROM 
        sys.dm_exec_sessions s
    JOIN 
        sys.dm_exec_connections c ON s.session_id = c.session_id
)
SELECT 
    ul.KullaniciAdi,
    ul.SonLoginZamani,
    aus.IP_Adresi,
    aus.LoginZamani
FROM 
    UserLogins ul
LEFT JOIN 
    ActiveUserSessions aus ON ul.KullaniciAdi = aus.KullaniciAdi
ORDER BY 
    ul.SonLoginZamani DESC;

Sorgu Açıklaması:

  • UserLogins: sys.server_principals ve sys.dm_exec_sessions kullanılarak kullanıcıların en son login zamanları bulunur.
  • ActiveUserSessions: sys.dm_exec_sessions ve sys.dm_exec_connections kullanılarak aktif oturumlar için IP adresi ve login zamanı bilgileri alınır.
  • Sonuç olarak, tüm kullanıcılar, en son login zamanı, oturum açmışlarsa IP adresleri ve login zamanları raporda yer alır.

5. Güvenlik ve Denetim İçin Dikkat Edilmesi Gereken Noktalar

Bu sorgular yalnızca aktif oturumları izlediğinden, tarihsel oturum kayıtlarına ihtiyaç duyduğunuzda SQL Server Audit (Denetim) gibi daha kalıcı bir çözüm kullanmanız önerilir.

Audit, kullanıcı etkinliklerini ve erişim zamanlarını kaydederek daha kapsamlı güvenlik raporları oluşturmanıza olanak tanır.

Sonuç

Bu makalede, SQL Server kullanıcılarının en son login zamanlarını, IP adreslerini ve login bilgilerini raporlamak için nasıl sorgular yazabileceğinizi öğrendiniz.

Bu bilgiler, veritabanı güvenliğini artırmak ve yetkisiz erişimleri izlemek için kritik öneme sahiptir.

SQL Server Audit ile bu raporlara kalıcı olarak erişmek ve güvenlik stratejilerinizi daha etkin hale getirmek de mümkündür.

Loading