SQL Server 2008 – Logon DDL Trigger ile Login leri Yönetmek

tarafından 2 Ocak 2011 tarihinde Microsoft kategorisine yazıldı.



Bu makalemde DDL trigger ile SQL Server login işlemi esnasında gelen login isteğini kontrol edeceğiz ve kullanıcı istediğimiz makinadan yada uygulamadan bağlanmadıysa login i engelleyeceğiz.




Canlı bir örnek vermek gerekirse; production SQÇ Server ortamınızda bir user create ettiniz ve uygulamada bu user ı kullanıyorsunuz. User ın bu uygulama harici kullanılmasını örneğin SSMS kullanılarak login olunmasını engellemek istiyorsunuz. İşte bu engellemeyi DDL trigger ile yapacağız.

 



Ama bu işleme geçmeden önce SQL Server DDL Trigger lar hakkında biraz bilgi vermek istiyorum.

 


DDL – DML Nedir?

 



SQL Server da DML(Data Manipulation Language) ve DDL(Data Definition Language) olmak üzere 2 tip script tipi vardır.

 



DML, database üzerinde yapılan insert,update,delete gibi data manipulation işlemlerini ifade eder. DML Trigger lar ile bir tablo  için insert,update vb işlemlerinde belirli bir script i çalıştırabilirsiniz. Örneğin bir stok programınız var. Stok her hareket gördüğünde bu stoğun envanter değerini azaltmak yada arttırmak istiyorsunuz.  Stok Hareket tablosuna bir insert yada update trigger koyarak stok envanter tablosunu güncelleyebilirsiniz.

 



DDL ise database yada server üzerinde yapılan obje create,drop,alter gibi obje değişikliklerini ifade eder. Database creatinde yada table drop unda DDL trigger kullanarak işlemi yakalayabilir ve buna göre aksiyon alabiliriz.

 



İlk DDL Trigger ımız

 



Örneğin aşağıdaki DDL trigger ile kullanıcıların database üzerinde table drop etmesini engelleyebiliriz.

 



CREATE TRIGGER trg_Table_Drop

 


ON DATABASE

 


FOR DROP_TABLE

 


AS

 


PRINT ‘Table drop işlemi engellenmiştir!. “trg_Table_Drop trigger ýný disable etmeniz gerekmektedir.’

 


ROLLBACK

 





DDL Trigger ı geçici olarak disable etmeniz gerekebilir. Örneğin yukarıdaki gibi table drop ddl trigger ınız var. Drop etmeye çalıştınız ve hata mesajını aldınız. Ama genede drop etmeniz gerekiyor. Bu durumda trigger ı disable edip drop işlemini yaptıktan sonra tekrar enable edebilirsiniz.

 





DISABLE TRIGGER [logon_ddl_trg] ON ALL SERVER

 


ENABLE TRIGGER [logon_ddl_trg] ON ALL SERVER

 





Server da create edilmiş DDL Trigger lara sys.server_triggers table ından bakabilirsiniz.

 



select * from sys.server_triggers

 



sys.server_trigger_events table ını kullanarakta bu trigger ların hangi event ler için create edilmiş olduğuna bakabilirsiniz.

 



select * from sys.server_trigger_events

 



Daha öncede söylediğim gibi DDL trigger lar sadece bazı event ler ile tetiklenmektedir. Bu event lerin tamamına aşağıdaki MSDN url sinden erişebilirsiniz.

 



http://msdn.microsoft.com/en-us/library/bb522542.aspx

 



DDL Trigger İle Login Kontrolü

 



Şimdi asıl konumuz olan DDL trigger ile Login kontrolüne geçelim. Konuyu anlamanın daha anlaşılır olacağını düşündüğüm için direk örneklere geçiyorum. DDL Trigger ı yazmadan önce “t” name inde bir SQL server user create ediyoruz.

 



USE [master]

 


GO

 


