1. Ana Sayfa
  2. SQL Server
  3. SQL CLR ile SQL Server Üzerinde .NET Assembly Kullanımı

SQL CLR ile SQL Server Üzerinde .NET Assembly Kullanımı

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

* Kaynak: http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/

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ı https://forum.mshowto.org linkini kullanarak ulaşacağınız forum sayfamızda sorabilirsiniz.

Referanslar

www.mshowto.org

Yorum Yap

Yazar Hakkında

Mert Yeter, lisans eğitimini Yıldız Teknik Üniversitesi Gemi İnşaatı Mühendisliği bölümünde, yüksek lisans eğitimini ise Bahçeşehir Üniversitesi Bilgi Teknolojileri bölümünde tamamlamıştır. Yazılım dünyasına üniversitenin ilk yıllarında aldığı QBasic ile başlayan Mert, .NET ve SQL Server gibi Microsoft teknolojileri ile devam etmiş; yüksek lisans tezini ise Linux konusunda yapmıştır. Netaş ve Ziraat Teknoloji gibi sektörün önde gelen firmalarında C#, .NET, SQL Server, Cisco Contact Center ürünleri ve Linux üzerine çalışmış, bir çok firmaya da bu konularda danışmanlık vermiştir. Şu anda da Done'de Cloud Development Manager olarak Azure, .NET Core, SQL Server, Docker vb güncel teknolojiler üzerinde çalışmaktadır.

Yorum Yap