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中对应字段的类型相同，要不就是乱码哦（千辛万苦找出来的）