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'
GO
-- 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
GO
服务主密钥、数据库主密钥和证书必须像其他数据库对象那样备份、还原和维护。可以使用数据库来备份和还原证书、对称密钥和非对称密钥。证书也可以分别备份到磁盘或从磁盘还原。可以为证书提供只备份和还原公钥或公钥-私钥对。数据库主密钥不过是另一种对称密钥，但有专门的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)
)
GO
GRANT INSERT, SELECT ON dbo.secret_table TO User1, User2
GO
注意，这个表定义中并没有指定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
as
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
END
RETURN @RetVal
END
GO
现在让我们向表中放入一些数据。需要知道这个事实：加密算法使作为一个索引或带有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
GO
REVERT
GO
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
GO
REVERT
GO
用完密钥后应该将密钥关掉，尽管所有打开的密钥(称作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
GO
REVERT
GO
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
GO
REVERT
GO
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
GO
REVERT
GO
除了对每一类密钥的加密/解密函数以及传递基于短语的加密/解密，在使用证书或非对称密钥时还有自动的解密函数。为了方便，也提供了这些函数，因为一个单独的证书或非对称密钥有时用来加密表中的所有数据。如程序清单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
GO
CREATE SYMMETRIC KEY somekey AUTHORIZATION DBO
WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE somecert
GRANT VIEW DEFINITION ON SYMMETRIC KEY::somekey TO lowpriv_user
GO
CREATE TABLE dbo.secret_col_table (
id INT PRIMARY KEY IDENTITY,
secret_col VARBINARY(8000)
)
GO
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
GO
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
GO
-- now the lowpriv_user can access the column
GRANT SELECT ON dbo.public_view TO lowpriv_user
GO
注意，使用一个定义使用自动解密函数的视图时，低权限的用户不仅可以访问视图，而且还可以访问证书(CONTROL)，能查看(视图定义)加密密钥。可以使用一个帮助函数加以简化，该函数使用了EXECUTE AS OWNER特征。关于EXECUTE AS，详见本章后文的描述。
--示例一,使用证书加密数据.
--建立测试数据表
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
GO
--建立证书一,该证书使用数据库主密钥来加密
CREATE CERTIFICATE Cert_Demo1
WITH
SUBJECT=N'cert1 encryption by database master key',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--建立证书二,该证书使用密码来加密
CREATE CERTIFICATE Cert_Demo2
ENCRYPTION BY PASSWORD='liangCK.123'
WITH
SUBJECT=N'cert1 encrption by password',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--此时,两个证书已经建立完,现在可以用这两个证书来对数据加密
--在对表tb做INSERT时,使用ENCRYPTBYCERT加密
INSERT tb(data)
SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书1加密的内容-liangCK'); --使用证书1加密
INSERT tb(data)
SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是证书2加密的内容-liangCK'); --使用证书2加密
--ok.现在已经对数据加密保证了.现在我们SELECT看看
SELECT * FROM tb ;
--现在对内容进行解密显示.
--解密时,使用DECRYPTBYCERT
SELECT 证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),
--使用证书2解密时,要指定DECRYPTBYCERT的第三个参数,
--因为在创建时,指定了ENCRYPTION BY PASSWORD.
--所以这里要通过这个密码来解密.否则解密失败
证书2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))
FROM tb ;
--我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL
/*
证书1解密 证书2解密
-------------------------------------------------- --------------------------------------------------
这是证书1加密的内容-liangCK NULL
NULL 这是证书2加密的内容-liangCK
(2 行受影响)
*/
GO
--删除测试证书与数据表
DROP CERTIFICATE Cert_Demo1;
DROP CERTIFICATE Cert_Demo2;
DROP TABLE tb;
GO
--示例二,使用对称密钥加密数据,
--对称密钥又使用证书来加密.
--创建测试数据表tb
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
GO
--建立证书,该证书用于加密对称密钥.
CREATE CERTIFICATE Cert_Demo
ENCRYPTION BY PASSWORD=N'liangCK.123'
WITH
SUBJECT=N'cert encryption by password',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--建立对称密钥
CREATE SYMMETRIC KEY Sym_Demo
WITH
ALGORITHM=DES --使用DES加密算法
ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo证书加密
GO
--要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它
OPEN SYMMETRIC KEY Sym_Demo
DECRYPTION BY CERTIFICATE Cert_Demo
WITH PASSWORD=N'liangCK.123'
--插入加密数据
INSERT tb(data)
SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是加密的数据,能显示出来吗?')
--关闭密钥
CLOSE SYMMETRIC KEY Sym_Demo
--插入完加密数据,现在使用SELECT来查询一下数据
SELECT * FROM tb
GO
--现在来解密此数据
--同样,还是要先打开对称密钥
OPEN SYMMETRIC KEY Sym_Demo
DECRYPTION BY CERTIFICATE Cert_Demo
WITH PASSWORD=N'liangCK.123'
SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了.
FROM tb
CLOSE SYMMETRIC KEY Sym_Demo
GO
--删除测试
DROP SYMMETRIC KEY Sym_Demo
DROP CERTIFICATE Cert_Demo
DROP TABLE tb
--示例三,还有一种方法加密数据更简单
--就是使用EncryptByPassPhrase
--建立测试数据表tb
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
GO
INSERT tb(data)
SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');
--解密
SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))
FROM tb
GO
DROP TABLE tb