SQL Server 加密解密策略2

6.5 加密密钥和内置加密函数

工业和政府开始要求对数据库数据进行加密。在美国，健康保健和信用卡行业最先受到影响。根据新的法律，例如，医生不能私自访问其他医生的病人记录。在SQL Server 2005以前，数据加密仅仅由第三方的加密产品来支持。在SQL Server 2005中，数据加密是一个内置功能。有时候，数据加密在其他数据库的列级别上实现，具体做法是使用DDL扩展语句声明要加密的列。对于医生，这种类型的加密不够充分，除非每个医生的病人信息被存储在不同的表中。SQL Server 2005没有实现列级加密，它提供的是数据加密函数。由开发者决定调用这些函数，在执行INSERT语句时手工加密数据，在执行SELECT语句时解密数据。

为了加密和解密数据，我们需要使用密钥。所以首先需要回答的两个问题是“密钥将存放在哪里？”，“密钥本身是如何加密的？”。在讨论加密函数之前，我们需要先回答这些问题。

在很多情况下，SQL Server 2005通过使密钥成为数据库对象并通过正常的DDL语句存储和管理它们。密钥和数据库捆绑在一起，加密对象分等级配置。这个链条的最顶端是服务器主机密钥，这个密钥在安装SQL Server 2005时自动生成，是使用DPAPI(数据保护API，一个Windows操作系统特征)生成和存储的。这个密钥存储在master数据库中。密钥的生成首先使用运行SQL Server服务的用户。稍后将讨论SQL Server服务的用户发生改变时对密钥有何影响以及如何备份和还原密钥。每一个数据库都有一个数据库主密钥。对每一个新的数据库，数据库主密钥不是自动生成的是可选的。可以使用CREATE MASTER KEY DDL语句手工生成，如下所示：

-- Generating the database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPW#'

数据库主密钥是一个128字节的DES3密钥。数据库主密钥生成时，被存储在两个地方。一个副本使用服务主密钥加密并存储在master数据库；另一个副本使用提供的密码进行加密并存储在数据库本身中。使用Service Broker特性时，加密会话之类的功能需要有数据库主密钥。有关Service Broker的详情，参见第11章。

SQL Server可以存储的三种密钥类型分别是X.509证书、对称密钥和非对称密钥。这些密钥在数据库级定义，它们不是数据库架构的一部分。对称密钥和非对称密钥用来加密和解密数据或其他密钥。证书用来加密数据和密钥，但也可用于SQL Server 2005的其他功能，如在HTTP终端上的SSL加密。SQL Server 2005可以生成它自己拥有的证书，也可以使用外部源头生成的证书。使用由外部源头生成的证书时，它不使用证书认证链、过期策略以及撤销列表。可以用下面的DDL语句生成一个证书(最好为期一年)：

-- Generating a certificate,

-- no password, so encrypted by database master key

CREATE CERTIFICATE mycert

AUTHORIZATION certuser WITH SUBJECT = 'Cert for certuser'

每种类型的密钥可以用不同的机制来保护。表6.2给出了每类密钥保护的可能性。①

表6.2 SQL Server安全性密钥的加密选择

保密类型

可以使用的加密方法

多个可能的加密

(数据库)主密钥

密码(1个或多个)，服务主密钥(默认的，但它是可选的，并且可以被删除)

是

证书(私有密钥)

数据库主密钥，密码

否

非对称密钥(私有密钥)

数据库主密钥，密码

否

对称密钥

密码，证书，非对称密钥，对称密钥

是

使用DDL语句创建对称密钥和非对称密钥时，要指定密钥、拥有者、密钥使用的加密算法，以及用来加密密钥本身的机制。SQL Server支持不同的加密算法。

现在我们放下基本信息，排练一下使用加密密钥的场景并完整给出所涉及的DDL。

一张表包含一个加密数据列。User1和User2将只能够看到自己的加密数据，Admin1能够看到由User1和User2加密的数据。程序清单6.2阐述了如何创建相应的对称密钥和证书以保护它们。每一个证书和对称密钥被相应的用户拥有，如程序清单6.2所示。Admin1(管理员)必须能访问这两个密钥和两个证书。

程序清单6.2 创建由证书保护的对称密钥

