加密列和 SQL Server 性能

引言

出于法律原因，加密包含敏感数据（如社保号码 SSN）的表列非常重要。SQL Server 2005 允许您使用不同的对称和非对称密钥算法来加密数据。或者，您也可以使用基于密码的加密，其中密码必须由客户端提供才能加密/解密数据。然而，任何事情都有代价，所以我们想知道这代价有多大。

有加密和无加密

让我们准备一些测试数据，10 万个“客户”：

SET NOCOUNT ON
go
create table Customers_Data (
   name varchar(128) not null, 
   SSN varchar(10) not null)
go
declare @cnt int set @cnt=100000
while @cnt>0 
   begin
   set @cnt=@cnt-1
   insert into Customers_Data (name,SSN) 
      select 'Cust_'+convert(varchar,@cnt),
      '010'+convert(varchar,@cnt)
   end
go

我们人工生成的伪造社保号码 SSN 都是唯一的。它们的格式为“010xxxxxx”，但在某些情况下较短。首先，让我们在没有任何加密的情况下进行实验。

-- Not encrypted
create table Customers (
   id int identity primary key, 
   name varchar(128) not null, 
   SSN varchar(10) not null)
go

在我们的第一个实验中，我们将原始 Customers_Data 复制到目标表中，并测量经过的时间。

-- inserts
declare @t1 datetime, @cnt int
truncate table Customers
set @t1=getdate()
insert into Customers (name,SSN) 
      select * from Customers_Data
select datediff(ms,@t1,getdate())
go

在第二个实验中，我们通过给定的社保号码 SSN 查找一条记录。这是通过表扫描比较所有社保号码 SSN 来完成的。重复此测试多次，以使其更精确。

-- table scan
declare @t1 datetime, @name varchar(128), @cnt int
set @t1=getdate()
set @cnt=30
while @cnt>0 begin
   set @cnt=@cnt-1
   select @name=max(name) from Customers 
      with(index(0)) – forced table scan
      where SSN='010'+convert(varchar,@cnt)
end
select datediff(ms,@t1,getdate())/30.
go

密钥

要加密数据，我们必须创建加密密钥。这是一个例子

create master key encryption by password = 'p@sswOrd'
go
create asymmetric key AsymKey With Algorithm = RSA_1024
go

现在，如果数据库被盗，加密数据无法恢复，因为主密钥未在用户数据库中备份。当数据被加密时，结果存储为 varbinary(128)。加密数据不能太长。

create table encr_Customers (
      id int identity primary key, 
      name varchar(128) not null, 
      SSN varbinary(128))
go

透明性和解密

我们希望我们的加密对其他 SQL 代码和前端是透明的。我们的视图 customers 必须与旧表 customers 的行为完全相同，除了不能使用 truncate table。

create view Customers 
as
   select 
      id, name, 
      convert(varchar(10),
        DecryptByAsymKey(AsymKey_ID('AsymKey'), SSN)) as SSN
      from encr_Customers
go

因此，当我们读取数据时，我们会动态解密列 SSN。当我们把数据插入到这个视图中时，如何加密数据呢？我们可以使用一个 instead of 触发器

create trigger IT1 on Customers
instead of insert
as
begin
   insert into encr_Customers (name,SSN) 
      select name,
      EncryptByAsymKey(AsymKey_ID('AsymKey'),SSN)
from inserted
end
GO

如果加密列参与更新，我们应该为更新创建一个类似的触发器。我们的视图的工作方式与之前的表完全相同，除了您不能说 truncate table Customers；您应该改用 truncate table encr_Customers。我们无法使基于密码的加密透明，因为我们必须将密码硬编码到视图中。当然，这将破坏加密的意义。

比较密钥和算法

现在我们可以比较不同的密钥和不同的加密算法

密钥类型	算法/长度	表扫描	测试插入
无加密		2	93
非对称密钥	1024	102656	2623
非对称密钥	512	21716	1486
非对称密钥	2048	747766	6563
对称密钥	DES	173	623
对称密钥	TRIPLE_DES	200	580
对称密钥	RC2	173	606
对称密钥	RC4	250	576
对称密钥	DESX	203	516
对称密钥	AES_128	173	736
对称密钥	AES_192	173	576
对称密钥	AES_256	186	610

请注意，非对称密钥的加密时间要长得多。解密数据时尤其如此。现在到了有趣的部分。假设我们想通过社保号码 SSN 查找客户

select * from Customers where SSN=@value

通常，社保号码 SSN 是索引的，并且可以立即找到记录。但是，如果您在加密视图中尝试它，您会看到 SQL Server 使用表扫描，即使在 encr_Customers.SSN 上创建了索引。为什么？看起来上面的语句等价于（在简化的代码中）

select * from encr_Customers where decrypt(SSN)=@value

如果我们将其重写为...

select * from encr_Customers where SSN=encrypt(@value)

...那么它的速度会更快并使用索引，但它不会找到我们的记录！发生这种情况是因为每次加密函数都会生成另一个值（值被截断），它们都是非确定性的

select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')
select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')
select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')
(values truncated)
0x9FD1DDA8A6ACED67C0C24CDD566CAD655E7B7E3D76197896…
0xE18FFFB9EC75CD2093089A5DDB83220A244346F77AA548BF…
0x98D563BA855573A442A278B2565D9216192AD5BC7B664637…

因此，不幸的是，加密数据上的所有索引都绝对没用。

历史

• 2007 年 6 月 11 日 - 发布原始版本