資料庫的資料加解密

最近常在搞資安。現在要求一些個資的資料需要在資料表內加密，讓一般的人看不懂。

而在 SQL Server 2005 以上，就內建了加解密的功能。

現在，就建立一個測試的資料庫

CREATE DATABASE [Test] ON  PRIMARY 
( NAME = N'Test', FILENAME = N'C:\db\.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'C:\db.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

要加解密之前，需有一些基礎建設，就如同PKI

步驟1

-- 在 database 層級建立 master key
use Test;
GO

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Password1'
GO

步驟2

--建立憑證。會自動使用資料庫層級唯一的 master key
CREATE CERTIFICATE EncryptTestCert
    WITH SUBJECT = 'Test Encrypt'
GO

步驟3

-- 使用憑證建立對稱式金鑰
CREATE SYMMETRIC KEY SymKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE EncryptTestCert
GO

最後，就是要加解密資料了。

加解密資料

USE Test
GO
--開啟對稱式金龠
OPEN SYMMETRIC KEY SymKey
DECRYPTION BY CERTIFICATE EncryptTestCert

declare @bin varbinary(256)

-- 對身份證字號加密
set @bin = ENCRYPTBYKEY(KEY_GUID('SymKey'), N'A123456789') 

-- 解密
select @bin
select Convert(nvarchar, DECRYPTBYKEY(@bin))

程式執行結果如下

0x0087FA222A1EE545A4EA753E7B09299801000000147A6F5EEB331D83A9E23024E78DB424319011020A4558E3D5AE13F8758484E3F9628F7464EA098C

(1 row(s) affected)


------------------------------
A123456789

(1 row(s) affected)

加解密的過程，當然可以改成存取資料表的寫法，不過這並不是加解密的重點，讀者可以自行發揮。

結論

資料表內的資料經過上述的操作，可以進行加解密，但是選擇解密欄位的對象，卻要斤斤計較。例如做 foreign key，where 子句的查詢欄位，因為效能的關係，就不適合加密了。