MS SQL 資料庫加密

手機版選單

下方連結

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