OPEN SYMMETRIC KEY scope in SQL Server

 

最近我收到一些关于对称密钥有效范围的问题，特别是在模块内（存储过程）打开对称密钥。其中一个问题是,如果在存储过程内执行打开密钥（OPEN SYMMETRIC KEY）的操

作，而退出存储过程前没有将密钥关闭，会导致密钥“泄露”到模块以外。

 

在BOL的OPEN SYMMETRIC KEY（在备注下方）文章中，我们已经写明，打开的密钥在整个会话范文内有效，而仅仅是在执行上下文内（包括存储过程），并且密钥在被显示关闭（使用CLOSE SYMMETRIC KEY语句）或者会话终止前密钥都是处于打开状态的。

 

如何保证密钥在模块内打开并且不会被“泄露”出去呢？需要显示密钥关闭。目前为止SQLSERVER 2005还不能将OPEN SYMMETRIC KEY的有效范围限制在模块内。

 

示例：

 

CREATE  CERTIFICATE  [ cert_keyring_demo ]
   WITH  SUBJECT  =   ' key ring demo '

go

 
CREATE  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
   WITH  ALGORITHM  =  AES_192
  ENCRYPTION  BY  CERTIFICATE  [ cert_keyring_demo ]

go

 
CREATE   USER   [ lowpriv_user ]  WITHOUT LOGIN
go

CREATE   PROC   [ sp_openkey ]
--  We will be runnign this module under an impersonated context
WITH   EXECUTE   AS  OWNER
AS
   OPEN  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
    DECRYPTION  BY  CERTIFICATE  [ cert_keyring_demo ]

   --  Notice that the key is not being closed on purpose

   --

go


--  Grant minimum privielges
--
GRANT   EXECUTE   ON   [ dbo ] . [ sp_openkey ]   TO   [ lowpriv_user ]
GRANT   VIEW  DEFINITION  ON  SYMMETRIC  KEY :: [ symkey_keyring_demo ]   TO   [ lowpriv_user ]

go

EXECUTE   AS   USER   =   ' lowpriv_user '
go


SELECT   *   FROM  sys.openkeys

go

--  fails with error 15151
--
OPEN  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
     DECRYPTION  BY  CERTIFICATE  [ cert_keyring_demo ]
go

 
--  This will succeed
--
EXEC   [ dbo ] . [ sp_openkey ]
go

 

--  And we can verify that the key is opened on our session.
SELECT   *   FROM  sys.openkeys
go

 

--  and we can encrypt & decrypt
declare   @blob   varbinary ( 1000 )
declare   @pt   varchar ( 1000 )
SET   @blob   =  encryptbykey( key_guid(  ' symkey_keyring_demo ' ),  ' data '  )
SET   @pt   =   convert (  varchar ( 1000 ), decryptbykey(  @blob  ))
SELECT   @pt ,  @blob
go


--  We can swithc context
REVERT

go

--  and verify that the key ring is still opened
SELECT   *   FROM  sys.openkeys

go

--  And the key remains opened until we close it 
--  or we terminate the session
--
CLOSE  SYMMETRIC  KEY  symkey_keyring_demo

go

 

上面的代码故意使密钥在模块结束后还保持打开状态，但是这种情况也可能是由错误的操作引起，导致了密钥的泄露（也就是说 应用程序中存在这样的bug）

例如：

 

CREATE   TABLE   [ dbo ] . [ tabl_keyring_demo ] ( id  int   IDENTITY   PRIMARY   KEY , 
  data  varbinary ( 1000 ), LastUsedDate  datetime  )
go


OPEN  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
 DECRYPTION  BY  CERTIFICATE  [ cert_keyring_demo ]
go

 

INSERT   INTO   [ dbo ] . [ tabl_keyring_demo ]  
   VALUES  ( encryptbykey( key_guid(  ' symkey_keyring_demo ' ),  ' lowpriv_user '  ),  GetDate ())
INSERT   INTO   [ dbo ] . [ tabl_keyring_demo ]  
   VALUES  ( encryptbykey( key_guid(  ' symkey_keyring_demo ' ),  ' outdated_user '  ),  GetDate ())
go

CLOSE  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
go

 