-- Generate a certificate for user1 and user2

CREATE CERTIFICATE User1cert

AUTHORIZATION User1 WITH SUBJECT = 'Cert for User1'

CREATE CERTIFICATE User2cert

AUTHORIZATION User2 WITH SUBJECT = 'Cert for User2'

-- symmetric keys to be used for encryption

-- they are faster than certificates

CREATE SYMMETRIC KEY Key1 AUTHORIZATION User1

WITH ALGORITHM = TRIPLE_D ENCRYPTION BY CERTIFICATE User1cert

CREATE SYMMETRIC KEY Key2 AUTHORIZATION User2

WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE User2cert

服务主密钥、数据库主密钥和证书必须像其他数据库对象那样备份、还原和维护。可以使用数据库来备份和还原证书、对称密钥和非对称密钥。证书也可以分别备份到磁盘或从磁盘还原。可以为证书提供只备份和还原公钥或公钥-私钥对。数据库主密钥不过是另一种对称密钥，但有专门的DDL来创建和操纵它。分离一个有数据库主密钥的数据库并把这个数据库附加到另一个实例时，需要打开数据库主密钥(通过一个密码)并使用新实例的服务主密钥加密它。也可以使用一个数据库主密钥，而不使用服务主密钥来加密数据库主密钥并将其存储在主数据库中。详情参见后文。

服务主密钥是特殊的，因为：

它和运行SQL Server服务进程的用户绑定

它用于加密数据库主密钥

它用于加密服务器范围内的其他认证，例如认证证书

可以像数据库主密钥和证书一样，使用DDL语句来备份和还原服务主密钥。但是，如果没有显式解密和重新加密服务主密钥，则不能更改运行SQL Server服务进程用户的标识。否则会使所有的密钥不可用。SQL Server Configuration Manager包含一个特殊函数，用于改变SQL Server服务的一个用户。你必须使用这个实用工具来保护密钥的可行性。

6.6 加密函数

既然了解了什么是密钥，让我们讨论一下用于数据加密的函数。为此用途定义的15T_SQL内置函数和一个相关的辅助函数如下所示：

EncryptByAsymKey

DecryptByAsymKey

EncryptByCert

DecryptByCert

EncryptByPassPhrase

DecryptByPassPhrase

EncryptByKey

DecryptByKey

DecryptByKeyAutoCert

DecryptByKeyAutoAsymKey

SignByAsymKey

SignByCert

VerifySignedByAsymKey

VerifySignedByCert

HashBytes

首先，谈谈密钥和加密/解密函数。对称密钥使用相同的密钥来加密和解密。这提出了一个密钥分布的问题，因为分布在双方的密钥必须没有变动。然而，对于加密/解密而言，对称密钥比非对称密钥要快几个数量级。非对称密钥使用一对密钥来实现加密/解密。只有非对称密钥的拥有者才有私钥部分，外部世界拥有公钥部分。公钥是公开的，以至于它们可以在密钥目录中发布。非对称密钥对的一个用途是：用你的私钥加密一些数据(比如一个合法文档)的散列。一个拥有公钥的用户可以重新计算散列并用你的公钥解密被加密的散列并确认这个散列来自你，因为只有你拥有用来加密它的私钥。这称作签名。外部世界可以使用你的公钥加密数据来发送给你，只有你有正确的私钥来解密它。这就是所谓的数据封装。非对称密钥加密和解密数据较慢，但常用来生成和保护一个在单个数据加密会话中使用的对称的“会话密钥”。证书仅仅是附加了元数据的非对称密钥，这些元数据包括密钥发行者和终止日期等。SQL Server支持X509.V3证书，它可以存储但不能使用X509.V3证书中的V3特殊字段。总的说来，有三个常用的加密规则需要记住：

对称密钥比非对称密钥快

数据的数量越大①，加密和解密所花费的时间越长

通常，用来加密的密钥越长，加密越安全，但花费的加密和解密时间也越长

让我们使用在前面定义的用来加密一个表中部分数据的一套密钥。我们将演示两个用户各自能看到他们自己拥有的数据的场景。除此之外，这里有一个管理员，他可以看到两个用户的数据。首先，我们定义一个用于保存一个加密字段的表：

-- The primary key and name data are public

