|
今天一边解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'),'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中对应字段的类型相同，要不就是乱码哦（千辛万苦找出来的）
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
