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. Kolonu şifreli hale getirmek için öncelikle ilgili veritabanında aşağıdaki script yardımıyla master key oluşturmalısınız. Örneğimizde test amaçlı olduğu için basit bir şifre verdik. Production ortama uygularken daha karmaşık bir şifre vermelisiniz.

USE AdventureWorks2014
GO
CREATE MASTER KEY ENCRYPTION BY  
    PASSWORD = 'CLE123' 
GO 

Daha sonra aşağıdaki gibi bir sertifika oluşturuyoruz.

USE AdventureWorks2014
GO
CREATE CERTIFICATE KrediKartiSertifikasi 
   WITH SUBJECT = 'Kredi Kartı Numaraları'; 
GO 

Daha sonra aşağıdaki gibi  Symmetric Key’imizi oluşturuyoruz.

USE AdventureWorks2014
GO
CREATE SYMMETRIC KEY KrediKartKey 
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY CERTIFICATE KrediKartiSertifikasi;

Aşağıdaki gibi şifrelenecek tablomuzu oluşturalım. Bu tabloda KrediKartNo ve KrediKartSifre kolonlarını şifreleyeceğiz. Bu yüzden veri tipi olarak varbinary olarak set ettik. Bunun sebebi veriyi şifrelemek için kullanacağımız ENCRYPTBYKEY fonksiyonunun return type’ının varbinary olması.

USE AdventureWorks2014
GO
CREATE TABLE dbo.KrediKartlari
(
ID int NOT NULL IDENTITY (1, 1),
Ad varchar(200) NOT NULL,
Soyad varchar(200) NOT NULL,
KrediKartNo varbinary(128) NOT NULL
)  ON [PRIMARY]
GO

Şimdi bu tablomuza veri girişi yapmaya çalışalım. Aşağıdaki script’i kullanırsanız tanımlamış olduğunuz symmetric key’i açmadığınız için hata verecektir.

DECLARE @Anahtar AS UNIQUEIDENTIFIER
SET @Anahtar = Key_GUID('KrediKartKey')
IF( @Anahtar is not null )
BEGIN
INSERT INTO [dbo].[KrediKartlari]([Ad],[Soyad],[KrediKartNo]) VALUES ( 'Nurullah','ÇAKIR', encryptbykey( @Anahtar, N'1111-1111-1111-1111'))
INSERT INTO [dbo].[KrediKartlari]([Ad],[Soyad],[KrediKartNo]) VALUES ( 'Faruk','ERDEM', encryptbykey( @Anahtar, N'2222-2222-2222-2222'))
INSERT INTO [dbo].[KrediKartlari]([Ad],[Soyad],[KrediKartNo]) VALUES ( 'Dilara','AYDIN', encryptbykey( @Anahtar, N'3333-3333-3333-3333'))
END
ELSE
BEGIN
PRINT 'Symmetric Key Bulunamadı..'
END

Verdiği hata mesajı şu şekilde olacaktır:

Msg 515, Level 16, State 2, Line 21

Cannot insert the value NULL into column ‘KrediKartNo’, table ‘AdventureWorks2014.dbo.KrediKartlari’; column does not allow nulls. INSERT fails.

Sorgunun başına aşağıdaki script’i :

OPEN SYMMETRIC KEY KrediKartKey  
   DECRYPTION BY CERTIFICATE KrediKartiSertifikasi  ; 

Sorgunun sonuna da aşağıdaki script’i koymalısınız:

CLOSE SYMMETRIC KEY KrediKartKey;

Bu işlemlerin sonucunda kayıtlarımızı eklemiş olduk. Tabloya aşağıdaki gibi select çektiğimizde verilerimizin şifrelenmiş şekilde geldiğini göreceksiniz.

Peki bu kolonların şifrelenmemiş hallerine ulaşmak istediğimizde nasıl ulaşacağız?

Aşağıdaki script yardımıyla encrypt edilmiş datayı decrypt ederek açabiliriz.

OPEN SYMMETRIC KEY KrediKartKey 
   DECRYPTION BY CERTIFICATE KrediKartiSertifikasi  ; 
SELECT ID,Ad,Soyad, CONVERT(NVARCHAR, DecryptByKey(KrediKartNo)) as 'Kredi Kartı Numarası'
  FROM [AdventureWorks2014].[dbo].[KrediKartlari]
CLOSE SYMMETRIC KEY KrediKartKey;

Aşağıda da göründüğü üzere verilerimiz orijinal halleri ile gelecektir.