-- The secret_data column is encrypted

CREATE TABLE dbo.secret_table (

id INT PRIMARY KEY IDENTITY,

first_name VARCHAR(20),

last_name VARCHAR(50),

secret_data VARBINARY(8000)

)

GRANT INSERT, SELECT ON dbo.secret_table TO User1, User2

注意，这个表定义中并没有指定secret_data是用来存储加密数据的。事实上，同一个列可以用来存储加密数据和非加密数据，尽管这样可能导致一些难处理的SQL语句。列被定义成VARBINARY类型(这是要求)，但并不支持新数据类型VARBINARY(MAX)。但这一列事实上至少有多大？能存储多少加密数据？存储需要的最小容量可以使用程序清单6.3中的算法计算出。加密算法使用“一般”的VARCHAR数据类型，而不是新的VARCHAR(MAX)数据类型，所以加密数据的最大数量恰好低于8000字节。②试图加密任何更大的数据都将导致一个截断错误。

程序清单6.3 计算加密数据所需字段大小的算法

CREATE FUNCTION dbo.CalculateCipherLen(

@KeyName sysname, @PTLen int, @UsesHash int = 0 )

RETURNS int

BEGIN

declare @KeyType nvarchar(2)

declare @RetVal int

declare @BLOCK int

declare @IS_BLOCK int

declare @HASHLEN int

-- Hash length that

SET @HASHLEN = 20

SET @RetVal = NULL

-- Look for the symmetric key in the catalog

SELECT @KeyType = key_algorithm

FROM sys.symmetric_keys WHERE name = @KeyName

-- If parameters are valid

IF( @KeyType is not null AND @PTLen > 0)

BEGIN

-- If hash is being used. NOTE: as we use this value to

-- calculate the length, we only use 0 or 1

IF( @UsesHash <> 0 ) SET @UsesHash = 1

-- 64 bit block ciphers

IF( @KeyType = N'R2' OR @KeyType = N'D'

OR @KeyType = N'D3' OR @KeyType = N'DX' )

BEGIN

SET @BLOCK = 8

SET @IS_BLOCK = 1

END

-- 128 bit block ciphers

ELSE IF( @KeyType = N'A1' OR @KeyType = N'A2' OR @KeyType = N'A3' )

BEGIN

SET @BLOCK = 16

SET @IS_BLOCK = 1

END

-- Stream ciphers, only RC4 is supported as a stream cipher

ELSE

BEGIN

SET @IS_BLOCK = 0

END

-- Calculate the expected length.

-- The formula is different for block ciphers & stream ciphers

IF( @IS_BLOCK = 1 )

BEGIN

SET @RetVal =

( FLOOR( (8 + @PTLen + (@UsesHash * @HASHLEN) )/@BLOCK)+1 ) *

@BLOCK + 16 + @BLOCK

END

ELSE

BEGIN

SET @RetVal = @PTLen + (@UsesHash * @HASHLEN) + 28

END

RETURN @RetVal

END

现在让我们向表中放入一些数据。需要知道这个事实：加密算法使作为一个索引或带有UNIQUE，PRIMARY KEY和FOREIGN KEY列约束的列基本无用。

为了使用对称密钥，用户不仅必须有使用它们的权限，还必须显式打开密钥。为了打开密钥，必须提供密钥相应的密码。这依赖于密钥是如何使用CREATE和ALTER DDL语句来加密的。如果密钥是使用数据库主密钥加密的，则只需要访问密钥。数据库主密钥本身可以自动打开，只要它像本章前面描述的那样(由服务主密钥加密)存储在master数据库中。这是默认的。对于加密和解密，都需要打开密钥。让我们为每一个用户向数据库中插入一行加密的数据。如程序清单6.4所示。

程序清单6.4 向表中插入数据的同时加密数据

-- insert rows into secret_table, using encryption

EXECUTE AS USER='user1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert;

INSERT dbo.secret_table VALUES('Joe', 'User',

EncryptByKey(Key_GUID('Key1'),'some secret number1'))

CLOSE SYMMETRIC KEY Key1

REVERT

EXECUTE AS USER='user2'

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert;

INSERT dbo.secret_table VALUES('Jill', 'Smith',

EncryptByKey(Key_GUID('Key2'),'some other secret number2'))

