SQL Server 2008中的代码安全（六）：对称密钥加密

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

我们看一组例子：

示例一、创建对称密钥

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

创建对称密钥使用如下命令:

CREATE SYMMETRIC KEY  创建对称密钥。（aspx">http://msdn.microsoft.com/en-us/library/ms188357.aspx ）

view plaincopy to clipboardprint?
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
-- 创建一个用于加密对称密钥的非对称密钥
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

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

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

view plaincopy to clipboardprint?
--查看当前数据库中的非对称密钥  
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
SELECT name, algorithm_desc FROM sys.symmetric_keys
结果返回
/*
name    algorithm_desc
sym_Demo    TRIPLE_DES
*/

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

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

view plaincopy to clipboardprint?
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 
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、为了使用对称密钥对数据进行加密，必须首先打开它，然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx )

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

下面是一个例子：

view plaincopy to clipboardprint?
/****************************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 
/****************************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

查看未加密的数据：

view plaincopy to clipboardprint?
--解密数据  
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  
--解密数据
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 

view plaincopy to clipboardprint?
--不打开直接读取  
SELECT CustomerID,PasswordHintQuestion,  
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer  
FROM dbo.PWDQuestion  
WHERE CustomerID = 12 
--不打开直接读取
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12

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

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

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

看一个完整的例子：

view plaincopy to clipboardprint?
truncate table dbo.PWDQuestion&nbs

补充：综合编程 , 安全编程 ,

上一个：SQL Server 2008中的代码安全（七）：证书加密
下一个：SQL Server 2008中的代码安全（五）：非对称密钥加密