SQLServer對稱金鑰的調查

今天一邊解bug，一邊調查了一下對稱金鑰，項目剛開始用到的時候，還真是無從下手啊，後來多虧有經驗的同志協助，才知道，這東東叫對稱key。是SQLServer2005用來加密用的啊，整理個小例子便於以後使用時恢複記憶，o(∩_∩)o...

 

1、建庫：Test 
 USE Test;

2、建master Key: 
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

3、建表：Person 
 CREATE TABLE Person
 (
 ContactID INT PRIMARY KEY,
 FirstName NVARCHAR(200),
 MiddleName NVARCHAR(200),
 LastName NVARCHAR(200),
 eFirstName VARBINARY(200),
 eMiddleName VARBINARY(200),
 eLastName VARBINARY(200),
 );

4、建認證： 
 CREATE CERTIFICATE TestCertificate
 WITH SUBJECT = 'Adventureworks Test Certificate',EXPIRY_DATE = '10/10/2009';

5、建對稱key： 
 CREATE SYMMETRIC KEY TestSymmetricKey
 WITH ALGORITHM = TRIPLE_DES
 ENCRYPTION BY CERTIFICATE TestCertificate;
 OPEN SYMMETRIC KEY TestSymmetricKey
 DECRYPTION BY CERTIFICATE TestCertificate;

6、向表Person中插入加密的資料 
 INSERT
 INTO Person (ContactID, eFirstName, eMiddleName, eLastName)
 values(1,
 EncryptByKey(Key_GUID('TestSymmetricKey'), 'eFirstName'),
 EncryptByKey(Key_GUID('TestSymmetricKey'), 'eMiddleName'),
 EncryptByKey(Key_GUID('TestSymmetricKey'), 'eLastName')
 )

7、解密6中的資料 
 UPDATE Person
 SET FirstName = CONVERT(varchar,DecryptByKey(eFirstName)),
 MiddleName = CONVERT(varchar,DecryptByKey(eMiddleName)),
 LastName = CONVERT(varchar,DecryptByKey(eLastName));

8、查看解密後的結果 
 select Cast(DecryptByKey(eFirstName) as Varchar) FROM Person

9、刪除以上的結構 
 DROP TABLE Person;
 CLOSE SYMMETRIC KEY TestSymmetricKey;
 DROP SYMMETRIC KEY TestSymmetricKey;
 DROP CERTIFICATE TestCertificate;
 DROP MASTER KEY;

PS:EXPIRY_DATE是有效時間，要大於目前時間

     解密時的資料類型要和DB中對應欄位的類型相同，要不就是亂碼哦（千辛萬苦找出來的）