CLOSE SYMMETRIC KEY Key2

REVERT

用完密钥后应该将密钥关掉，尽管所有打开的密钥(称作keyring)在会话结束是自动关闭的。即使是在使用连接池，情况也如此，但每个会话一个密钥，并且和执行上下文无关。现在让我们来获取数据。

在没有使用解密函数的情况下，如果试图访问包含了加密数据的列，则无论你是否打开相应的解密密码，返回的都是加密的数据。为了取得不加密的数据，必须在SELECT语句中使用一个加密算法。一个简单的替代方法是把它封装在一个视图里。当解密函数被指定作为SELECT语句的一部分时，SQL Server试图使用你所拥有的所有密钥来解密。只要有一个密钥成功，就会有正确的数据返回。否则，解密函数将返回空。程序清单6.5说明了这个问题。

程序清单6.5 只有可以访问密钥的用户可以访问数据

EXECUTE AS USER='user1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert

-- you get the encrypted value

SELECT id, first_name, last_name, secret_data

FROM secret_table

-- you either get the unencrypted value or NULL

SELECT id, first_name, last_name,

cast(decryptByKey(secret_data) AS VARCHAR(256))

FROM secret_table

CLOSE SYMMETRIC KEY Key1

REVERT

EXECUTE AS USER='user2'

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert

-- you get the encrypted value

SELECT id, first_name, last_name, secret_data

FROM secret_table

-- you either get the unencrypted value or NULL

SELECT id, first_name, last_name,

cast(decryptByKey(secret_data) AS VARCHAR(256))

FROM secret_table

CLOSE SYMMETRIC KEY Key2

REVERT

EXECUTE AS USER='admin1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert

-- cyphertext

SELECT id, first_name, last_name, secret_data

FROM secret_table

-- sees both rows decrypted

SELECT id, first_name, last_name,

cast(decryptByKey(secret_data) AS VARCHAR(256))

FROM secret_table

CLOSE ALL SYMMETRIC KEYS

REVERT

除了对每一类密钥的加密/解密函数以及传递基于短语的加密/解密，在使用证书或非对称密钥时还有自动的解密函数。为了方便，也提供了这些函数，因为一个单独的证书或非对称密钥有时用来加密表中的所有数据。如程序清单6.6所示。在这种情况下，你可以使用一个视图来查看表，密钥管理是自动进行的。

程序清单6.6 在一个视图上使用DecryptByKeyAutoCert函数

-- Generate a certificate owned by DBO

CREATE CERTIFICATE somecert

AUTHORIZATION DBO WITH SUBJECT = 'Cert for Encrypted column'

GRANT CONTROL ON CERTIFICATE::somecert TO lowpriv_user

CREATE SYMMETRIC KEY somekey AUTHORIZATION DBO

WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE somecert

GRANT VIEW DEFINITION ON SYMMETRIC KEY::somekey TO lowpriv_user

CREATE TABLE dbo.secret_col_table (

id INT PRIMARY KEY IDENTITY,

secret_col VARBINARY(8000)

)

OPEN SYMMETRIC KEY somekey DECRYPTION BY CERTIFICATE somecert

INSERT dbo.secret_col_table VALUES(

EncryptByKey(Key_GUID('somekey'), 'more secret data'))

CLOSE SYMMETRIC KEY somekey

CREATE VIEW dbo.public_view AS

SELECT id, convert(varchar(100),

DecryptByKeyAutocert(cert_id('somecert'), null, secret_col))

AS dbo.secret_col

FROM dbo.secret_col_table

-- now the lowpriv_user can access the column

GRANT SELECT ON dbo.public_view TO lowpriv_user

注意，使用一个定义使用自动解密函数的视图时，低权限的用户不仅可以访问视图，而且还可以访问证书(CONTROL)，能查看(视图定义)加密密钥。可以使用一个帮助函数加以简化，该函数使用了EXECUTE AS OWNER特征。关于EXECUTE AS，详见本章后文的描述。

posted on 2009-07-28 09:50 ghost's 阅读(604) 评论(0) 编辑收藏举报

刷新页面返回顶部

ghost's

导航

公告

SQL Server 加密解密策略2