SQL Server 2005: 如何让用户只能加密数据却不能解密数据

豆包ko

我经常被问及这样的问题，能否让一个用户有加密数据的能力却不能解密数据呢？ 答案是：可以。但在我展示这个方法之前，我们先稍微讨论一下对称密钥（symmetric key）。 用户能否加密或解密数据取决于用户能否够打开这个密钥。 如果用户可以打开密钥，那么他既能加密也能解密。 要想限制用户去使用密钥，需要删除用户打开密钥的能力。我们可以创建一个存储过程，将加密和打开密钥的过程封装进去，并让这个存储过程有权限执行这些操作（通过签名），然后赋予用户执行存储过程的权限。这样用户就可以通过存储过程访问密钥，但不能直接方法问密钥。注意，如果用户可以在并行操作中调用这个存储过程，可能会导致密钥被用户直接使用(用户没有打开密钥的权限，却能执行加密/解密操作。译者注)。虽然我不认为这会被利用，但仍存在这种可能性。
下面是示例代码


　　
　　


--
-- 这个demo演示让用户可以加密数据而不能解密数据
create database test
use test
-- 创建数据库主密钥
create master key encryption by password = 'Avcptnwgu@)!)'
-- 创建一个证书，该证书用于加密下面的对称密钥
create certificate cert_protect_skey_data with subject = 'Certificate for encrypting
symmetric key'
-- 创建对称密钥，用于加密数据
create symmetric key skey_data with algorithm = triple_des encryption by certificate
cert_protect_skey_data
--创建存储过程，使用对称密钥加密数据
create procedure sp_encrypt_with_skey_data
@plaintext  varbinary(8000),
@ciphertext  varbinary(8000) output
as
begin
open symmetric key skey_data decryption by certificate cert_protect_skey_data
set @ciphertext = encryptbykey(key_guid('skey_data'), @plaintext)
close symmetric key skey_data
end
-- 验证该存储过程， 在一个batch内执行下代码
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
print 'Plaintext: '
open symmetric key skey_data decryption by certificate cert_protect_skey_data
print convert(varchar(200), decryptbykey(@ciphertext))
close symmetric key skey_data
go
-- 创建一个不能访问密钥的主体
create login alice with password = 'TiA''ssptncgt#)))'
create user alice
-- 允许Alice执行该存储过程
-- 我们希望她能够使用密钥加密
-- 但不能解密
grant execute on sp_encrypt_with_skey_data to alice
-- 验证Alice可以加密
execute as login = 'Alice'
select suser_name()
-- Alice可以执行存储过程，但他不能访问密钥
-- 密钥并没有所有权链的机制
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
-- Alice明显无法直接访问密钥
open symmetric key skey_data decryption by certificate cert_protect_skey_data
-- revert context
revert
-- 现在对存储过程进行签名，使Alice能够访问密钥
-- 创建用于签名的证书
create certificate cert_sign2use_skey_data with subject = 'Certificate for signing code that
will use the symmetric key'
-- 创建一个映射到证书的用户
create user u_cert_sign2use_skey_data for certificate cert_sign2use_skey_data
-- 授权
grant view definition on symmetric key::skey_data to u_cert_sign2use_skey_data
grant control on certificate::cert_protect_skey_data to u_cert_sign2use_skey_data
-- 签名
add signature to sp_encrypt_with_skey_data by certificate cert_sign2use_skey_data
-- 现在Alice可以真正地使用这个存储过程了
execute as login = 'alice'
select suser_name()
-- 注意，现在Alice仍然不能直接访问密钥
open symmetric key skey_data decryption by certificate cert_protect_skey_data
-- 现在在一个batch执行一下代码，验证加密
-- 注意加密会成功，但解密会失败
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
print 'Plaintext: '
print convert(varchar(200), decryptbykey(@ciphertext))
go
revert
-- cleanup
use master
drop database test
drop login alice
-- EOD
　　
　　
　　原文地址：http://blogs.msdn.com/lcris/archive/2006/01/13/sql-server-2005-example-for-how-to-allow-a-user-to-encrypt-but-not-decrypt.aspx