SQL Server 2014 Yenilikleri - 5 (Backup Encyrption)

Veritabanları üzerinde çalışan hemen hemen herkes bir şekilde backup/restore işlemleriyle muhatap olmuştur. Yedekler çoğu zaman farklı lokasyonlara taşınır. Zaten işi sağlama almak için yedeklerin kopyalarının farklı lokasyonlarda tutulması iyi bir şeydir.

Ancak farklı lokasyonlara dağıtılan yedekler veri güvenliğini riske atar. Eğer bilgilerin gizli kalması önemli ise, belli alanların okunamamasını arzu ederiz. Hatta veritabanının farklı bir serverda açılamamasını isteriz. Bu tür istekler Microsoft Azure Storageler gibi bulut teknolojileri kullanılmaya başlandıkça daha da artmaktadır. Microsoft Azure tarafında yeterli güvenlik alınmasına rağmen içi rahat etmeyenler için şifreleme yoğun şekilde başvurulabilecek bir konu haline gelecektir.

Veri güvenliğini, şifreleme yaparak sağlamamız gereken durumlar olduğunda; Önemli verilerin kolayca okunamamasını istiyorsak Column-Level Encrytpion yöntemini, veritabanının farklı bir serverda komple açılamamasını istiyorsak TDE (Transparent Data Encryption) yöntemini tercih ederiz.

TDE yöntemi ile şifreleme yaptığımızda pageler diskte encyrpt edilmiş, memoryde decrypt edilmiş olarak tutulur. Bu işlem sürekli tekrarlandığı için CPU’ya %5-10 civarı yük biner. Zamanla CPU konusunda zaten problemi olan serverlar için tercih edilemeyecek bir yöntem haline gelir. Eğer önemli olan sadece yedeklerin şifreli olarak saklanabilmesi ise o zaman SQL Server 2014 ile birlikte gelen backup encyrption özelliği tam ihtiyacımız olan şey demektir.

Hem veritabanın başka serverda kullanılamamasını hem de yedeklerin şifrelenmesini istiyorsak, bu durumda TDE ve Backup Encyrption özelliklerini birlikte kullanabiliriz. Tabi her şeyden önce şifrelemede kullanılan sertifika ve keylerin yedeklerini sağlıklı yerlerde saklamamız gerekir. Aksi halde şifreleme yaptığımız veritabanlarını tekrar kullanamayız.

Şifreleme konusu ile ilgili 5 bölümlük bir makale serisi hazırlamıştım. İlgililer son bölüm olan TDE yöntemine şu linkten bir göz atabilirler.

http://abdullahkise.blogspot.com.tr/2014/02/dosya-database-bazl-sifreleme.html

Biz bu yazımızda SQL Server 2014 ile birlikte gelen Backup Encyrption özelliğine yoğunlaşalım ve yedeklerimizi nasıl şifreleyeceğimizi inceleyelim.

Yedeklerimizi bir certificate veya asimetrik key yardımıyla şifrelemekteyiz. Yedek alırken WITH seçeneklerinden ENCRYPTION ifadesi ile şifreleme algoritmasını ve certificate veya asymmetric keyi belirtiyoruz. Tabi bu certificate veya asymmetric keyinde korunmaya ihtiyacı var. Bunları da master veritabanının Database Master Key’i (DMK)  ile şifreliyoruz. Database Master Key kolayca oluşturulabilen private bir keydir.

Database Master Key (DMK), certificate ve asymmetric key hakkında daha fazla bilgi almak isterseniz şifreleme serimizdeki ilk yazıdan başlayabilirsiniz.

http://abdullahkise.blogspot.com.tr/2013/05/sifrelemeencryption-esaslar-bolum-1.html

Yedekleri nasıl şifreliyoruz?

İlk adım olan master veritabanın Database Master Keyini oluşturalım:

--master veritabanının "Database Master Key"i :

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';

Burdaki Password, DMK’yı korumak için kullanılır ve Windows Password Policy mekanizmasına uygun olmalıdır.

DMK oluşturduktan hemen sonra bir certificate veya asymmetrik key oluşturuyoruz. Biz burada certificate ile ilerliyor olacağız.

--Yedeklemede kullanılacak sertifika:

