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.
“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
