|
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'),'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防止模块过早终止
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