USE Master;

GO

CREATE CERTIFICATE YedekSifrelemeCert

   WITH SUBJECT = 'yedekleme sertifikası;

Sertifikaların da korunmaya(şifrelenmeye) ihtiyacı vardır. Çeşitli şekillerde şifrelenebilirler. Eğer bu konuda bir şey belirtilmezse otomatik olarak DMK yardımıyla şifrelenmiş olurlar. Bizim oluşturduğumuz certificate master veritabanın DMK’sı ile korunmaktadır.

Şimdi sıra geldi backup alırken bu sertifikayı göstermeye ve şifreleme algoritmasını seçmeye.

BACKUP DATABASE veritabani

TO  DISK = N'C:\yedeklerim\veritabani_enc.bak'

WITH

      ENCRYPTION

         (

              ALGORITHM = AES_128, --AES_192, AES_256, Triple DES kullanılabilir.

              SERVER CERTIFICATE = [YedekSifrelemeCert]

         )

Bu örneğimizde AES_128 algoritmasını kullandık. Siz ihtiyaca göre sisteminiz için diğer algoritmaları da (AES_192, AES_256, Triple DES) tercih edebilirsiniz.

Yedek almak için scripti çalıştırdıktan hemen sonra bir uyarı ile karşılaşacaksınız. Bu uyarı yedek almaya engel değil ancak çok önemli bir hatırlatmayı içeriyor. Şifrelemede kullandığımız sertifikanın bir yedeği alınmadı. Sertifika kullanılamaz hale gelirse yedek de açılamaz hale gelir. Yani certificate ve onu koruyan private keyin (bu örnekteki DMK) yedeğinin felaket durumunda veya başka bir servera taşınma durumunda kullanılmak üzere alınması gerekir. Bu konuya birazdan döneceğiz.

Artık yedeklerimizi şifrelenmiş olarak saklayabiliyoruz. Oluşan yedek dosyası üzerine başka yedekler alamıyoruz. Yeni bir media set oluşturmamız ve içeriği ezmemiz gerekir. Bu yedek dosyasında bulunan bir önceki yedeğin silineceği anlamına gelir.

Peki, yedekten nasıl döneceğiz?

Eğer yedek alırken kullandığınız DMK ve certificatenın bulunduğu serverda iseniz yedekten dönmek için normalden farklı bir şey yapmanıza gerek yok. En basit haliyle aşağıdaki scripti kullanarak veritabanını yedekten döndürebilirsiniz.

USE [master]

RESTORE DATABASE [veritabani]

FROM  DISK = N'C:\yedeklerim\veritabani_enc.bak'

Eğer veritabanının yedeğini farklı bir serverda açmak istiyorsanız veya şifrelemede kullandığınız certificate silinmiş ise yukarıdaki scriptten daha fazlasını yapmanız gerekecek.

Sertifikaları nasıl koruyup yedekleyebilirim?

Şifrelemede kullandığımız sertifikanın yedeğini güvenli bir ortama almamız gerekir. Yedek alırken WITH PRIVATE KEY ifadesi ile sertifikayı koruyan private key de belirttiğimiz password yardımıyla şifrelenmelidir. Bu ifade kullanılmadan sertifika yedeği alınabilir ancak bu esnada sertifikanın private keyi diske alınmadığından sertifikadaki PrivateKeyEncryptionType özelliği NoKey olarak işaretlenir. Bu da sertifikayı restore işleminde kullanılamayacak hale getirir. Bu konuda çeşitli derinliklerde çalışmalar yapılabilmektedir. Biz amacımızın dışına çıkmadan aşağıdaki şekilde yedekleme yapmamızın doğru olacağını kabul etmiş olalım.

Sertifikaların yedeğini şu script yardımıyla alıyoruz:

--sertifikanın yedeğini alalım

USE master

GO

BACKUP CERTIFICATE YedekSifrelemeCert

TO FILE ='C:\yedeklerim\YedekSifrelemeCert.cer'

WITH PRIVATE KEY

     (

          FILE ='C:\yedeklerim\YedekSifrelemeCert.pvk',

          ENCRYPTION BY PASSWORD=N'pass@word1'

     )

Sertifikayı tekrar(yedekten) oluşturmak için şu komutu kullanmamız gerekecek:

--sertifikayı yedekten dönelim

USE master

GO

CREATE CERTIFICATE YedekSifrelemeCert

FROM FILE = 'C:\yedeklerim\YedekSifrelemeCert.cer'

WITH PRIVATE KEY

     (

          FILE ='C:\yedeklerim\YedekSifrelemeCert.pvk',

          DECRYPTION BY PASSWORD='pass@word1'

     )

DMK silinmiş ise öncesinde mutlaka DMK oluşturulmalıdır. Çünkü bu şekilde yedek aldığımızda sertifikanın, MasterKey olarak işaretlenen PrivateKeyEncryptionType özelliği DMK’yı isteyecektir.

Eğer ihtiyaç duyarsanız, DMK’nın yedeklenmesi ile ilgili bilgilere şu linklerden ulaşabilirsiniz.

http://msdn.microsoft.com/en-us/library/aa337546.aspx

http://msdn.microsoft.com/en-us/library/ms186336.aspx

Eğer Certificate ve DMK’yı kaldırmak isterseniz şu drop komutları işinizi görecektir.

USE master;

GO

--Certificate yı kaldırmak için

DROP CERTIFICATE YedekSifrelemeCert

-- DMK yı kaldırmak için

DROP MASTER KEY

Master veritabanı altında konumlanan sertifikamızın PrivateKeyEncryptionType özelliğini şuradan görüntüleyebilirsiniz:

Sertifikayı kullanılabilir hale getirdiğinizde klasik restore scriptini kullanarak yedeklerinizi istediğiniz serverda açabilirsiniz.

Tabi ki backup encrption özelliği sadece SQL Server 2014’ün desteklediği bir özelliktir. Hatta bu versiyondaki Web ve Express editionlarda backup encription desteklenmemektedir.

Eğer yedeklerin şifrelenmesi ilginizi çektiyse ve SQL Server 2014 öncesi versiyonlarda kullanmak isterseniz Microsoft Download Centerdan indireceğiniz Microsoft SQL Server Backup to Windows Azure Tool’u kullanabilirsiniz. Bu araç yardımıyla SQL Server 2014 yedeklerindeki tüm özellikler SQL Server 2005, 2008, 2008 R2 ve 2012 yedeklerine de uygulanabilir. Bu konuda gereken tüm bilgiye şu yazımdan erişebilirsiniz:

http://abdullahkise.blogspot.com.tr/2014/05/sql-server-2005-2008-2008-r2-ve-2012.html

Yedeklerin şifrelenmesi ilerleyen günlerde bir hayli önem kazanacak bir konu.

Faydalı olması dileğiyle…

Dosya-Database Bazlı Şifreleme (Transparent Data Encryption) - Bölüm 5

Bu bölüme kadar incelediğimiz Column-Level Encryption, kolon veya satır bazında metinsel alanları şifrelemek için kullanılmaktaydı. Hem yazma hem de okuma işleminde encryp-decrypt yapılması zorunlu olan bu yöntem, şifrelenecek alanlar çoğaldıkça kaynaklara olduğu gibi programlama tarafına da ciddi yük getirmektedir. Bu konudaki bir önceki yazımıza şu adresten ulaşabilirsiniz.

http://abdullahkise.blogspot.com.tr/2013/09/kolon-satr-bazl-sifreleme-column-level.html

Eğer şifrelenecek alanlar çok fazlaysa ve istediğimiz şey veri tabanının şifreleme yaptığımız server haricinde çalıştırılamaması ise TDE(Transparent Data Encryption) yöntemini tercih etmemiz daha doğru olacaktır.

TDE yöntemiyle, diskteki pagelere encyrpt edilerek yazılan veriler, memoryde decrypt edilmiş şekilde tutulurlar. Bu durum verinin boyutunda bir değişikliğe sebep olmazken CPU kullanımını (%5-%10 civarı ) ve index bozulma hızını arttırmaktadır. Eğer keyler veya sertifikalar daha sonra kullanılmak üzere yedeklenmemişse, server seviyesinde gerçekleşen bir felaket durumundan sonra verilere erişilemeyecektir.

Transparent Data Encryption yapabilmek için aşağıdaki resimde olduğu gibi bir takım key ve sertifika oluşturmak gerekmektedir. Biz bu resimdeki numaralarla belirttiğim gereksinimleri yerine getiriyor olacağız. Diğerleri zaten işletim sistemi ve SQL Server kurulumu sonrasında hazır hale gelmiş durumda. İlk iki adım hakkında bilgi edinmek isterseniz şu yazıma bir göz atabilirsiniz.

http://abdullahkise.blogspot.com.tr/2013/05/sifrelemeencryption-esaslar-bolum-1.html

Bu konuda ayrıntılı bilgi için msdn’den de faydalanabilirsiniz.

http://msdn.microsoft.com/en-us/library/bb934049.aspx

Şimdi bu işlemleri bir örnek üzerinde görelim. Öncelikle TDE yöntemiyle şifreleyeceğimiz bir veri tabanı oluşturalım.

CREATE DATABASE TDE_DB

TDE encryption için master veri tabanında gerekli altyapıyı hazırlayalım. master veri tabanı üzerinde TDE için kullanılacak “Database Master Key"i oluşturalım. “Service Master Key” bu keyi korumaktadır.

USE master

GO

CREATE MASTER KEY

ENCRYPTION BY PASSWORD=N'pass@word1'

Oluşturduğumuz bu DMK ile korunan bir de server “Certificate” oluşturalım.

CREATE CERTIFICATE tde_Cert

WITH SUBJECT='TDE serfikam'

Şifrelemek istediğimiz veri tabanını kullanıma alarak, TDE işlemi için gerekli olan “Data Encrption Key”i üreteceğiz.

DEK için 4 şifreleme algoritması( AES_128, AES_192, AES_256, TRIPLE_DES_3KEY) ile birlikte bu keyi koruyacak server Certificate veya server Asymmetric Key belirtilmelidir. Bu konudaki ayrıntılı bilgiye şu linkten ulaşabilirsiniz.

http://msdn.microsoft.com/en-us/library/bb677241.aspx

Bu aşamadan sonra TDE’yi aktif etmek için TDE_DB/Task/Manage Database Encryption… ara yüzünden de faydalanabilirsiniz.

Komutlarla devam edelim:

USE TDE_DB

GO

TDE işleminde kullanılacak "Database Encryption Key" oluşturalım. Bu key yukarıdaki tde_cert tarafından korunacaktır. İstenirse server “Certificate” yerine server “Asymmetric Key” de kullanılabilir.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE tde_Cert

Bu işlemden sonra çıkan uyarıyı göz ardı etmeyip Certificate ve Private Keyin yedeğini almalıyız. Aksi halde bir felaket durumunda veri tabanını tekrar açıp kullanamayabiliriz.

USE master

GO

BACKUP CERTIFICATE tde_Cert

TO FILE ='C:\TDEBackups\tde_Cert.cer'

WITH PRIVATE KEY

     (

          FILE ='C:\TDEBackups\tde_Cert.pvk',

          ENCRYPTION BY PASSWORD=N'pass@word1'

     )

Daha sonra bu sertifika şu şekilde yedekten tekrar üretilebilir.

USE master

GO

CREATE CERTIFICATE tde_Cert

FROM FILE = 'C:\TDEBackups\tde_Cert.cer'

WITH PRIVATE KEY

     (

          FILE ='C:\TDEBackups\tde_Cert.pvk',

          DECRYPTION BY PASSWORD='pass@word1' 

     )

NOT: Bu sertifika farklı bir serverda üretilecekse önce master databaseinde DMK(Database Master Key) oluşturulmalıdır.

Bundan sonra TDE_DB isimli veri tabanımızda Transparent Data Encrytion’ı şu şekilde aktif edebiliriz.

USE TDE_DB

Go

ALTER DATABASE TDE_DB

SET ENCRYPTION ON

Artık veri tabanımız yeni bir servera taşındığında okunamayacak, okunabilmesi için yedeğini aldığımız sertifikaya ihtiyaç duyacaktır.

Yapılan tüm işlemleri geri almak istersek şu sırada keyleri ve certificatei kaldırabiliriz.

Önce TDE’yi pasif hale getirelim.

USE TDE_DB

Go

ALTER DATABASE TDE_DB

SET ENCRYPTION OFF

Database Encrption Key’i kaldıralım.

DROP DATABASE ENCRYPTION KEY

Server Certificate’i kaldıralım.

USE master

GO

DROP CERTIFICATE tde_Cert

Master databaseindeki Database Master Key’i kaldıralım.

DROP MASTER KEY

Bu bölümde TDE yöntemini kullanarak veri tabanını tamamen şifrelemiş olduk. Şifreleme yaptığımız serverda sorgu sonuçlarını decrypt edilmiş olarak göreceğiz. Ancak veri tabanımız yeni bir servera taşındığında, şifrelemede kullandığımız Server Certificate olmadan kullanılamayacaktır.

mdf ve ldf dosyalarını Windows Azure Storage gibi dış bir lokasyonda güvende tutmak istediğimizde TDE şifreleme yöntemi tercih edilebilir. Yine benzer şekilde veri tabanı yedeklerinin farklı bir serverda açılıp kullanılamamasını istiyorsak bu yöntem işimizi görecektir.

SQL Server 2014 ile birlikte gelen Azure Storagelere yedek almak amaçlı geliştirilen URL Backup özelliği aynı zamanda yedeklerin şifrelenmesi opsiyonu olan Backup Encrytion’ı da sağlamaktadır. Bu sebeple Azure için sırf yedekleri koruma amaçlı TDE encrption yapılmasına ihtiyaç kalmamaktadır.

Şifreleme yapılan serverda verilerin encrypt edilmiş halini görmek ve yönetmek istiyorsak önceki yazılarımızda incelediğimiz Column-Level Encryption yöntemini kullanmalıyız.

Son olarak TDE ve Column-Level Encryption yöntemlerinden birine karar vermenizde Mustafa Acungil hocamın şu yazısı yardımcı olabilir.

http://www.sqlserveronculeri.com/1/Article/183/encryption(sifreleme)--kolon-bazli-mi-dosya-bazli-mi.aspx

Encryption konusunda değinmek istediğim genel ve temel yaklaşımlar şimdilik bu kadar.

Faydalı olması dileğiyle…

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

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

Şifreleme konusunun devamı olan Kolon-Satır Bazlı Şifreleme (Column Level Encrypyion) - Bölüm 3  isimli yazımı sqlserveronculeri.com adresinden de inceleyebilirsiniz.

Bir önceki Kolon-SatırBazlı Şifreleme (Column Level Encrypyion) - Bölüm 2 isimli yazımda kolon veya satır bazlı şifreleme (Column-Level Encryption) kapsamında kullanılan 4 mekanizmadan ilki olan T-SQL Functions yöntemini incelemiştik. Bu yazımızda aynı kapsamdaki diğer mekanizmalara Asymmetric Keys, Symmetric Keys ve Certificates yöntemlerine odaklanıyor olacağız.

2-      Asymmetric Keys

Asymetric Keys public ve private olarak adlandırılan iki key içermektedir. Public key network üzerinden paylaşılabilirken private key kimse ile paylaşılmaz. Public key alıcılara gönderilmeden önce verileri şifreler. Private key ise bu mesajın şifresini çözer. Asymmetric Keys, birazdan değineceğimiz Symetric Keys güvenliği için de kullanılabilmektedir.

Aşağıdaki resimde Party #1 ile ifade edilen kişi public keyi kullanarak encrypt ettiği veriyi Party  #2 ile ifade edilen kişi sahip olduğu private keyi kullanarak decrypt etmektedir.

Asymmetric key ile symmetric keyin karşılaştırılmasının yapıldığı şu makaleye bir göz atmak isteyebilirsiniz.

http://www.mssqltips.com/sqlservertip/1886/sql-server-encryption-symmetric-vs-asymmetric-keys/

Asymmetric keys aşağıdaki ifade ile oluşturulabilir. CREATE işlemi için 3 çeşit şifreleme algoritması kullanılabilmektedir(RSA_512, RSA_1024, RSA_2048). Algoritma yerine FROM ifadesi ile keylerin import edilebileceği bir kaynak belirtilebilir(File,Executable,Assembly). Bu konudaki detaylı bilgiye http://technet.microsoft.com/en-us/library/ms174430.aspx linkinden ulaşabilirsiniz.

CREATE ASYMMETRIC KEY asymmAnahtarim

WITH ALGORITHM=RSA_1024

ENCRYPTION BY PASSWORD='pass@word1'

İfadedeki son ENCRYPTION BY PASSWORD satırı private keyin password ile encrypt edilmesi için kullanılmaktadır. Eğer belirtilmezse varsayılan olarak Database Master Key kullanılmaktadır. Burada belirtilen şifre maksimum 128 karakterdir ve SQL’in kurulu olduğu makinedeki Windows Password Policy gereksinimlerine uygun olmalıdır(büyük,küçük harfler,karakterler vs.).

Oluşturduğumuz bu asymmetric keyi üzerinde çalıştığımız veri tabanındaki Security/Asymmetric Keys bölümünde görebiliriz.

Oluşturduğumuz bu asymmetric key sayesinde ENCRYPTBYASYMKEY fonksiyonu ile verileri şifreleyebilir DECRYPTBYASYMKEY fonksiyonu ile şifreli metni çözebiliriz. Bu fonksiyonlar encytp etmek için 2, decrypt etmek için 3 parametre ile çalışmaktadır. Encrypt işleminde ilk parametreye asymmetric keyin idsi ikinci parametreye de encrypt edilecek temiz metin verilmektedir. Decrypt işleminde ise son parametreye asymmetric key oluşturulurken belirtilen password verilmektedir.

Fonksiyonda kullanılan parametreler kısaca açıklayalım:

Key_ID :

int tipinden asymmetric keyin idsidir. ASYMKEY_ID() fonksiyonu ile bu id elde edilebilir.

Plain_Text:

               Encrypt edilecek veridir. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Ciphertext:

Asymmetric key ile encrypt edilmiş veridir.

Asym_Key_Password:

               Asymmetric key oluşturulurken kullanılan password dur.

Bir önceki bölümde yaptığımız gibi burada da değişkenler üzerinden her iki fonksiyonu da kullanarak metni encrypt ve decrypt edelim

3-      Symmetric Keys

SQL Server 2012 üzerinde en hızlı encryption metotlarından birisidir. Verilerin encrypt edilmesi ve decrypt edilmesi için tek bir key kullanılır. Çalışma şeklini aşağıdaki gibi resmedebiliriz.

Yukarıda da belirttiğim gibi asymmetric key ile symmetric keyin karşılaştırılmasının yapıldığı şu makaleye bir göz atmak isteyebilirsiniz.

http://www.mssqltips.com/sqlservertip/1886/sql-server-encryption-symmetric-vs-asymmetric-keys/

Symmetric key mekanizmasının da korunması gerekmektedir. Bunun için Password, Certificate, Asymmetric Key veya başka bir Symmetric Key kullanılabilir. Symmetric keyin encrypt edilmesi için kullanılabilecek ifadeleri yorum satırı olarak aşağıda belirttim.

Aytıntılı bilgilere msdn’den erişebilmek için şu linki kullanabilirsiniz.

http://technet.microsoft.com/en-us/library/ms188357.aspx

CREATE SYMMETRIC KEY symAnahtarim

WITH ALGORITHM = TRIPLE_DES

--ENCRYPTION BY ASYMMETRIC KEY asymmAnahtarim

--ENCRYPTION BY SYMMETRIC KEY symAnahtarim0 ---(önce aktif edilmeli)

--ENCRYPTION BY CERTIFICATE sertifikam

ENCRYPTION BY PASSWORD='pass@word1'

Symmetric key oluştururken çeşitli encryption algoritmaları kullanılabilir. Symmetric keyler kullanılmadan önce mutlaka OPEN SYMMETRIC KEY ifadesi ile aktif edilmelidir. Session sonlandığında bu symmetric key kapanacaktır. İstenirse CLOSE SYMMETRIC KEY ifadesi kullanılarak session bitmeden de symmetric key kapatılabilir.

Verilerin encrypt edilmesi için kullanılan ENCRYPTBYKEY fonksiyonu minimum 2 parametre decrypt edilmesi için kullanılan DECRYPTBYKEY fonksiyonu minimum 1 parametre ile çalıştırılabilmektedir.

Parametreleri aşağıdaki gibi özetleyebiliriz:

Key_guid:

               Symmetric keyin uniqueidentifier tipinden GUID çıktısı. KEY_GUID() fonksiyonu ile elde edilebilir.

Clear_text:

               Encrypt edilecek veridir. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Ciphertext:

Symmetric key ile encrypt edilmiş veridir.

Benzer bir çalışma ile bu yöntemi örneklendirelim.

4-      Certificates

Certificates dijital olarak imzalanmış güvenlik nesneleridir. İnsanların, cihazların veya organizasyonların tanınması için haklarında çeşitli bilgiler ve private key ile ilişkilendirilecek public key içerir. Bu sayede endpointlere güvenli bağlantı(secure connection) ile erişim sağlanabilmektedir. Database Mirroring’te, veri ve connection şifrelemede veya paket gibi nesnelerin imza altına alınmasında kullanılabilir.

Certificate aşağıdaki ifade ile oluşturulabilir.

CREATE CERTIFICATE Sertifikam

ENCRYPTION BY PASSWORD ='pass@word1'

WITH

     SUBJECT='Sertifika konusu',

          START_DATE='01/01/2013',

          EXPIRY_DATE='01/01/2020'

Burada kullanılan ENCRYPTION BY PASSWORD ifadesi private keyi encrypt etmek için kullanılmaktadır. Belirtilmezse varsayılan olarak Database Master Key geçerli olmaktadır. Burada belirtilen şifre SQL’in kurulu olduğu makinedeki Windows Password Policy gereksinimlerine uygun olmalıdır(büyük,küçük harfler,karakterler vs.).  İfadeden de anlaşılacağı üzere bir tarih aralığı belirtilerek sertifikanın geçerlilik süresi belirlenebilir. Ayrıntılı bilgi için msdn’e göz atmak isteyebilirsiniz.

http://msdn.microsoft.com/en-us/library/ms187798.aspx

Oluşturduğumuz bu Certificate üzerinde çalıştığımız veri tabanının Security/Certificates bölümünde yer almaktadır.

Veri şifrelemek için kullanabileceğimiz ENCRYPTBYCERT fonksiyonu 2 parametre DECRYPTBYCERT fonksiyonu ise 3 parametre almaktadır. Bu parametreleri şu şekilde açıklayabiliriz:

Certificate_id:

               Sertifikaya ait int tipinden bir iddir. Bu değer Cert_ID() fonksiyonu ile elde edilebilir.

Clear_text:

               Encrypt edilecek veri. Bu veri sadece nvarchar, char, varchar, binary, varbinary veya nchar tiplerinden birisi olabilir.

Cipher_text:

               Certificates ile encrypt edilmiş verdir.

Cert_password:

               Sertifika oluşturulurken belirtilen password.

Öncekilere benzer bir çalışma ile bu yöntemi şöyle örneklendirebiliriz.

Asymmetric key ve Symmetric keyi SQL Server dışında tutmaya yarayan ve sadece Enterprise editionda kullanılabilir olan Extensible Key Management(EKM) modülünü bilinçli olarak kapsam dışında bıraktım. SSMS üzerinden "Server/Facet/Sql Server Config" yoluyla aktif edilen EKM, encryption key üretmek ve tutmak için kullanılmaktadır.

Yukarıda oluşturduğumuz nesneler ilgili veri tabanı üzerinde çalıştırılan DROP komutu ile serverdan kaldırılabilir.

DROP SYMMETRIC KEY ad

     DROP ASYMMETRIC KEY ad

     DROP CERTIFICATE ad

Bu bölümde Column-Level Encryption kapsamındaki 4 mekanizmadan geriye kalan 3’ünün neler olduğuna ve nasıl kullanıldığına bir göz attık.

Bir sonraki bölümde bu mekanizmaları karma bir şekilde kullanarak, bir tablodaki metin bazlı kolonları şifreleyip çözeceğiz. Böylece sıfırdan alarak gerçek hayatta pratiği olan bir örnek yapmış olacağız.

Faydalı olması dileğiyle,