if exists (Select * from sql_logins where type_desc=‘SQL_LOGIN’ and name=‘t’)

 


DROP LOGIN [t]

 


GO

 


CREATE LOGIN [t] WITH PASSWORD=N’t’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

 


GO

 



Şimdi DDL Trigger örnekleri;

 


 “t “ SQL Server login i SQL Server a bağlanamasın.

if
exists(select * from sys.server_triggers
    where name=‘logon_ddl_trg’)
DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;
GO

CREATE
TRIGGER [logon_ddl_trg]

 


ON ALL SERVER WITH EXECUTE AS ‘sa’

 


FOR LOGON

 


AS

 


BEGIN

 


IF ORIGINAL_LOGIN()= ‘t’ BEGIN

 


rollback;

 


END

 


END

 



Yukarıdaki trigger ı Execute ettikten sonra SSMS üzerinden bağlantı kurmayı denediğinizde aşağıdaki gibi bir hata mesajıyla karşılaşacak ve bağlantı kuramayacaksınız.

 





image001

 




 


 “t” user ı sadece management studi o kullanarak login olamasın, diğer uygulamalarla login olabilsin.

if exists(select * from sys.server_triggers
    where name=‘logon_ddl_trg’)
DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;
GO

ALTER
TRIGGER [logon_ddl_trg]

 


ON ALL SERVER WITH EXECUTE AS ‘sa’

 


FOR LOGON

 


AS

 


BEGIN

 


IF ORIGINAL_LOGIN()= ‘t’
     and APP_NAME()= ‘Microsoft SQL Server Management Studio’ BEGIN

 


rollback;

 


END

 


END

 



Trigger ı execute ettikten sonra SSMS üzerinden bağlantı kurmayı denerseniz bağlanamadığınızı göreceksiniz.  Birde osql ile bağlantı kurmayı deneyelim. Bunun için command promp ta osql –S sahtiyan_tu –U t –P t yazıp enter a basalım. Bingo. Bağlantı kurabildik J Bu tarz bir trigger la application için tanımladığınız user ın sadece o application ile kullanılmasını sağlayabilir ve güvenliğinizi arttırabilirsiniz.



Son olarak LoginName ve ApplicationName gibi bilgileri gelen eventdata dan almak mümkün. Bununla alakalı örneğide aşağıda inceleyebilirsiniz.




if exists(select * from sys.server_triggers

 


where name=‘logon_ddl_trg’)

 


DROP TRIGGER [logon_ddl_trg] ON ALL SERVER;

 


GO

 



CREATE TRIGGER [logon_ddl_trg]

 


ON ALL SERVER WITH EXECUTE AS ‘sa’

 


FOR LOGON

 


AS

 


BEGIN

 


declare @EventData xml,

 


@PostTime datetime,

 


@LoginName varchar(50),

 


@ClientHost varchar(50),

 


@LoginType varchar(50)

 


set @EventData = eventdata()

 



set @PostTime = @EventData.value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘datetime’)

 


set @LoginName = @EventData.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(50)’)

 


set @ClientHost = @EventData.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘varchar(50)’)

 


set @LoginType = @EventData.value(‘(/EVENT_INSTANCE/LoginType)[1]’, ‘varchar(50)’)

 



if @LoginType = ‘SQL Login’ and @LoginName = ‘t’

 


rollback;         

 


end

 


GO

 


İyi Çalışmalar

 

Bu yazı blog üzerinde Tayfun DEĞER tarafından paylaşılmıştır. 2009 yılında açılan blog kısa zaman içerisinde büyük bir izleyici kitlesine sahip olmuştur.Tayfun DEĞER danışmanlık ve eğitimler vermektedir. vExpert 2013-2015, VCP5, VCP5-DT, VCP-Cloud ve MCSE sertifikalarına sahiptir.Twitter 'dan @tayfundeger veya RSS ile sitedeki değişiklikleri takip edebilirsiniz.