SQLServer2008中的代码安全（六）对称密钥加密分享

代码如下:

use DB_Encrypt_Demo
go

— 创建一个用于加密对称密钥的非对称密钥
CREATE ASYMMETRIC KEY symDemoKey –名称
WITH ALGORITHM = RSA_512 –加密算法
ENCRYPTION BY PASSWORD = ‘TestSYM456!’–密码

— 创建一个对称密钥
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey

代码如下:

–查看当前数据库中的非对称密钥
use DB_Encrypt_Demo
go
SELECT name, algorithm_desc FROM sys.symmetric_keys
—-结果返回
/*
name algorithm_desc
sym_Demo TRIPLE_DES
*/

代码如下:

use DB_Encrypt_Demo
go

–先用私钥密码打开对称密钥
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD = ‘TestSYM456!’

–打开之后，先增加密码加密，取代原密钥
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD = ‘newnew!456’
–再删除非对称密钥加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
–完成操作后，关闭对称密钥
CLOSE SYMMETRIC KEY sym_Demo

代码如下:

/****************************3w@live.cn***********************/
USE DB_Encrypt_Demo
GO
–创建测试数据表，用于对称加密
CREATE TABLE dbo.PWDQuestion
(CustomerID int NOT NULL PRIMARY KEY,
PasswordHintQuestion nvarchar(300) NOT NULL,
PasswordHintAnswer varbinary(200) NOT NULL)
GO
–插入加密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(12, ‘您出生的医院名称？’,
EncryptByKey(Key_GUID(‘sym_Demo ‘), ‘杭州市一’))
CLOSE SYMMETRIC KEY sym_Demo

代码如下:

–解密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
–打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

代码如下:

–不打开直接读取
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12

代码如下:

truncate table dbo.PWDQuestion
go

–添加两个未加密的行
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
select 13, ‘您出生的医院名称？’,cast(‘浙江妇保院’ as varbinary)
union all
select 14, ‘您出生的医院名称？’,cast(‘浙江妇保二院’ as varbinary)

–打开对称密钥,连CustomerID列一起加密
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
UPDATE dbo.PWDQuestion
SET PasswordHintAnswer =
EncryptByKey(Key_GUID(‘sym_Demo’),
PasswordHintAnswer,1,–1表示使用验证器值
CAST(CustomerID as varbinary))
WHERE CustomerID in (13,14)
–打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

–此时必须这样查看原数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,–1表示使用验证器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 13
–打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

代码如下:

/**********************************************************
–我们用刚才的CustomerID = 13的PasswordHintAnswer列值
–替换CustomerID = 14的PasswordHintAnswer列值，
–再用刚才读取14的方法读取真实值
**********************************************************/

update dbo.PWDQuestion set PasswordHintAnswer=
(select PasswordHintAnswer from dbo.PWDQuestion where CustomerID = 14)
where CustomerID = 13

代码如下:

OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD = ‘newnew!456’
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,–1表示使用验证器值
CAST(CustomerID as varbinary)) as varchar(200)) PasswordHintAnswer,
PasswordHintAnswer as binaryValue
FROM dbo.PWDQuestion
WHERE CustomerID in(13,14)
–打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo