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 […]

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/

Benzer Yazılar

SQL SERVER RIGHT-LEFT PARTITION

SQL Server 5 gün önce

SQL Server’da partitioning, büyük veritabanı tablolarını daha yönetilebilir ve performanslı hale getirmek amacıyla kullanılan bir tekniktir. Bu teknik, tablonun verilerini fiziksel olarak değil, mantıksal olarak parçalara ayırır. Veriler, belirli bir partition function ve partition scheme kullanılarak farklı bölümlere yönlendirilir. Partitioning, özellikle büyük veri kümeleriyle çalışan veri tabanlarında sorgu performansını artırır ve veri yönetimini kolaylaştırır. Partition Function ve Partition Scheme nedir? Partition Function: Verilerin hangi kriterlere göre bölüneceğini belirler. Örneğin, bir tarih aralığına göre verileri ayırmak. Partition Scheme: Verilerin hangi filegroup’larda depolanacağını belirler.   Örnek olarak Range LEFT ve Range RIGHT olmak üzere iki ayrı tabloda partition nasıl yapılır sizlere göstereceğim. İlk olarak Range LEFT olan partition yapısından başlayacağım. Öncelikle Veri tabanıma yeni filegroup ve file ekliyorum Şimdi sıra FUNCTION ve SCHEME oluşturmakta ben tablomu yıllık olarak partition yaptım sizler ihtiyaçlarınız doğrultusunda aylık,günlük vs yapabilirsiniz. LEFT partition dediğimiz olay vermiş olduğunuz tarih aralığına eşit bir veri geldiğinde bu veriyi solundaki partition […]

Veritabanı Recovery Pending Durumu ve Düzeltme Seçenekleri

SQL Server 2 hafta önce

İçindekilerVeritabanı Recovery Pending Durumu Nedir?Veritabanı Neden Recover Pending Duruma Düşer?Recovery Pending Durumu Nasıl Çözülür?SonuçKaynaklarVeritabanı Recovery Pending Durumu Nedir? SQL Server’da veritabanları bazı nedenlere bağlı olarak “Recovery Pending” (Kurtarma Bekleme)  moduna geçebilir. Veritabanın düzgün bir şekilde kapatılmaması, eksik veya bozuk log dosyaları, disk depolama sorunları, sistemde yaşanan anormal şekilde çökmeler veya MS SQL Server’daki hatalar bu duruma sebep olabilir. Recovery durumu, aslında veritabanını tekrar kullanılabilir hale getirmek için bir kurtarma işlemi yürüttüğünü ifade eder ve üç aşamadan oluşur; Analysis (Analiz): Transaction log incelemesi yapılması ve işlemlerin tamamlanma (Commit) durumunun kontrol edilmesi, Redo (Yeniden İşleme): Tamamlanmış (Commit) ancak henüz diske yazılamamış olan işlemlerin yeniden işlenmesi, Undo (Geri Alma): Başlamış (Begin) ancak tamamlanmamış (Commit) işlemlerin  geri alınmasıdır.   Veritabanı Neden Recover Pending Duruma Düşer? SQL Server Restart Süreci SQL Server servisi restart edildiğinde üzerinde bulunan tüm veritabanları tutarlılığın sağlanması için otomatik olarak recovery moduna girer ve redo/undo işlemleri sürecince devam eder. Ani Sistem […]

SQL Server DMV ve DMF – 6

SQL Server 2 hafta önce

Bu yazımızda DMV ve DMF Serimizin 6.sına devam edeceğiz. Bir önceki seride Memory’ye ilişkin DMV ve DMF’leri ele almıştık. Bu yazıda Memory konusunda devam edeceğiz. SQL server’da Memory kavramı en önemli kavramlardan biridir. Özellikle tüm transaction işlemlerinin önce Buffer sonra disk üzerinden devam ettiğini düşünürsek buffer’ın oynadığı kritik rolü daha iyi anlayabiliriz. Bu yazıda Memory’nin durumunu ve monitör edilmesine bakacağız. Özellikle Performans sorunlarında memory konusunda sorun yaşandığı durumda nasıl okumak gerektiği önemli rol oynamaktadır. Hangi database’de, hangi tablo’da sorun yaşandığına ilişkin bilgilere bu paylaşım sonrasında görebileceğiz. SQL Server’ın Memory kullanım durumunu incelediğimde; select physical_memory_in_use_kb/1048576.0 AS ‘physical_memory_in_use (GB)’, locked_page_allocations_kb/1048576.0 AS ‘locked_page_allocations (GB)’, virtual_address_space_committed_kb/1048576.0 AS ‘virtual_address_space_committed (GB)’, available_commit_limit_kb/1048576.0 AS ‘available_commit_limit (GB)’, page_fault_count as ‘page_fault_count’ from  sys.dm_os_process_memory; Görsel – 1   Physical_memory_in_use: Kullanımda olan Fiziksel Memory miktarını gösterir. locked_page_allocations: Memory’de lock’lanmış olan Page’lerin miktarını belirtir. virtual_address_space_contained: SQL Server VAS(Virtual Adress Space) için ayrılan miktarı belirtir. available_commit_limit: SQL Server tarafından kullanılabilecek Memory Miktarını gösterir. […]

1 Yorum

Yorum Yaz

Rastgele