SQL Common Language Runtime (SQL CLR), Managed Code’un SQL Server üzerinde çalışmasını sağlayan teknolojidir. SQL CLR sayesinde Stored Procedure, Trigger, UDT (User-Defined type), UDF (User-Defined Function) ve UDA (User-Defined Aggregate) Managed Code olarak yazılabilir. Özellikle T-SQL’de olmayan ya da yazılması daha karmaşık olan işlemlerde, Managed Code kullanmak avantaj sağlamaktadır.
Öncelikle kısaca CLR (Common Language Runtime)’dan bahsedelim. CLR, .NET Framework’ün çalışma ortamını sağlar ve CLR, JIT (just-in-time) derlemesi, hafıza yönetimi, tip güvenliği, hata ayıklama, thread yönetimi ve güvenlik gibi işlemleri yürütür.
Resim-1
CLR içerisinde çalışan kod “Managed Code” olarak adlandırılır. Managed Code, CAS (Code Access Security) olarak adlandırılan bir güvenlik modeli sayesinde Assembly’lerin belirli işlemleri yapmasını engeller. SQL Server CAS’ı Managed Code’un güvenli bir şekilde çalışması ve işletim sistemi ile veritabanı üzerindeki işlemlerini denetlemek için kullanır.
Assembly Güvenlik Politikaları
Assembly’lerin izinleri ile ilgili güvenlik politikaları üç şekilde belirlenir:
- Machine Policy: SQL Server’ın yüklü olduğu makinada çalışan bütün Managed Code’u etkiler.
- User Policy: Bir process tarafından host edilen Managed Code’u etkiler. SQL Server için SQL Server servisinin çalıştığı Windows Account’u için geçerlidir.
- Host Policy: Managed Code’un çalıştığı host’u, yani SQL Server’ı etkiler.
SQL Server tarafından yüklenen Assembly’ye verilen yetkiler, User ve Machine Policy ile sınırlanabilir.
SQL Server Host Policy İzinleri
SQL Server üzerinde Assembly’yi oluştururken üç izin seviyesinden birini belirlemek gereklidir: SAFE, EXTERNAL_ACCESS ve UNSAFE
SAFE
Sadece lokal veri erişimi ve iç hesaplamalara izin verilir. En fazla kısıtlamanın olduğu izin setidir. SAFE izini ile çalışan Assembly, dosya sistemi, registry, network gibi dış sistemlere erişemez.
- SecurityPermission: Managed Code çalıştırma yetkisi
- SqlClientPermission:
- Context connection = true, context connection=yes: Sadece context-connection kullanılabilir ve connection string değeri sadece “context connection=true” ve ya “context connection=yes” olabilir.
- AllowBlankPassword = false: Şifreler boş geçilemez.
EXTERNAL_ACCESS
SAFE izinleri ile aynı yetkilere sahip olmakla birlikte, ek olarak dosya sistemi, registry, network gibi dış sistem kaynaklarına erişim yetlileri vardır.
- DistributedTransactionPermission: Distributed transactions’lara izin verilir.
- DNSPermission: DNS’ten bilgi alınabilir.
- EnvironmentPermission: Sistem ve kullanıcı değişkenlerine tam erişim.
- EventLogPermission: Event source oluşturma, mevcut logları okuma, silme, event log oluştuma, event’leri dinleme
- FileIOPermission: Dosya ver klasörlere tam erişim
- KeyContainerPermission: Key container’larına tam erişim.
- NetworkInformationPermission: Ping işlemi.
- RegistryPermission: HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE, HKEY_CURRENT_USER, HKEY_CURRENT_CONFIG, ve HKEY_USERS için okuma yetkisi
- SecurityPermission:
- Assertion: Kodu çağıranların yetkisi olmasa bile kodun yetkisi olması halinde çalışmasını sağlama
- ControlPrincipal: Principal objesini işleme yetkisi
- Execution: Managed Code çalıştırma
- SerializationFormatter: Serialization servisi sağlama
- SmtpPermission: Dış bağlantılarda 25 no.lu SMTP portuna erişim
- SocketPermission: Bütün port ve protokoller için dış bağlantı yapabilme
- SqlClientPermission: Veri kaynağına erişim izni
- StorePermission: X.509 certificate store’a tam erişim.
- WebPermission: Web kaynaklarına tam erişim
UNSAFE
UNSAFE, SQL Server içerisindeki ve dışındaki tüm kaynaklara erişim sağlamakla birlikte, unManaged Code (P/Invoke) çalıştırmaya da izin verir.
Resim-2
Örnek olarak, parametre olarak verilen bir cümlenin baş harflerini büyük harf yapan bir fonksiyon düşünelim:
CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (‘ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-‘, ‘/’, ‘&’,””,'(‘)
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ””
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != ‘S’
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,”)
END
Aynı işlemi bir de Managed Code kullanarak yapalım. StringFunctions adında bir class ve içerisine de ToTitleCase adında bir method yazalım. Örnekte function yazacağımız için, methoda da attribute olarak SqlFunction verelim.
using Microsoft.SqlServer.Server;
using System.Globalization;
public class StringFunctions
{
[SqlFunction]
public static string ToTitleCase(string text)
{
return CultureInfo.CurrentCulture.TextInfo.ToTitleCase(text);
}
}
Görüldüğü gibi tek satırlık bir kod ile aynı işlemi yapabiliyoruz. Projeyi derledikten sonra, DLL’i SQL Server’a yükleyebiliriz. Dış kaynak kullanımı yapmayacağımız için izin setini SAFE olarak veriyoruz.
CREATE ASSEMBLY SqlClrFunctions
FROM ‘C:\SqlClrFunctions.dll’
WITH PERMISSION_SET = SAFE
Yükleme işlemi sonrasında CLR’ı aktif hale getirmek için de aşağıdaki query’yi çalıştırıyoruz.
SP_CONFIGURE ‘CLR ENABLED’, 1
GO
RECONFIGURE
GO
Yükleme işlemi sonrası, Programmability altında, Assemblies altında DLL’imizi görebiliriz:
Resim-3
Sonrasında ise, yüklediğimiz Assembly’yi çağıracağımız fonksiyonu oluşturmak kalıyor.
CREATE FUNCTION ToTitleCase (@Text nvarchar(250))
RETURNS NVARCHAR(250)
AS EXTERNAL NAME SqlClrFunctions.StringFunctions.ToTitleCase;
GO
Fonksiyonu oluşturduktan sonra çağırarak test edebiliriz:
Resim-4
Bu fonksiyonda da bir öncekinde olduğu gibi ile ait ilçeleri web servisten alarak dropdownlist’in item’larına ekleyebiliyoruz. Bu yöntem ilk bakışta biraz uzun ve zahmetli görünse de, kullanım aşamasında oldukça hızlı çalıştığı için tercih edebilirsiniz. Karar sizin.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
