MSSQL Server Kolon Seviyesinde Şifreleme – Column Level Encryption

SQL Server’da önemli bir tablonuzdaki önemli bir kolonunuzun içeriğini kimsenin görmesini istemeyebilirsiniz. Böyle bir durumda bu kolonu şifreli hale getirebilirsiniz.

İlk başta bir tablo oluşturalım ve içine bazı verileri atalım daha sonra ilgili sertifikaları kullanarak şifreleme işlemini yapacağız

SQL Server şifreli sütunun veri türü VARBINARY olmalıdır Bunun sebebi veriyi şifrelemek için kullanacağımız ENCRYPTBYKEY fonksiyonunun return type’ının varbinary olması. Bunun için kart no kolonun bu türde tanımlıyorum.

create table encryp(
id int,
ad varchar(100) not null,
soyad varchar(100) not null,
kartno varbinary(max) not null
)

insert into encryp([id], [ad], [soyad], [kartno])values(1,'yunus','yucel',111111111)

insert into encryp([id], [ad], [soyad], [kartno])values (2,'abdullah kadir','yucel',222222222)

insert into encryp([id], [ad], [soyad], [kartno])values(3,'talat','yucel',333333333)

select*from encryp

Tablomuzu oluşturduktan sonra şifreleme işlemi için aşağıdaki adımların uygulanması gerekiyor.

1. Bir master key oluşturulması lazım
2. Bir sertifika oluşturması gerekmektedir.
3. Şifreleme için bir symmetric_key tanımlanmalı
4. İlgili kolunlar şifrelenmeli
5. Sonuç olarak çıktının alınması

İzleyeceğim yol bu şekilde uygulamalı yapmak akılda kalacağı için teori bilgilere takılmadan işlemlerimize başlayalım

1. İlk başta ilgili veritabanı için bir master key oluşturulmalı.

USE columnencry;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Parola23';

Aşağıdaki komut ile bu anahtarı görebiliriz.

SELECT name KeyName, 
    symmetric_key_id , 
    key_length , 
    algorithm_desc 
FROM sys.symmetric_keys;

2. İlgili veritabanında bir sertifika oluşturuyoruz.

USE columnencry;
GO
CREATE CERTIFICATE sertifika WITH SUBJECT = 'benim sertifika';
GO

Aşağıdaki komut ile bu sertifikayı görebiliriz.

SELECT name CertName, 
    certificate_id CertID, 
    pvt_key_encryption_type_desc EncryptType, 
    issuer_name Issuer
FROM sys.certificates;

3. Kolun  şifreleme için bir simetrik key oluşturuyoruz.

CREATE SYMMETRIC KEY simetrik_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE sertifika;

Yukarıdaki işlemlerden sonra tablomuza tekrardan select çektiğimizde verilerimizin hashlenmiş bir şekilde geldiğini görmekteyiz.

OPEN SYMMETRIC KEY simetrik_key
        DECRYPTION BY CERTIFICATE sertifika;

Bu komut ile oluşturmuş olduğumuz simetrik anahtarı açıyoruz ve verilerimizi şifreleme başlıyoruz. Belirtilen şifreleme algoritmasıyla.

UPDATE columnencry.dbo.encryp
        SET kartno = EncryptByKey (Key_GUID('simetrik_key'), kartno)
        FROM columnencry.dbo.encryp;
        GO

Bu güncellemeyle simetri key i kullanarak kartno’daki değerleri bulunduğu kolona şifreli bir şekilde atıyoruz.

Şifrelenmiş verileri tekrar eski haline getirmek içinde  tekrar aşağıdaki kod bloğunu çalıştırıyoruz.

OPEN SYMMETRIC KEY simetrik_key
   DECRYPTION BY CERTIFICATE sertifika;
SELECT id,ad,soyad,kartno,
   CONVERT(varbinary(max), DecryptByKey(kartno)) AS 'şifrelenmeden önceki hali'
            FROM dbo.encryp;

Şimdi başka bir session’da select çekelim tablomuza hangi değer geliyor. Şifrelenmiş şekilde geliyor çünkü verilerimizi artık şifrelenmiş bir şekilde.

Şimdi tabloma bir değer insert edeceğim.

insert into encryp([id], [ad], [soyad], [kartno])values(4,'mehmet','güder',Cast(3333333333 As varbinary(max)))
select*from encryp

Yeni eklemiş olduğum değer varbinary türünde şifrelenmemiş bir şekilde geldi bunu şifreli bir şekilde güncelleme işlemi yapmamız gerekiyor.

Tekrar güncellemeyle verileri şifreli yapıya getirebiliriz.

Aşağıdaki komut ile şifreleme işlemini kapattım.

CLOSE SYMMETRIC KEY simetrik_key;

Tekrar şifresiz hale getirebilmem için ilk başta open symmetrıc key yapılması gerekmektedir.

OPEN SYMMETRIC KEY simetrik_key
        DECRYPTION BY CERTIFICATE sertifika;

Bu makalede bir kolun nasıl şifrelenir nasıl şifrelenme işleminden sonra eski verileri tekrar görmek için nelerin yapılması gerektiğini görmüş olduk. Şunu unutmamak lazım sys admin yetkisine sahip diğer kullanıcı bile verileri şifreli görmektedir. Bir sonraki makalede ise farklı bir kullanıcı şifreli bir şekilde ilgili kolunu görecek sys admin ve db owner’da her zaman şifresiz görmüş olacak .

Başka makalede görüşmek dileğiyle..

“Allah içinizden iman edenlerin ve kendilerine ilim verilenlerin derecelerini yükseltir.” Mücâdele – 11