CREATE   PROC   [ sp_keyring_demo2 ] (  @id   int  )
WITH   EXECUTE   AS  OWNER
AS
--  改存储过程目的为解密数据，
--  并在退出存储过程前关闭密钥
--
declare   @username   varchar ( 1000 )
if (  EXISTS ( SELECT   count ( * )  FROM   [ dbo ] . [ tabl_keyring_demo ]   WHERE  Id  =   @id ))
BEGIN
      OPEN  SYMMETRIC  KEY   [ symkey_keyring_demo ]  
          DECRYPTION  BY  CERTIFICATE  [ cert_keyring_demo ] ;
          
      SELECT   @username   =   convert (  varchar ( 1000 ), decryptbykey( data )) 
        FROM   [ dbo ] . [ tabl_keyring_demo ]   WHERE  Id  =   @id ;

      --  For demonstration purposes, I will add a DDL statetemnt that I know
      --  will fail the second time I execute the module
     if (  @username   is   not   null  )
     BEGIN
           EXECUTE   AS   USER   =   @username
           --  do something interesting under this context
          REVERT 
     END

      --  Updating LastUsedDate column 
      UPDATE   [ dbo ] . [ tabl_keyring_demo ]   SET  LastUsedDate  =   GetDate ()  WHERE  Id  =   @id ;
      CLOSE  SYMMETRIC  KEY   [ symkey_keyring_demo ] ;

END

go
GRANT   EXECUTE   ON   [ dbo ] . [ sp_keyring_demo2 ]   TO   [ lowpriv_user ]
go

 

-- Let's give it a try
EXECUTE   AS   USER   =   ' lowpriv_user '
go

--  This one will work as expected
--
EXEC   [ dbo ] . [ sp_keyring_demo2 ]   1
go
--  and no keys in the key ring
--
SELECT   *   FROM  sys.openkeys
go

 

--  这次运行时会失败，出现15517错误
--  该错误会终止batch
--  导致密钥无法关闭
--

EXEC   [ dbo ] . [ sp_keyring_demo2 ]   2
go

SELECT   *   FROM  sys.openkeys
go

-- Clean up
CLOSE  SYMMETRIC  KEY   [ symkey_keyring_demo ] ;
go

REVERT

go

 

注意，上面的代码设不当，使密钥打开的时间过长，代码的错误处理也可能失败。下面是改进代码：

ALTER   PROC   [ sp_keyring_demo2 ] (  @id   int  )
WITH   EXECUTE   AS  OWNER
AS

--  The intention of this SP is to decrypt data, but close the key
--  before leaving the module frame
--

declare   @username   varchar ( 1000 )
if (  EXISTS ( SELECT   count ( * )  FROM   [ dbo ] . [ tabl_keyring_demo ]   WHERE  Id  =   @id ))
BEGIN
      SELECT   @username   =   convert (  varchar ( 1000 ), DecryptByKeyAutoCert( cert_id( ' cert_keyring_demo ' ),  null , data )) 
        FROM   [ dbo ] . [ tabl_keyring_demo ]   WHERE  Id  =   @id ;

      --  For demonstration purposes, I will add a DDL statetemnt that I know
      --  will fail the second time I execute the module

     if (  @username   is   not   null  )
     BEGIN
           BEGIN  TRY
               EXECUTE   AS   USER   =   @username
               --  do something interesting under this context
              REVERT 
           END  TRY

           BEGIN  CATCH
               print   ' Unexpected error '
               print  error_message()
               --  add code to handle error properly here
               --
               RAISERROR (  ' Error in impersonated context ' ,  16 ,  1  )
           END  CATCH

     END

      --  Updating LastUsedDate column 
      UPDATE   [ dbo ] . [ tabl_keyring_demo ]   SET  LastUsedDate  =   GetDate ()  WHERE  Id  =   @id ;
END

go

--  succeeds
--
EXEC   [ dbo ] . [ sp_keyring_demo2 ]   1
go

 

--  fails gracefully
--
EXEC   [ dbo ] . [ sp_keyring_demo2 ]   2
go

 

    如你所见，密钥是在会话范围内有效的。如果你要编码一种模块：密钥只能被模块的调用者使用，并只在模块内有效。 密钥的这种特性会影响到模块的设计。 所以如果你的应用程序用到了OPEN SYMMETRIC KEY语句，你要考虑到密钥的这种限制和SQLSERVER错误处理的方式。

 

一些提示，如何在保护调用者利益的情况下开打密钥：
 

·         如果可能，尽量用DecryptByKeyAuto语句代替DecryptByKey语句，使用DecryptByKeyAuto不需要显示地将密钥打开，该语句会自动打开密钥（并在解密后关闭密钥，译者注）

·         尽可能减少打开密钥和关闭密钥之间的代码，减少打开密钥却没有关闭密钥的逻辑错误

·         确保在代码内进行错误处理，使用TRY/CATCH防止模块过早终止

 

 

 原文地址：OPEN SYMMETRIC KEY scope in SQL Server