Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Ekim 2017  >>
PaSaÇaPeCuCuPa
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345
Keywords

Merhaba arkadaşlar

Bu makalede içerisinde transaction bulunan bir insert (update ya da delete’te olabilir) stored procedure’ün tetiklediği cümlelerin ne zaman çalışacaklarını inceleyeceğiz.

[more]

Bilindiği gibi trigger’lar for ve instead of olmak üzere ikiye ayrılırlar. For trigger’ı tablo üstünde işlem yapıldığı anda çalışır. Instead of trigger’ı ise For’dan farklı olarak, tablo üstünde yapılmak istenen işlem yerine tetiklenir.

Instead Of trigger’ına örnek vermek gerekirse;

Bir adet x tablosuna kayıt yapan stored procedure’ümüz bir adette x tablosu için instead of trigger’ımız olsun.

Kayıt yapan stored procedure’ü çalıştırdığımız zaman stored procedure içindeki insert kodu yerine instead of trigger’ı içindeki kod çalışır. Dolayısı ile bu stored procedure’ümüz sadece instead of trigger’ını tetiklemeye yarayacaktır.

Kısa bir özetten sonra konumuza dönecek olursak; basit bir örnek yapalım. İki adet tablomuz ve şu şekilde bir stored procedure’ümüz olsun;

INSERT INTO TEST2 (test,DTDATE) VALUES ('TR DEN ÖNCE',GETDATE())

BEGIN TRANSACTION trTest

INSERT INTO TEST (test,DTDATE) VALUES ('TR ARASINDA SP DE',GETDATE())

COMMIT TRANSACTION trTest          

INSERT INTO TEST2 (test,DTDATE) VALUES ('TR DEN SONRA',GETDATE())

 

Test tablomuzun iki adette trigger’ı olsun biri For diğeri de Instead Of ve içerikleri de

INSERT INTO TEST (test,DTDATE) VALUES ('INSTEAD OF',GETDATE())

INSERT INTO TEST (test,DTDATE) VALUES ('FOR’,GETDATE())

 

Çıkan sonuç aşağıdaki gibidir.

makale1

makale2

Görüldüğü gibi TEST2 tablosuna ‘TR DEN ÖNCE’ ve ‘TR DEN SONRA’ cümleleri kayıt oldu. TEST tablosuna ise INSTEAD OF trigger kullandığımız için stored procedure içindeki cümle kayıt olmadı. Bunun yerine triggerlardan gelen önce INSTEAD OF trigger’ı içindeki sonra da FOR trigger’ı (ikisinin aynı anda kayıt olduğunu da gözlemlemiştim) içindeki cümle kayıt oldu.

Burada iki adet tablo kullanmamızın sebebi; hem Instead of hem de For Trigger’ı kullandığımız için, bu iki trigger içerisinde de insert cümleleri bulunduğundan dolayı birbirlerini tetikleyecekler ve stored procedure içindeki cümle çalıştırılmadan aşağıdaki gibi kayıt yapacak.

makale3

 

İyi Çalışmalar

Yunus Emre Kırkanahtar

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugünkü makalemde bana çok sorulan sorulardan birine değineceğim. “Bir tablomdan örneğin cari kart tablomdan bir cari silindiğinde bu durumdan mail yoluyla haberdar edilebilir miyim, yada yeni bir cari insert edildiğinde bu bilgi mail olarak gelebilir mi?”

Cevabımız tabiki evet. Mantalitemiz SQL Server da yapamayacağınız şey yok. :)

[more]

Bu alert işlemini trigger vasıtasıyla mail göndererek yapacağız. Önce sunucumuzda database mail ayarlarını yapacağız. Daha sonra tabloya bir insert trigger yazarak yeni bir kayıt insert edildiğinde insert edilen kayıdın bilgilerini mail olarak göndereceğiz.

