浅析SQL Server 2008中的代码安全之六：对称密钥加密-对称密钥加密

证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据。而对称密钥相对简单，它们包含一个同时用来加密和解密的密钥。困此，使用对称密钥加密数据更快，并且用在大数据时更加合适。尽管复杂度是考虑使用它的因素，但它仍然是一个很好的加密数据的选择。

我们看一组例子：

示例一、创建对称密钥

对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。

创建对称密钥使用如下命令:CREATE SYMMETRIC KEY 创建对称密钥。（http://msdn.microsoft.com/en-us/library/ms188357.aspx）

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  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

示例二、查看当前数据库中的对称密钥

使用目录视图sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)来查看。

--查看当前数据库中的非对称密钥  
use DB_Encrypt_Demo  
go  
SELECT name, algorithm_desc FROM sys.symmetric_keys  
----结果返回  
/*  
name    algorithm_desc  
sym_Demo    TRIPLE_DES  
*/  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

示例三、修改非对称密钥的加密方式

你可以使用ALTER SYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改对称密钥的加密方式。但执行前必须使用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打开它。

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    
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

示例四、使用对称密钥对数据进行加密和解密。

1、为了使用对称密钥对数据进行加密，必须首先打开它，然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)

2、使用DecryptByKey来解密使用对称密钥加密的数据。注意DecryptByKey不像甩EncryptByKey，无须使用对称密钥GUID。因此，为了解密，必须打开正确的对称密钥会话，否则会显示null。

下面是一个例子：

/***************************************************/  
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  
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

查看未加密的数据：
--解密数据

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  
--打开后切记关闭！！！  
CLOSE SYMMETRIC KEY sym_Demo   
1.
2.
3.
4.
5.
6.
7.
8.

--不打开直接读取  
SELECT CustomerID,PasswordHintQuestion,  
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer  
FROM dbo.PWDQuestion  
WHERE CustomerID = 12   
 
1.
2.
3.
4.
5.
6.

至此，好像已经大功告成了，别，千万别高兴得太早！

这里有个问题，如果恶意用户不知道CustomerID＝13的PasswordHintAnswer列的真实值，但知道CustomerID＝14的PasswordHintAnswer列的真实值,则完全可以通过恶意替换PasswordHintAnswer列而绕过加密！！此时，我们索性连CustomerID列作为验证列也一起加密，以绝后患 !

注意：加密的验证列也可以由另一个相关表的列作为参数传入。

看一个完整的例子：

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)  
--打开后切记关闭！！！  
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  
--打开后切记关闭！！！  
CLOSE SYMMETRIC KEY sym_Demo   
 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

恶意替换开始：

/**********************************************************  
--我们用刚才的CustomerID = 13的PasswordHintAnswer列值  
--替换CustomerID = 14的PasswordHintAnswer列值，  
--再用刚才读取14的方法读取真实值**********************************************************/  
update dbo.PWDQuestion set PasswordHintAnswer=  
(select PasswordHintAnswer from   dbo.PWDQuestion where CustomerID = 14)  
where CustomerID = 13  
1.
2.
3.
4.
5.
6.
7.

此时，我们再查看：

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)  
--打开后切记关闭！！！  
CLOSE SYMMETRIC KEY sym_Demo   
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

郎勒个郎！爽吧！虽然复制了相同的二进制数据，可是读取结果令攻击者大失所望啊！

示例五、删除对称密钥

命令：DROP SYMMETRIC KEY 删除指定的对称密钥( http://technet.microsoft.com/en-us/library/ms182698.aspx)

例子：

DROP SYMMETRIC KEY symDemoKey  
1.

注意：如果加密密钥打开没有关闭，则drop失败。

小结：

1、本文主要介绍对称密钥的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。

2、对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。

3、对称密钥可用于大数据的加密。

下文将主要介绍证书加密(Certificate Encryption)

原文链接：http://www.cnblogs.com/downmoon/archive/2011/03/15/1984352.html

【编辑推荐】