OPEN SYMMETRIC KEY scope in SQL Server
最近我收到一些关于对称密钥有效范围的问题，特别是在模块内（存储过程）打开对称密钥。其中一个问题是,如果在存储过程内执行打开密钥（OPEN SYMMETRIC KEY）的操
作，而退出存储过程前没有将密钥关闭，会导致密钥“泄露”到模块以外。
在BOL的OPEN SYMMETRIC KEY（在备注下方）文章中，我们已经写明，打开的密钥在整个会话范文内有效，而仅仅是在执行上下文内（包括存储过程），并且密钥在被显示关闭（使用CLOSE SYMMETRIC KEY语句）或者会话终止前密钥都是处于打开状态的。
如何保证密钥在模块内打开并且不会被“泄露”出去呢？需要显示密钥关闭。目前为止SQLSERVER 2005还不能将OPEN SYMMETRIC KEY的有效范围限制在模块内。
示例：
CREATE CERTIFICATE
WITH SUBJECT = 'key ring demo'
go
CREATE SYMMETRIC KEY
WITH ALGORITHM = AES_192
ENCRYPTION BY CERTIFICATE
go
CREATE USER WITHOUT LOGIN
go
CREATE PROC
-- We will be runnign this module under an impersonated context
WITH EXECUTE AS OWNER
AS
OPEN SYMMETRIC KEY
DECRYPTION BY CERTIFICATE
-- Notice that the key is not being closed on purpose
--
go
-- Grant minimum privielges
--
GRANT EXECUTE ON . TO
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: TO
go
EXECUTE AS USER = 'lowpriv_user'
go
SELECT * FROM sys.openkeys
go
-- fails with error 15151
--
OPEN SYMMETRIC KEY
DECRYPTION BY CERTIFICATE
go
-- This will succeed
--
EXEC .
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 .( id int IDENTITY PRIMARY KEY,
data varbinary(1000), LastUsedDate datetime )
go
OPEN SYMMETRIC KEY
DECRYPTION BY CERTIFICATE
go
INSERT INTO .
VALUES ( encryptbykey( key_guid( 'symkey_keyring_demo'), 'lowpriv_user' ), GetDate())
INSERT INTO .
VALUES ( encryptbykey( key_guid( 'symkey_keyring_demo'), 'outdated_user' ), GetDate())
go
CLOSE SYMMETRIC KEY
go
CREATE PROC ( @id int )
WITH EXECUTE AS OWNER
AS
-- 改存储过程目的为解密数据，
-- 并在退出存储过程前关闭密钥
--
declare @username varchar(1000)
if( EXISTS(SELECT count(*) FROM . WHERE Id = @id))
BEGIN
OPEN SYMMETRIC KEY
DECRYPTION BY CERTIFICATE ;
SELECT @username = convert( varchar(1000), decryptbykey( data ))
FROM . 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 . SET LastUsedDate = GetDate() WHERE Id = @id;
CLOSE SYMMETRIC KEY ;
END
go
GRANT EXECUTE ON . TO
go
--Let's give it a try
EXECUTE AS USER = 'lowpriv_user'
go
-- This one will work as expected
--
EXEC . 1
go
-- and no keys in the key ring
--
SELECT * FROM sys.openkeys
go
-- 这次运行时会失败，出现15517错误
-- 该错误会终止batch
-- 导致密钥无法关闭
--
EXEC . 2
go
SELECT * FROM sys.openkeys
go
--Clean up
CLOSE SYMMETRIC KEY ;
go
REVERT
go
注意，上面的代码设不当，使密钥打开的时间过长，代码的错误处理也可能失败。下面是改进代码：
ALTER PROC ( @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 . WHERE Id = @id))
BEGIN
SELECT @username = convert( varchar(1000), DecryptByKeyAutoCert( cert_id('cert_keyring_demo'), null, data ))
FROM . 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 . SET LastUsedDate = GetDate() WHERE Id = @id;
END
go
-- succeeds
--
EXEC . 1
go
-- fails gracefully
--
EXEC . 2
go
如你所见，密钥是在会话范围内有效的。如果你要编码一种模块：密钥只能被模块的调用者使用，并只在模块内有效。 密钥的这种特性会影响到模块的设计。 所以如果你的应用程序用到了OPEN SYMMETRIC KEY语句，你要考虑到密钥的这种限制和SQLSERVER错误处理的方式。
一些提示，如何在保护调用者利益的情况下开打密钥：
· 如果可能，尽量用DecryptByKeyAuto语句代替DecryptByKey语句，使用DecryptByKeyAuto不需要显示地将密钥打开，该语句会自动打开密钥（并在解密后关闭密钥，译者注）
· 尽可能减少打开密钥和关闭密钥之间的代码，减少打开密钥却没有关闭密钥的逻辑错误
· 确保在代码内进行错误处理，使用TRY/CATCH防止模块过早终止
原文地址：OPEN SYMMETRIC KEY scope in SQL Server
页:
[1]