İlk olarak database mail ayarlarını yapmamız gerekmekte. Bunun için aşağıdaki makaledeki adımları izleyebilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-2008-den-Mail-Gonderme.aspx

Deneme yapmak için bir table create ediyoruz.

create table userTable(name varchar(100), surName varchar(100))
GO

 

Daha sonra bu table için bir insert trigger yazıyoruz. Bu trigger table’a yeni bir insert olduğunda insert edilen kullanıcının ad ve soyad bilgisini bize mail olarak gönderecek.

CREATE TRIGGER dbo.trgUserTable ON  dbo.userTable
   AFTER INSERT
AS 
BEGIN
  declare @msg varchar(max)=''
		 ,@subjectx varchar(100)='Yeni user''lar oluþturuldu'
  select @msg += name+' '+surName + ', ' from inserted
  set @msg=substring(@msg,0,len(@msg))
  
  EXECUTE msdb.dbo.sp_send_dbmail           
			 @profile_name = 'Control'            
			,@recipients = 'aaa@bbb.com'
			,@body = @msg
			,@subject = @subjectx
			,@body_format = 'TEXT'  
			,@importance    ='HIGH'  
END
GO

 

Son olarak deneme yapmak için bir insert yapıyoruz.

insert userTable 
  select 'Turgay','Sahtiyan'

 

Mailiimizi kontrol ettiğimizde insert işlemi ile ilgili mail’in geldiğini görüyoruz.

image

Bu mantıkta update ve delete trigger’larıda yazarak güncellenen ve silinen kayıtlarıda kontrol edebilmemiz mümkün.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Query ve Stored Procedure’lerin kullanım istatistikleri ile alakalı daha önce 2 makale yayınlamıştım. Bu makalelerde Query ve Stored Procedure’lerin kullanımlarına bakmış, CPU,IO gibi kaynak tüketimlerini inceleyip problemli olanları analiz etmiştik.

Bugünkü yazımda ise bu kapsamdaki son DMV olan sys.dm_exec_trigger_stats ile trigger istatistiklerini inceleyip gene CPU,IO gibi kaynak tüketimleri açısından analizlerini yapacağız. Aynı zamanda gene bu DMV vasıtasıyla kullanılmayan trigger’ları raporlayacağız.

[more]

select * from sys.dm_exec_trigger_stats

 

Sorgu sonucu gelen kolonlar hemen hemen sys.dm_exec_query_stats DMV’sinde gelen kolonlar ile aynı. O yüzden bu kolon açıklamalarına sys.dm_exec_query_stats makalesinden bakabilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Her-Gun-1-DMW-Gun-4-sysdm_exec_query_stats-ile-Query-Istatistikleri.aspx

 

En Çok CPU Tüketen İlk 50 Trigger (Top 50 CPU Consume Trigger)

Şimdi detaylı bir analiz sorgusu yazalım. Bu sorgumuz ile AdventureWorks DB’si üzerinde çalışan trigger’lari analiz edip en çok CPU tüketen trigger’a göre sıralayacağız.

Benim bu amaç için production ortamlarımda kullandığım Query aşağıdaki gibi.

select top 50 
	   DB_NAME(database_id) as DBName,
	   SCHEMA_NAME(o.schema_id) as TableSchemaName,
	   o.name as TableObjectName, 
	   t.name as TriggerObjectName,
       st.[text] as TriggerCode,
	   qp.query_plan,
	   cached_time as first_execution_time,
	   last_execution_time,
	   execution_count,
       ps.total_logical_reads as total_logical_read,
       ps.total_logical_reads/execution_count as avg_logical_read,
       ps.total_worker_time/1000 as total_cpu_time_ms,
       ps.total_worker_time/ps.execution_count/1000 as avg_cpu_time_ms
	   --*
