OPEN SYMMETRIC KEY scope in SQL Server
Recently I have heard a few questions regarding the scope of the SYMMETRIC KEY key-ring, especially when using modules (i.e. stored procedures) to open a key. One particular topic that got my attention is the impression that the OPEN SYMMETRIC KEY call may “leak outside the module” (i.e. the key will remain opened) if the SYMMETRIC KEY is not closed inside the module.
In the OPEN SYMMETRIC KEY topic in BOL (under Remarks section) we documented that the opened key is bound to the session, not to the execution context (including a module frame) and that it will remain opened until the key is explicitly closed (using CLOSE SYMMETRIC KEY) or the session is terminated. This is indeed the designed behavior.
What does this mean when opening a symmetric key on a module that is not supposed to “leak” the key? It means that it is necessary to make sure that the key is explicitly closed; there is no mechanism to bind the OPEN SYMMETRIC KEY call to the module in SQL Server 2005.
For example:
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
Obviously in this example the intention was to keep the key opened after the module ends, but it is possible that this may happen by mistake and we “leak the key” as an undesired error (i.e. a bug in the application), for example:
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
-- 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
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
-- But this one will fail with error 15517
-- This error will terminate the current batch,
-- and the key will not close properly
--
EXEC [dbo].[sp_keyring_demo2] 2
go
SELECT * FROM sys.openkeys
go
--Clean up
CLOSE SYMMETRIC KEY [symkey_keyring_demo];
go
REVERT
go
Notice that this particular SP was not properly designed, and it keeps the key opened far longer than needed and that the error handing for the code segment that may fail. The following modification in the code is an improvement over the original one:
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
As you can see, the current design decision of binding the key ring to the session may affect the way you need to design modules that open symmeric keys, especially during scenarios where the key should only be opened on behalf of the caller exclusively during a module execution. It is very important to understand the limitations and behavior of the system in case of an error and taking it into consideration when designing an application that uses OPEN SYMMETRIC KEY.
A few tips for modules that open symmetric keys on behalf of the caller:
· Whenever is possible use the DecryptByKeyAuto* built-ins instead of the regular DecryptByKey one, that way the key is implicitly opened by the built-in and there is no need to explicitly open it on your module
· Have as little code as possible between Opening and Closing the symmetric key to minimize logic errors that may lead to opening the key without closing it properly.
· Make sure your code logic will handle errors properly. You can use TRY/CATCH in SQL to avoid an early termination of the module.
I hope this information will be useful; as always feel free to ask any questions or send us feedback regarding this topic, we will be glad to help.
-Raul Garcia
SDE/T
SQL Server Engine
