MS SQL 資料庫加密 作者:陳淑萍 / 臺灣大學計算機及資訊網路中心程式設計組幹事 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' --結果 結語 經過了繁瑣的加密程序,萬一資料庫庫被盜取,有加密過的敏感資料也不至於外洩。但是值得注意的是,一定要落實密碼與憑證的備份與保管,不然一旦密碼遺失,寶貴的資料也無法再復原。
|