from sys.dm_exec_trigger_stats ps
left join sys.triggers t on t.object_id=ps.object_id
left join sys.objects o on o.object_id=t.parent_id
cross apply sys.dm_exec_sql_text(ps.plan_handle) st
cross apply sys.dm_exec_query_plan(ps.plan_handle) qp
where DB_NAME(database_id)='AdventureWorks'
order by ps.total_worker_time desc

 

Sorgu sonucu bende boş geldi çünkü AdventureWorks’te hiç SP trigger çalımlamış demekki. İstatistik oluşturmak için trigger’ları tetikleyebilmek amaçlı aşağıdaki sorguları yazan sayı kadar çalıştırıyorum.

--3 kez çalıştırılacak
update Production.WorkOrder 
set StartDate=GETDATE(), EndDate=GETDATE(), DueDate=GETDATE()
where WorkOrderID=1

--2 kez çalıştırılacak
update Sales.SalesOrderHeader
set OrderDate=GETDATE(), DueDate=GETDATE(), ShipDate=GETDATE()
where SalesOrderID=43659

 

Şimdi yukarıdaki DMV select’ini tekrar çekelim.

Sonucu 2 resme bölerek gösteriyorum.

1

2


Sorgu sonucunda gelen trigger’lar bizim az önce tetiklediğimiz trigger’lar. Bu trigger’ların ilk kez ve son kez ne zaman çalıştırıldıklarını görebiliyoruz.Aynı zamanda çalışma istatistiklerini de analiz edebiliyoruz. Örneğin Production.WorkOrder table’ı üzerinde bulunan uWorkOrder trigger’ı toplam 3387 kez çalışmış, her çalışmasında ortalama 50 logical read yapmış ve her çalışması ortalama 6 milisaniye sürmüş.

 

Kullanılmayan Trigger Raporu

Uygulama geliştirme aşamasında gerekli olan trigger’lar yazılır fakat bu trigger’ların bazıları uzun vadede kullanılmamaya başlayabilir. Bu yüzden periyodik olarak trigger’larin kullanım istatistikleri incelenip kullanılmayan trigger’lar yorumlanmalı ve drop edip edilmemeleri üzerinde çalışma yapılmalıdır.

Trigger’larin kullanılmama raporu hazırlanırken DMV’de uzun süredir istatistiki bilgi toplandığından emin olunmalıdır. Örneğin dün restart ettiğiniz SQL Server’da toplanan DMV bilgisi size genel resmi çizmeyecektir. O yüzden service’in analiz yapmak için yeterli süredir UP olduğundan emin olunmalıdır.

Ben production ortamlarımda, kullanılmayan trigger raporu için aşağıdaki Query’i kullanmaktayım.

select SCHEMA_NAME(o.schema_id) as TableSchemaName,
	   o.name as TableObjectName, 
	   t.name as TriggerObjectName,
	   t.create_date,
	   t.modify_date
from sys.triggers t
left join sys.objects o on t.parent_id=o.object_id
where t.is_disabled=0 and t.parent_id>0
	and not exists(Select ps.object_id 
					from sys.dm_exec_procedure_stats ps 
					where ps.object_id=t.object_id
					  and ps.database_id=DB_ID('AdventureWorks')
				  )
order by SCHEMA_NAME(o.schema_id),o.name,t.name

 

Bu sorgu size AdventureWorks DB’si için kullanılmayan trigger’ları sorgulayacaktır.

3

 

Sizde yukarıdaki sorguları kullanarak trigger’larınızın kaynak kullanım istatistiklerini analiz edip kötü yazılmış trigger’larda iyileştirme çalışmaları yapabilirsiniz. Ayrıca kullanılmayan trigger raporu çekip bu trigger’ları drop edip etmeme üzerinde çalışabilirsiniz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Yazılım geliştiricilerin en büyük sıkıntılarından biri geliştirdikleri kodların başkaları tarafından çalınma riskidir. Execute file lar ile bu çalınma riski büyük ölçüde azaltılıyorken .Net yeni mimari yapısından kaynaklanan durum gereği .Net executable file ları bildiğiniz üzere reverse engineering yapılabilmektedir. Gerçi bu durumada önlem olarak obfuscator programları var ki bu konu başlı başına bir konu ve daha önce bloğumuzda işlenmiş durumda.

