OPEN SYMMETRIC KEY scope in SQL Server

13916729435

　　
最近我收到一些关于对称密钥有效范围的问题，特别是在模块内（存储过程）打开对称密钥。其中一个问题是,如果在存储过程内执行打开密钥（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