SQL SERVER ENCRYPTION HIERARCHY (COLUMN LEVEL ENCRYPTION)

Verileriniz yeterince güvende mi?

Tüm veriler kritik olmasa da her ortamda bazı veriler kritik öneme sahiptir. Şifreler, finansal veriler, kimlik verileri, adli içeriğe sahip veriler vs. kısacası her DBA in kendi verisi kritiktir 🙂

Bu yazımızda SQL Serverdaki kriptografi yöntemlerinden birkaçına değineceğiz. Öncelikle hangi amaçla kriptolama yapacağız? Bu amaca göre burada kullanacağımız yöntem farklı olacaktır. Database bazlı olarak, örneğin database backup alınamasın veya alınan backup şifresiz şekilde restore edilemesin istiyorsak Transparent Data Encryption (TDE) yöntemini kullabiliriz (TDE, Server tarafında şifreleme yapar ve veritabanının tamamını şifreler). Tablo ve kolon bazlı olarak, eğer veri sadece kriptolu şekilde tutulsun ve tekrar geri anlamlı hale getirilemesin istiyorsak HASHBYTES yöntemiyle kriptolamamız yeterli olacaktır. Eğer veriler kriptolu tutulsun ve gerektiğinde tekrar SSMS ortamında açılabilsin istiyorsak Column Level Encryption yöntemlerini kullanabiliriz. (Ayrıca bu yöntemlerden biri olan Always Encrypted konusunda bilgi için; https://www.veritabani.org/always-encrypted/). Burada şu soruyu sormalıyız encrypt şifresini bilen herkes kriptoyu çözebilsin mi yoksa sadece yetki verilen kullanıcılar mı çözebilsin.

Şimdi örneklerle bir inceleyelim, üzerinde çalışacağımız bir tablo oluşturalım ve veri atalım. (Kriptolamak istediğimiz kolon “KullaniciSifre” olsun)

USE VERITABANIORG
CREATE TABLE TEST_MT(
       ID [int] IDENTITY (1,1) NOT NULL,
       KullaniciAd varchar(30),
       KullaniciSifre varchar(30)
) ON [PRIMARY]

--Tablomuza veri atalım
insert into TEST_MT (KullaniciAd, KullaniciSifre)
values
('Teoman','bc220209'),
('Metehan','bc209174'),
('Balamir','ce375469'),
('Aksuvar','ce440710'),
('Bumin','ce552745'),
('Bilge','ce744847'),
('Kadir','ce8401212'),
('Alptekin','ce9621183'),
('Tugrul','ce10401157'),
('Timur','ce13681501'),
('Atilla','ce395453'),
('Fatih','145314531453')

Tablomuza bakalım;

1)

Hashbytes yöntemi örnekleriyle başlayalım.

Hashbytes varbinary tipinde output verir. Hashbytes şifreleme algoritmaları (SQL Server 2012 den sonra) 128 bit (16 byte) boyutundaki MD2, MD4, ve MD5, 160 bit (20 byte) boyutundaki SHA ve SHA1, 256 bit (32 byte) boyutundaki SHA2_256, 512 bit (64 byte) boyutundaki SHA2_512 olarak sıralanabilir. Yaygın olarak kullanılan SHA2_256 ya örnek verelim;

Örneklere başlamadan önce daha anlaşılır olması için mevcut KullaniciSifre kolonunu etkilemeden tablomuza kriptolu veri için Varbinary(32) tipinde yeni bir kolon oluşturalım.

ALTER TABLE VERITABANIORG..TEST_MT
ADD KullaniciSifre_HASHBYTES varbinary(32) --SHA2_256 32byte boyutunda olduğundan.

Oluşturduğumuz kolona KullaniciSifre kolonunu Hashbytes yöntemiyle kriptolayarak atalım.

update VERITABANIORG..TEST_MT
set KullaniciSifre_HASHBYTES= HASHBYTES('SHA2_256', KullaniciSifre)

 

Burada dikkat edilmesi gereken konu bu kolondaki kriptolu verinin SSMS ortamında önceki haline çevrilememesidir.

2)

Yukarı bahsettiğimiz gibi eğer veriler kriptolu tutulsun ve gerektiğinde tekrar SSMS ortamında açılabilsin istiyorsak kullanılacak yöntemlerden biri olan EncryptByPassPhrase / DecryptByPassPhrase’ den bahsedelim.

Veri tipi nvarchar, char, varchar, binary, varbinary veya nchar tipinde ve maksimum 8000 byte boyutunda olmalıdır. Output da maksisum 8000 byte boyutunda ve varbinary veri tipinde verir. (Veri tipleri ile ilgili bilgi için; https://www.veritabani.org/sql-server-string-fonksiyonlari/ )

Varbinary(8000) tipinde bir kolon oluşturarak örneğimize başlayalım.

ALTER TABLE VERITABANIORG..TEST_MT
ADD KullaniciSifre_Encrypt VARBINARY(8000); --MAX 8000 BYTE veri encrypt olabilir

Kriptolama algoritmasında kullanılmak üzere bir PAROLA belirleyelim, ardından oluşturduğumuz yeni kolona KullaniciSifre kolonunun kriptolu halini basalım.

DECLARE @parola VARCHAR(30); 
SET @parola = 'ayşe tatile çıksın'; 

UPDATE VERITABANIORG..TEST_MT 
SET  KullaniciSifre_Encrypt = EncryptByPassPhrase(@parola , KullaniciSifre)

Görüldüğü üzere KullaniciSifre kolonundaki veriler KullaniciSifre_Encryp kolonunda Varbinary tipinde kriptolanmış şekilde bulunuyor ve tabloyu okuma yetkisi olan herkes bu kolonu Varbinary kriptolu şekilde görecektir.

Şimdi bu kriptolu verinin orijinal halini görmek için DecrypByPassPhrase fonksiyonunun nasıl kullanıldığına bakalım.

Encryp için belirlediğimiz PAROLA yı kullanarak Decrypt yapıyoruz (Symmetric).

DECLARE @parola VARCHAR(30); 
SET @parola = 'ayşe tatile çıksın'; 

select * ,
convert(varchar,decryptByPassPhrase(@parola , KullaniciSifre_Encrypt)) as KullaniciSifre_Decrypt
from VERITABANIORG..TEST_MT

Decrypt yaptığımızda veri tipi Varbinary olduğundan Varchar tipine çevirdiğimizde verinin orijinal halini görmekteyiz. EncryptByPassPhrase / DecryptByPassPhrase yöntemiyle yapılan kriptolama işlemlerinin dezavantajı; bu yöntemle yapılan kriptolamada yetkilendirme mekanizması olmadığından tabloyu okuma yetkisi olan ve kriptolama algoritmasındaki PAROLA’yı bilen herkes bu verilere ulaşabilecektir.

Yeni bir kullanıcı oluşturalım (kullanıcı oluşturma konusunda detaylı bilgi için; veritabani.org/sql-server-create-new-user/) ve bu kullanıcı ile SSMS girişi yaparak şifreyi bildiğimiz takdirde kriptolu veriyi açıp açamayacağımıza bakalım.

“kullanici” adında bir kullanıcı oluşturalım;

CREATE
LOGIN [kullanici] with PASSWORD=N'kullanici1!2.'

oluşturduğumuz kullanıcıya “datareader” yetkisi verelim;

USE [VERITABANIORG]
GO
CREATE USER [kullanici] FOR LOGIN [kullanici]
GO
USE [VERITABANIORG]
GO
ALTER ROLE [db_datareader] ADD MEMBER [kullanici]
GO

Decrypt scriptini bu kullanıcıda çalıştırıp bakalım.

Resimde görüldüğü üzere “kullanici” isimli kullanıcı ile şifre bilindiği takdirde kriptolu veriler decrypt edilebilmektedir.

3)

Peki nasıl bir yöntem kullanmamız gerekiyor? Öncelikle yetki mekanızmasına sahip bir yöntem olmalı ve olumsuz bir durumda başka bir ortamda kullanabilmek için backup ve restore yapılabilir olmalı.

SQL Server’ in kritografi hiyerarşisi; SQL Server, verileri hiyerarşik bir şifreleme ve anahtar yönetimi altyapısı ile şifreler. Her katman, sertifikaların (Certificates), asimetrik anahtarların (Asymmetric Key) ve simetrik anahtarların (Symmetric Key) bir kombinasyonunu kullanarak altındaki katmanı şifreler.

Aşağıdaki çizimde SQL Server’in şifreleme hiyerarşisini ve her katmanın altındaki katmanı şifrelediğini görmekteyiz. Hiyerarşinin başlangıcına erişim genellikle bir parola gerektirmektedir.

Burada kullanacağımız Symmetric/Asymmetric Key’ ler Certificate tarafından üretilir, Certificate’ ler Database Master Key’ e ihtiyaç duyar,  Database Master Key ise Service Master Key tarafından korunur. Service Master Key ise SQL Server Setup tarafından oluşturulur ve Windows Data Protection API’si (DPAPI) ile şifrelenir.

SQL Server aşağıdaki mekanizmalar ile şifreleme sağlar;

• T-SQL Functions (EncryptByPassPhrase/DecryptByPassPhrase vb.)
• Asymmetric Keys
• Symmetric Keys
• Certificates
• Transparent Data Encryption

Yukarıda örneğini gösterdiğimiz EncryptByPassPhrase / DecryptByPassPhrase yöntemi de aslında bir Symmetric Key kullanır (aynı algoritma/parola ile hem kriptolama hem dekriptolama yapar).

Şimdi son olarak bahsedeceğimiz Column Level Encryption yöntemi ise yetki mekanızmasına sahiptir ve olumsuz bir durumda başka bir ortamda kullanabilmek için backup ve restore imkanı sunar.

Öncelikle Service Master Key (SQL Server Setup tarafından oluşturulur), Database Master Key, Certificate ve Symmetric Key veya Asymmetric Key e ihtiyacımız vardır.

Database master key oluşturalım; (bu da bir symmetric key dir)

 CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Parola.,MTuran!'
       --master key i görelim
       select * from sys.symmetric_keys

Bu database master key ile Certificate oluşturacağız fakat işlem yapabilmek için OPEN komutuyla ektif etmemiz gerekiyor;

 OPEN master key decryption by password='Parola.,MTuran!'

 --(önce master key open yapılmalı)
 CREATE CERTIFICATE Certificate_MTuran WITH SUBJECT = 'Certificate_MTuran';
       --certificate i görelim
       select * from sys.certificates

 

Bu Certificate ile Symmetric Key oluşturacağız;

    --Symmetric Key Oluştur (Certificate den sonra)
 CREATE SYMMETRIC KEY Symmetric_MTuran WITH
 ALGORITHM = AES_256,
 IDENTITY_VALUE='Symmetric_MTuran',
 KEY_SOURCE='E78BBC00-139C-4370-9FC5-0A4ED1451451' --MasterKey'in key_guid alanını kullandım
 ENCRYPTION BY CERTIFICATE Certificate_MTuran;
       --symmetrickey i görelim
       select * from sys.symmetric_keys

Bu Symmetric Key ile kriptolama yapacağımız yeni bir kolon oluşturalım;

ALTER TABLE VERITABANIORG..TEST_MT
ADD KullaniciSifre_Encrypt2 VARBINARY(8000) --MAX 8000 BYTE veri encrypt olabilir

Symmetric Key i kullanabilmemiz için OPEN komutuyla aktif etmemiz gerekiyor;

--Symmetric key OPEN (Encrypt etmek için aktif etmemiz gerekiyor)
 OPEN SYMMETRIC KEY Symmetric_MTuran DECRYPTION BY CERTIFICATE Certificate_MTuran

UPDATE VERITABANIORG..TEST_MT 
SET KullaniciSifre_Encrypt2 = ENCRYPTBYKEY(KEY_GUID('Symmetric_MTuran'),KullaniciSifre)

Şimdi “KullaniciSifre_Encrypt2” kriptolu kolonunu çözelim;

--Decrypt
select
 *, convert (varchar(max),DECRYPTBYKEY(KullaniciSifre_Encrypt2)) as KullaniciSifre_Decrypt 
from VERITABANIORG..TEST_MT
--Where convert (varchar(max),DECRYPTBYKEY(KullaniciSifre_Encrypt2)) like '%453%'
/* Decyrpt yapılan alanda LIKE sorguları da çalıştırma imkanı sağlar */

Peki Certificate ve Symmetric Key yetkisi olmayan bir kullanıcı Parolayı bilse bile kriptolu alanı çözebilir mi?

Aşağıdaki tabloda görüldüğü üzere “kullanici” isimli kullanıcı ile Decrypt scriptini çalıştırdığımızda Symmetric Key i aktif hale getiremeyecektir. Bu nedenle kriptolu kolonu NULL olarak görecektir.

“Kullanici” isimli kullanıcıya Certificate ve Symmetric Key için kontrol yetkisi verelim;

--YETKİ
 use [VERITABANIORG]
GO
GRANT CONTROL ON SYMMETRIC KEY::[Symmetric_MTuran] TO [kullanici]
GO
use [VERITABANIORG]
GO
GRANT CONTROL ON CERTIFICATE::[Certificate_MTuran] TO [kullanici]
go

Resimde de görüldüğü üzere “Kullanici” isimli kullanıcıya Certificate ve Symmetric Key için kontrol yetkilerini verdikten sonra Symmetric Key aktif edildi ve kriptolu alan çözülebildi.

Sonuç olarak Master Key, Certificate ve Symmetric Key kullanarak yaptığımız kriptolama işleminde yetkilendirme mekanızması sayesinde sadece belirli kullanıcıların verilere erişimini sağlayabildik.

BACKUP işlemlerini anlattığım yazıyı da okumak isterseniz ; https://www.veritabani.org/sql_server_encryption-2/