Peki ya Database Devolopment ? Geliştirdiğimiz SP, Function ve trigger ları müşteriye deploy etmemiz gerektiğinde kodlarıyla beraber apaçık şekilde mi vereceğiz. Ya da kendi firmamızda yazdığımız kodların gözükmesini istemiyorsak.

İşte bu tarz bir istek için SQL Server bize WITH Encryption key word unu sunmakta. Bu parametre ile SP, View, Function ve Trigger ları şifrelemek mümkün. 

WITH Encryption ile SP,View,Function ve Trigger ları Şifrelemek


4 obje içinde şifreleme tekniği aynı olduğu için ben sadece view i anlatacağım. Diğerlerinde aynı kod yapısı kullanılabilir.

Ufak bir view i WITH Encryption parametresi ile yazalım ve neler değiştiğini görelim.

use AdventureWorks2008
GO
CREATE VIEW VEncSample WITH ENCRYPTION
AS
  Select FirstName,LastName from Person.Person

 

View e sorgu çekmeyi deneyelim.

select * from VEncSample

 

Gördüğünüz gibi sorgu sonucunun gelme kısmında herhangi bir değişiklik yok.

Şimdi view i modify etmeye çalışalım bunun için AdventureWorks2008 >> Views kısmından view i bulalım.

view1

Burada ilk dikkatinizi çekmek istediğim konu VEncSample view inin yanındaki kilit işareti. Bu işaret bu view in encrypted olduğunu belirtmekte.

View e sağ tıkladığımızda Design in disable olduğunu göreceksiniz. Bununda sebebi encrypted olması. Dolayısıyla view i modify edemiyoruz.

view2

Peki birde view in Script ini oluşturmaya çalışalım. Bu seferde aşağıdaki gibi bir hata ile karşı karşıya kalıyoruz.

Property TextHeader is not available for View '[dbo].[VEncSample]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  The text is encrypted. (Microsoft.SqlServer.Smo)

view3


Gördüğünüz gibi view in içeriğini görüntüleyemiyoruz.

Son bir not. Şifrelediğiniz obje lerin kodlarını kaybetmemek için script lerini açık bir halde yedeklemenizde fayda var.


İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Daha önce “DDL Trigger nedir? Nasıl Kullanılır?” konulu bir makale yazmıştır. Hatta bu makalemde DDL Trigger ile login lerin nasıl yönetileceği üzerinde durmuştuk. Okumadıysanız bu yazımı okumanızı tavsiye ederim.

http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Logon-DDL-Trigger-ile-Login-leri-Yonetme.aspx 

Bugünkü yazımda ise; DDL Trigger ile DDL Event lerin nasıl loglanabileceğini işliyor olacağız.

Bu yapı ile büyük ölçekli firmalarda sizin dışınızda gelişen DDL Eventleri loglayıp bakabileceksiniz.

Örneğin Developer larınız hangi SP leri değiştirmiş, değiştirmeden önceki hali neymiş, yeni hangi loginler create edilmiş gibi DDL Event adı altında sayılan bütün işlemleri loglayacağız.

Bunun için AdventureWorks DB sinde verilen örneği kullanacağım.

İlk önce bir örnek bir DB create edip daha sonrada logları yazacağımız bir table create edelim.

CREATE DATABASE dbDDLEventLog
GO

