SQLServer对称密钥

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

 

Ø  如何在保护调用者利益的情况下开打密钥：如果可能，尽量用DecryptByKeyAuto语句代替DecryptByKey语句，使用DecryptByKeyAuto不需要显示地将密钥打开，该语句会自动打开密钥（并在解密后关闭密钥，译者注）

Ø  尽可能减少打开密钥和关闭密钥之间的代码，减少打开密钥却没有关闭密钥的逻辑错误

Ø  确保在代码内进行错误处理，使用TRY/CATCH防止模块过早终止