跳到主要內容區塊

ntuccepaper2019

技術論壇

MS SQL 資料庫加密
  • 卷期:v0022
  • 出版日期:2012-09-20

作者:陳淑萍 / 臺灣大學計算機及資訊網路中心程式設計組幹事


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

 

--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          

 

--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE symmetricKey DROP COLUMN pwd
EXECUTE sp_rename 'dbo.symmetricKey.encry_pwd','pwd','COLUMN'

 

--結果

 

非對稱金鑰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

--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)))

 

--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE AsymmetricKey drop column pwd
EXECUTE sp_rename 'dbo.AsymmetricKey.encry_pwd','pwd','COLUMN'


--結果


憑證Certificate

--create table
CREATE TABLE CertificateKey( id INT,name VARCHAR(10), pwd varchar(10))
INSERT INTO CertificateKey values(3,'CKTEST','852')
SELECT * FROM CertificateKey

--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

 

--新增欄位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)))

 

--移除存放密碼的欄位pwd,並將存放加密後資料的欄位encry_pwd更名為pwd
ALTER TABLE CertificateKey drop column pwd
EXECUTE sp_rename 'dbo.CertificateKey.encry_pwd','pwd','COLUMN'

 

--結果


結語

經過了繁瑣的加密程序,萬一資料庫庫被盜取,有加密過的敏感資料也不至於外洩。但是值得注意的是,一定要落實密碼與憑證的備份與保管,不然一旦密碼遺失,寶貴的資料也無法再復原。