USE [dbDDLEventLog]
GO
CREATE TABLE [dbo].[DatabaseLog](
	[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
	[PostTime] [datetime] NOT NULL,
	[DatabaseUser] [sysname] NOT NULL,
	[Event] [sysname] NOT NULL,
	[Schema] [sysname] NULL,
	[Object] [sysname] NULL,
	[TSQL] [nvarchar](max) NOT NULL,
	[XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED 
(
	[DatabaseLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

Şimdi loglamayı yapacak DDL Trigger ı create edelim. Açıklamalar kod un içerisindedir.

USE [dbDDLEventLog]
GO

CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    --DDL Event i alıyoruz. Daha sonra DDL Event teki bilgileri değişkenleri alacağız.
    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);
	
	--Log ları tuttuğumuz tabloya DDL Event i yazıyoruz.
    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;
GO

 

Şimdi Trigger ı tetikleyecek bir işlem yapalım. Örneğin yeni bir table create edelim ve bunun loglandığını izleyelim.

USE [dbDDLEventLog]
GO
CREATE table ddlDeneme (a int, b varchar(10))
GO

 

Log ların tutulduğu tabloya bakalım bu event gelmiş mi?

USE [dbDDLEventLog]
GO
select * from DatabaseLog
GO

 

Gördüğünüz gibi table create işlemi loglandı. Sonuca bakacak olursak TSQL kolonunda işlemin TSQL komutu bulunmakta. XMLEVent kolonunda ki veriye tıklarsak yeni bir ekranda yapılan işlemin xml kodunu dökmekte.

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2010-06-13T22:06:26.080</PostTime>
  <SPID>55</SPID>
  <ServerName>FUNNYCIK\S10ENT</ServerName>
  <LoginName>funnycik\tugi</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>dbDDLEventLog</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>ddlDeneme</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE table ddlDeneme (a int, b varchar(10))
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

Bu xml kod da işlemle alakalı bütün detaylı bilgiyi bulabilirsiniz.

Table sonucu gelen veri için kolon açıklamalarını da yazıp yazımızı noktalayalım.

DatabaseLogID Table için primary key
PostTime İşlemin yapıldığı zaman
DatabaseUser İşlemi yapan user
Event İşlem Tipi (DDL Event Type)
Schema İşlemin hangi schema da yapıldığı
Object İşlemin hangi obje üzerinde yapıldığı
TSQL işlemin TSQL kodu
XmlEvent İşlemin XML Event i

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


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 sql 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ın oluşturulması ile alakalı daha önce yazdığım yazıyı okumanızı tavsiye ederim. Buradan erişebilirsiniz.

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

 

Ayrıca daha önce yazdığım tüm trigger ları disable-enable etme script ine aşağıdaki url den erişebilirsiniz.
http://www.turgaysahtiyan.com/post/Sql-Server-da-Triger-larc4b1-Sql-Kod-ile-Aktif-ve-Deaktif-Etmek.aspx

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 url den 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.

Örneklere geçmeden ö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 örnekler;

  1. “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
    
    ALTER 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şılasacak ve bağlantı kuramayacaksınız.

    image 

  2. “t” user ı sadece management studio 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. :)

    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.

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

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


http://www.turgaysahtiyan.com/post/SQL-Server-e28093-SP%28Stored-Procedure%29-lere-Topluca-Yetki-Verme.aspx adresindeki yazımda Stored Procedure lere topluca nasıl yetki verilebileceğini anlatmıştım. Şimdi ise geçenlerde yazdığım bir başka SP den bahsedeğim. Bu SP ile sadece SP lere değil server da ki tüm objelere (table,function,view vs) topluca yetki verebilirsiniz.

SP şu şekilde.

CREATE PROC GivePermissionForAllObjects
	@UserName varchar(max) = '' --Yetki verilmek istenen user ya da role
	,@Databases varchar(Max) = '' --Yetki verilmek istenen DB ya da DB ler, Boş bırakılırsa bütün DB ler
	,@GrantDeny varchar(Max) = 'DENY' --GRANT, DENY, REVOKE
	,@PermissionType varchar(Max) = 'EXECUTE' --Alter, Execute, View Definition vs.
	,@ObjectType varchar(2)='' --SP,View,Function vs.
AS
if (isnull(@ObjectType,'')='') or (isnull(@ObjectType,'')='?') begin
print '
--Object Types
--FN	SQL_SCALAR_FUNCTION
--C 	CHECK_CONSTRAINT
--UQ	UNIQUE_CONSTRAINT
--SQ	SERVICE_QUEUE
--U 	USER_TABLE
--D 	DEFAULT_CONSTRAINT
--PK	PRIMARY_KEY_CONSTRAINT
--V 	VIEW
--S 	SYSTEM_TABLE
--IT	INTERNAL_TABLE
--P 	SQL_STORED_PROCEDURE
--TF	SQL_TABLE_VALUED_FUNCTION
--TR	SQL_TRIGGER
--PC	CLR_STORED_PROCEDURE
--
'
Return
end
SET NOCOUNT ON
--variables
declare @DatabasesTmp varchar(max)=@Databases
declare @DBName sysname,@ProcName sysname,@SchemaName sysname
declare @sql varchar(max)
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##Prodecures(DBName sysname, ProcName sysname, SchemaName sysname)
--

--Split Databases
if @DatabasesTmp<>'' begin
	while @DatabasesTmp <> '' begin
		if CHARINDEX(',',@DatabasesTmp)>0 begin
			insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
			set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
		end else begin
			insert @Tbl_DB select @DatabasesTmp
			set @DatabasesTmp = ''
		end
	end
end else begin
	insert @Tbl_DB select name from sys.databases where database_id>4
end
--

--code
declare CursorX cursor for 
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
	set @sql = 'Use ' +@DBName + '; '+CHAR(10)+'
		insert ##Prodecures
		select '''+@DBName+''',name,SCHEMA_NAME(schema_id) from sys.objects where type='''+@ObjectType+''' '
	exec(@sql)
	fetch next from Cursorx into @DBName	
end
close Cursorx
deallocate Cursorx

declare CursorY cursor for 
select DBName,ProcName,SchemaName from ##Prodecures
open CursorY
fetch from CursorY into @DBName,@ProcName,@SchemaName
while @@FETCH_STATUS=0
begin
	set @sql = 'use ['+ @DBName + ']; ' +
		@GrantDeny+' '+@PermissionType+' ON ['+@SchemaName+'].['+@ProcName+'] TO ['+@UserName+'] AS [dbo]; '
	exec(@sql)
  fetch next from CursorY into @DBName, @ProcName, @SchemaName
end
close CursorY
deallocate CursorY

Drop Table ##Prodecures

SET NOCOUNT OFF
--code

 

Object Type için kullanılabilecek parametreler aşağıdaki gibidir.

FN SQL_SCALAR_FUNCTION
C CHECK_CONSTRAINT
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
PC CLR_STORED_PROCEDURE

 

Bir kaç örnek vermek gerekirse;

turgay user ına bütün AdventureWorks ve AdventureWorks2008 DB lerinde ki bütün table lar için SELECT yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','AdventureWorks,AdventureWorks2008','GRANT','SELECT','U'

 

turgay user ıne bütün DB lerde ki Stored Procedure ler için EXECUTE yetkisi vermek.

exec GivePermissionForAllObjects 'turgay','','GRANT','EXECUTE','P'

 

turgay user ına bütün DB lerde verilmiş View SELECT hakkını geri almak. (REVOKE)

exec GivePermissionForAllObjects 'turgay','','REVOKE','SELECT','V'

 

turgay user ı bütün DB lerde Sistem Table larını Inherit alsa dahi SELECT çekemesin

exec GivePermissionForAllObjects 'turgay','','DENY','SELECT','S'

 

Bu şekilde istediğiniz DB de ki objelere topluca yetki verebilir yada geri alabilirsiniz.

 

İyi çalışmalar.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Merhaba arkadaşlar,

Bu yazımda SQL Server da Trigger konusunu anlatacağım.

Trigger kelime manasıyla tetikleyici manasına gelir. Herhangi bir table da yapılan insert-update-delete işlemlerinde istediğimiz bir sql kodu trigger vasıtasıyla tetikleriz.

Mesela bir fatura girildiği zaman genel toplamı cari hesaba kaydetmek için, yada bir stok kartı yada cari kart silindiği zaman bunu kimin sildiğini loglamak için trigger kullanabiliriz.

Örnek uygulamamızda pubs kataloğunda ki jobs table ından bir job silindiğinden başka bir table a silinen bu job un id sini ve silinme tarihi yazacağız.

Bunun için bir delete trigger ı yazacağız.

Ondan önce silinen kayıtları tutmak için pubs kataloğuna aşağıda ki table ı create ediyoruz.

trigger1

Daha sonra trigger ı yazıyoruz.

--Job_Delete_Trigger trigger ın adı. İstediğimiz başka bir ad da kullanabiliriz.
--trigger var mı diye kontrol ediyoruz..varsa drop ediyoruz..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Job_Delete_Trigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Job_Delete_Trigger]
GO
--Jobs table ına Job_Delete_Trigger adlı trigger ı create et
CREATE TRIGGER Job_Delete_Trigger ON dbo.Jobs
--trigger ı encrypt etmeye yarar. Başkaları göremez. 
--Bu kodu stored procedure ve function larda da kullanabilirsiniz
With Encryption
--Trigger delete esnasında çalışsın
FOR  DELETE 
AS
  --deleted tan bilgileri alıp job_delete table ına yaz
  insert Job_Delete
    select job_id,GetDate()
    from Deleted
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Insert ve Update için trigger yazmak istiyorsak FOR DELETE kısmı yerine FOR INSERT veya FOR UPDATE kullanmalısınız.

Sormak istediğinzi bir şey olursa yorum kısmında sorabilirsiniz.

Kolay gelsin

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Merhaba arkadaşlar,

Bu yazımda vereceğim sql ler ile database de ki triger ları silmeden deaktif ve aktif etmeyi yapabileceksiniz.

Deaktif edecek fonksiyon

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_DISABLE_ALL_TRIGGERS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP Procedure P_DISABLE_ALL_TRIGGERS
GO

CREATE  PROCEDURE dbo.P_DISABLE_ALL_TRIGGERS AS
DECLARE @TableName VARCHAR(30)
BEGIN TRANSACTION
DECLARE Tables CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE   = 'BASE TABLE'
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC('ALTER TABLE ' +  @TableName + ' DISABLE TRIGGER ALL')
    FETCH NEXT FROM Tables INTO @TableName
  END
  IF @@error != 0 ROLLBACK TRANSACTION
  ELSE            COMMIT TRANSACTION
CLOSE Tables
DEALLOCATE Tables

GO

Aktif edecek fonksiyon

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_ENABLE_ALL_TRIGGERS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP Procedure P_ENABLE_ALL_TRIGGERS
GO

CREATE  PROCEDURE dbo.P_ENABLE_ALL_TRIGGERS AS
DECLARE @TableName VARCHAR(30)
BEGIN TRANSACTION
DECLARE Tables CURSOR FOR
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE   = 'BASE TABLE'
OPEN Tables
FETCH NEXT FROM Tables INTO @TableName
  WHILE @@FETCH_STATUS = 0 BEGIN
     EXEC('ALTER TABLE ' +  @TableName + ' ENABLE TRIGGER ALL')
     FETCH NEXT FROM Tables INTO @TableName
  END
  IF @@error != 0 ROLLBACK TRANSACTION
  ELSE            COMMIT TRANSACTION
CLOSE Tables
DEALLOCATE Tables

GO

Kullanımları

exec dbo.P_DISABLE_ALL_TRIGGERS

exec dbo.P_ENABLE_ALL_TRIGGERS

Kolay gelsin

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan