作者:陳淑萍 / 臺灣大學計算機及資訊網路中心程式設計組幹事
MS SQL Server從2005開始提供使用對稱式金鑰、非對稱式金鑰或憑證來加密及解密資料的功能,而且可在內部的憑證存放區內管理上述所有項目。此存放區會使用加密階層,藉由階層中的上層層級來確保下層層級的憑證及金鑰。MS SQL Server 的這個功能區也稱為「秘密儲存區」(Secret Storage)。對稱式金鑰加密是此加密功能所支援快加密的模式。此模式適合用來處理大量的資料。而對稱式金鑰可以藉由憑證、密碼或其他對稱金鑰進行加密。
前言
MS-SQL 2008提供了多種資料加解密的方式,以下將針對較為常用的三種加密方式:對稱金鑰(Symmetric Key)、非對稱金鑰(Asymmetric Key)及憑證(Certificate),逐一講解與示範如何使用用來對資料欄位進行加解密。
對稱金鑰
針對欄位進行加密後存入,其使用方式是在於 Insert / Update 時,下面就示範當在Update 時,對於要加密的欄位該如何進行。
特別要注意的是,使用 Symmetric key 時,必須要先OPEN KEY,用完之後在CLOSE KEY。
OPEN SYMMETRIC KEY sKey DECRYPTION BY CERTIFICATE sCert
-- ..T-SQL .......
CLOSE ALL SYMMETRIC KEYS
---create table
CREATE TABLE symmetricKey(id INT,name VARCHAR(10),pwd varchar(10))
INSERT INTO symmetricKey values(1,'testuser1','987')
SELECT * FROM symmetricKey
![](001/Upload/354/ckfile/b3796963-e9d0-45ab-ae12-29ff9ec3e198.jpg)
--create symmetric key
CREATE SYMMETRIC KEY SymKey
WITH ALGORITHM =TRIPLE_DES --演算法有DES,DES,AES,RC等~~~
ENCRYPTION BY PASSWORD ='P@ssw0rd'
--新增欄位encry_pwd
ALTER TABLE symmetricKey ADD encry_pwd varbinary(2048)
---symmetric key 使用前要先open key,為symmetric key解密,讓它能夠使用
OPEN SYMMETRIC KEY SymKey DECRYPTION BY PASSWORD ='P@ssw0rd'
UPDATE dbo.symmetricKey
SET encry_pwd= EncryptByKey(Key_GUID('SymKey'),cast(pwd as varchar(20)))
CLOSE SYMMETRIC KEY SymKey
--新增欄位encry_pwd存放了針對欄位pwd加密後產生的資料
SELECT * FROM symmetricKey
![](001/Upload/354/ckfile/9def4da3-9568-4a11-a81c-bda98c273bca.jpg)
--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE symmetricKey DROP COLUMN pwd
EXECUTE sp_rename 'dbo.symmetricKey.encry_pwd','pwd','COLUMN'
--結果
![](001/Upload/354/ckfile/4f49a320-1126-4f80-9f6c-b7ae2826948e.jpg)
非對稱金鑰Asymmetric Key
--create table
CREATE TABLE AsymmetricKey( id INT, name VARCHAR(10), pwd varchar(10))
INSERT INTO AsymmetricKey values(2,'AKTEST','555')
SELECT * FROM AsymmetricKey
![](001/Upload/354/ckfile/90bc29c0-91d6-47cd-b6c3-93684a586587.jpg)
--create asymmetric key
CREATE ASYMMETRIC KEY AsymKey
WITH ALGORITHM = RSA_2048 --使用RSA的位元演算法
ENCRYPTION BY PASSWORD = 'P@ssw0rd';
--新增欄位encry_pwd
ALTER TABLE AsymmetricKey add encry_pwd varbinary(2048)
--新增欄位encry_pwd存放了針對欄位pwd加密後產生的資料
UPDATE AsymmetricKey
SET encry_pwd = EncryptByAsymKey(AsymKey_ID('AsymKey'),cast(pwd as varchar(20)))
![](001/Upload/354/ckfile/f6775220-c762-4da4-80eb-3d3989b86ddc.jpg)
--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE AsymmetricKey drop column pwd
EXECUTE sp_rename 'dbo.AsymmetricKey.encry_pwd','pwd','COLUMN'
![](001/Upload/354/ckfile/08574d39-a2d9-4ec1-bfaf-abb238bdd00c.jpg)
--結果
![](001/Upload/354/ckfile/e91d804f-f9a3-4642-84e7-966013f95045.jpg)
憑證Certificate
--create table
CREATE TABLE CertificateKey( id INT,name VARCHAR(10), pwd varchar(10))
INSERT INTO CertificateKey values(3,'CKTEST','852')
SELECT * FROM CertificateKey
![](001/Upload/354/ckfile/15f390a4-f59f-46a5-9e6f-b245fae98cbc.jpg)
--create certificate
CREATE CERTIFICATE CeKey
ENCRYPTION BY PASSWORD = 'P@ssw0rd'
WITH SUBJECT = 'Credit Card Certificate',
START_DATE ='2012/01/01',
EXPIRY_DATE = '2012/06/30';
SELECT * FROM sys.certificates
![](001/Upload/354/ckfile/95f559a1-5a51-4520-ab5d-7342e6b4f5c9.jpg)
--新增欄位encry_pwd
ALTER TABLE CertificateKey add encry_pwd varbinary(2048)
--新增欄位encry_pwd存放了針對欄位pwd加密後產生的資料
UPDATE CertificateKey set encry_pwd = EncryptByCert(Cert_ID('Cekey'),cast(pwd as varchar(20)))
![](001/Upload/354/ckfile/dff820ea-663c-4104-b65b-7d7ab41e046e.jpg)
--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE CertificateKey drop column pwd
EXECUTE sp_rename 'dbo.CertificateKey.encry_pwd','pwd','COLUMN'
![](001/Upload/354/ckfile/ef3f82fe-6f32-4104-b1bc-95dab57085f9.jpg)
--結果
![](001/Upload/354/ckfile/551fc423-e55f-4a97-ad14-d021e7936c1a.jpg)
結語
經過了繁瑣的加密程序,萬一資料庫庫被盜取,有加密過的敏感資料也不至於外洩。但是值得注意的是,一定要落實密碼與憑證的備份與保管,不然一旦密碼遺失,寶貴的資料也無法再復原。