SQL 2005加密資料方法

--Author: liangCK 小樑<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

--示例一,使用證照加密資料.

 

--建立測試資料表

 

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

--建立證照一,該證照使用資料庫主金鑰來加密

CREATE CERTIFICATE Cert_Demo1 

WITH 

  SUBJECT=N'cert1 encryption by database master key',

  START_DATE='<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2008-01-01',

  EXPIRY_DATE='2008-12-31'

GO

 

--建立證照二,該證照使用密碼來加密

CREATE CERTIFICATE Cert_Demo2

  ENCRYPTION BY PASSWORD='liangCK.123'

WITH 

  SUBJECT=N'cert1 encrption by password',

  START_DATE='2008-01-01',

  EXPIRY_DATE='2008-12-31'

GO

 

--此時,兩個證照已經建立完,現在可以用這兩個證照來對資料加密

--在對錶tb做INSERT時,使用ENCRYPTBYCERT加密

 

INSERT tb(data)

  SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'這是證照1加密的內容-liangCK');  --使用證照1加密

 

INSERT tb(data)

  SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'這是證照2加密的內容-liangCK');  --使用證照2加密

 

 

--ok.現在已經對資料加密保證了.現在我們SELECT看看

 

SELECT * FROM tb ;

 

--現在對內容進行解密顯示.

--解密時,使用DECRYPTBYCERT

 

SELECT 證照1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),

       --使用證照2解密時,要指定DECRYPTBYCERT的第三個引數,

       --因為在建立時,指定了ENCRYPTION BY PASSWORD.

       --所以這裡要通過這個密碼來解密.否則解密失敗

       證照2解密

=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))

FROM tb ;

 

--我們可以看到,因為第2條記錄是證照2加密的.所以使用證照1將無法解密.所以返回NULL

 

/*

證照1解密                                              證照2解密

-------------------------------------------------- --------------------------------------------------

這是證照1加密的內容-liangCK                                 NULL

NULL                                               這是證照2加密的內容-liangCK

 

(2 行受影響)

*/

 

GO

 

--刪除測試證照與資料表

DROP CERTIFICATE Cert_Demo1;

DROP CERTIFICATE Cert_Demo2;

DROP TABLE tb;

GO

 

 

--示例二,使用對稱金鑰加密資料,

--對稱金鑰又使用證照來加密.

 

--建立測試資料表tb

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

 

--建立證照,該證照用於加密對稱金鑰.

 

CREATE CERTIFICATE Cert_Demo

  ENCRYPTION BY PASSWORD=N'liangCK.123'

WITH

  SUBJECT=N'cert encryption by password',

  START_DATE='2008-01-01',

  EXPIRY_DATE='2008-12-31'

GO

 

--建立對稱金鑰

 

CREATE SYMMETRIC KEY Sym_Demo

WITH

   ALGORITHM=DES  --使用DES加密演算法

ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo證照加密

GO 

 

--要使用Sym_Demo對稱金鑰.必需使用OPEN SYMMETRIC KEY來開啟它

 

OPEN SYMMETRIC KEY Sym_Demo

   DECRYPTION BY CERTIFICATE Cert_Demo

      WITH PASSWORD=N'liangCK.123'

 

--插入加密資料

 

INSERT tb(data)

  SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'這是加密的資料,能顯示出來嗎?')

  

--關閉金鑰

CLOSE SYMMETRIC KEY Sym_Demo

 

--插入完加密資料,現在使用SELECT來查詢一下資料

 

SELECT * FROM tb 

 

GO

 

--現在來解密此資料

 

--同樣,還是要先開啟對稱金鑰

 

OPEN SYMMETRIC KEY Sym_Demo

   DECRYPTION BY CERTIFICATE Cert_Demo

      WITH PASSWORD=N'liangCK.123'

 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --這裡可見,資料已經解密出來了.

FROM tb 

 

CLOSE SYMMETRIC KEY Sym_Demo

GO

 

--刪除測試

DROP SYMMETRIC KEY Sym_Demo

DROP CERTIFICATE Cert_Demo

DROP TABLE tb

 

 

 

--示例三,還有一種方法加密資料更簡單

--就是使用EncryptByPassPhrase

 

--建立測試資料表tb

 

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

 

INSERT tb(data)

  SELECT EncryptByPassPhrase(N'這是密碼,用來加密的',N'這是要加密的內容');

  

--解密

 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'這是密碼,用來加密的',data))

FROM tb 

 

GO

DROP TABLE tb