Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 4

Şifreleme konusunun devamı olan Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 4  isimli yazımı sqlserveronculeri.com adresinden de inceleyebilirsiniz.
Bu yazımla kolon bazlı şifrelemeyi sıfırdan bir örnekle somutlaştırmış olduk. 
Faydalı olması dileğiyle...

Encryption esasları ve Column-Level Encryption kapsamındaki mekanizmaları önceki yazılarımızda incelemiştik. Önceki 3 makaleyi okuyarak konu hakkında daha fazla bilgi edinmek isteyebilirsiniz. Bu bölümde sıfırdan bir örnek ile bu mekanizmaları karma bir şekilde kullanarak şifreleme teorisini pratiğe döküyor olacağız.

Bir önceki yazımı şu linkten ulaşabilirsiniz.

Daha önce de benzerini paylaşmış olduğum aşağıdaki resme bakarak şifreleme için gerekli kombinasyonları rahatça oluşturabiliriz.

Msdn’den aldığım bu resme bakarak sadece password ile verileri şifreleyebildiğim gibi bir certificate veya password ile korunan bir asymmetric key yardımı ile encrypt ettiğimiz symmetric keyi kullanarak da şifreleme yapılabileceğini söyleyebilirim. Buraya bakarak çeşitli kombinasyonları oluşturmak size kalmış.

Biz örneğimizde verileri şifrelemek için certificate ile korunacak bir symmetric key kullanacağız.

Öncelikle EncryptDB isimli bir test veri tabanı ile Musteri isimli bir tablo oluşturalım ve birkaç kayıt girelim.

--test veritabanı oluşturalım

CREATE DATABASE EncyptDB

GO

USE EncyptDB

GO

--Önemli bilgilerin bulunduğu bir tablo oluşturalım

CREATE TABLE Musteri

(

     Id int identity(1,1),

     Ad nvarchar(50),

     KartNum nvarchar(100)

)

GO

--Bir kaç test kaydı

INSERT INTO Musteri VALUES ('Ali','213-123-123-123'),

                                  ('Veli','675-456-345-234')

GO

SELECT * FROM Musteri

Hemen ardından şifreleme için gerekli alt yapıyı oluşturacağız. Bunun için her veri tabanında yalnızca bir tane oluşturulabilen Database Master Key(DMK) create etmeliyiz. DMK ile ilgili bilgileri Şifreleme(Encryption) Esasları – Bölüm 1 isimli yazımda bulabilirsiniz.

--Database Master Key(DMK) oluşturalım

CREATE MASTER KEY

ENCRYPTION BY PASSWORD='pass@word1'

GO

Sonrasında certificate ve bu sertifika ile korunacak symmetric key oluşturuyoruz. Ceriticate ve symmetric key oluşturma ile ilgili bilgileri ise Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 3 isimli yazımdan edinebilirsiniz.

--symmetric key korumak için certificate oluşturalım

--bu sertifikanın korunması için DMK kullanılır.

CREATE CERTIFICATE enc_cert

     WITH SUBJECT='Sifreleme Test'

GO

--encryption için gerekli symmetrik key oluşturalım

--bu keyi yukarıdaki certificate ile koruyalım

CREATE SYMMETRIC KEY enc_sym_key

     WITH ALGORITHM=DES

     ENCRYPTION BY CERTIFICATE enc_cert

Şifreleme için gerekli altyapı hazır durumda. Artık şifrelenmiş metni tabloda tutmak için varbinary bir kolona ihtiyacımız olacak.

--Şifrelenmiş verileri tutmak için yeni kolon ekleyelim

ALTER TABLE Musteri

ADD EncKartNum varbinary(max)

Ve işte şifrelemeye başlıyoruz.

Symmetric key kullandığımız için encryption ve decryption işlemlerinden önce mutlaka key açılmalıdır. Aksi halde fonksiyonlarımızdan sürekli NULL döner.

OPEN SYMMETRIC KEY enc_sym_key

     DECRYPTION BY CERTIFICATE enc_cert

Şifrelenmemiş metnin bulunduğu KartNum kolonundaki veriyi EncryptByKey fonksiyonu ile şifreleyip yeni olşturduğumuz EncKartNum isimli varbinary kolona basalım.

UPDATE Musteri

     SET EncKartNum=EncryptByKey(

                                          Key_GUID('enc_sym_key'),

                                          KartNum);

--bakalım

SELECT * FROM Musteri

Artık verinin şifrelenmiş hali de tablomuzda tutulmakta.

Açık olan symmetric key session düştüğünde kapanır. Öncesinde kapatmak isterseniz şu komutu kullanabilirsiniz.

--açık symmetrik keyi kapatmak için

CLOSE SYMMETRIC KEY enc_sym_key;

Artık temiz metnin bulunduğu KartNum isimli kolona ihtiyacımız yok. Kaldırmamız şifrelemeyi anlamlı hale getirecektir.

--temiz metnin bulunduğu KartNum kolonunu kaldırmak için

ALTER TABLE Musteri

DROP COLUMN KartNum

--bakalım

SELECT * FROM Musteri

Şifreleme işlemini başarıyla tamamladık. Artık temiz metin resimdeki gibi görünecek.

Peki şifreli metni okumak için neler yapmalıyız?

Öncelikle symmetric key kullandığımız için decryption işleminden önce mutlaka keyi açmalıyız.

--Şifreli metni çözmek için

--öncelikle symmetric key açılır

OPEN SYMMETRIC KEY enc_sym_key

     DECRYPTION BY CERTIFICATE enc_cert

Varbinary tipindeki EncKartNum isimli kolonu DecryptByKey fonksiyonu ile çözüyoruz ve nvcarchar tipine convert ediyoruz.

SELECT

     Id,

     Ad,

     CONVERT(NVARCHAR, DecryptByKey(EncKartNum)) as 'Kart Numarası'

FROM Musteri

Böylece şifrelenmiş veriden temiz metni elde etmiş olduk.

Açtığımız symmetric keyi session kapanmadan önce kapatmak istersek şu ifadeyi kullanıyoruz.

--açık symmetrik keyi kapatmak için

CLOSE SYMMETRIC KEY enc_sym_key;

Bu tarz bir şifreleme örnekten de anlaşılacağı gibi şifrelenen alan adedine paralel olarak development iş yükünü arttıracaktır. Ancak ihtiyaca göre az sayıda alanının şifrelenmesi, tüm veri tabanını şifreleyen TDE’ye göre daha az kaynak tüketeceğinden bu yöntemin tercih edilmesi mantıklı olacaktır.

Sizler de Column-Level Encryption mekanizmalarıyla farklı kombinasyonları oluşturarak belli bir metinsel alana odaklanıp yüksek seviyede koruma sağlayabilirsiniz.

Bir sonraki yazımda tüm veri tabanın şifrelenmesi için kullanılan Transparent Data Encryption(TDE) yöntemini inceliyor olacağız. Bu yöntem sayesinde veri tabanının başka serverlara taşınması halinde normal yollarla okunamayacağı garanti altına alınabilmektedir.

Faydalı olması dileğiyle,

Keyifli günler