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

我经常被问及这样的问题，能否让一个用户有加密数据的能力却不能解密数据呢？ 答案是：可以。但在我展示这个方法之前，我们先稍微讨论一下对称密钥（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