SQL Server – Symmetric Key – What permissions are needed to use?

Background

This is is something I have been trying to document for a while here. But, never quite got around to doing so.

It is Monday and though don’t really have a down time, I will take a little time to invest in the future.

 

Introduction

The basic question is that to use a symmetric key what are the minimal permissions needed?

 

LAB

Template

We will use Microsoft’s own sample available here to create a sample symmetric key.

Create Symmetric Key

Code


/*
 Create master key
*/
CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'My p@55w0Rd';
GO


/*
 Create certificate
*/
CREATE CERTIFICATE [cert_keyProtection] 
    WITH SUBJECT = 'Key Protection';
GO

/*
 Create Symmetric Key
*/
CREATE SYMMETRIC KEY [key_DataShare] WITH
      KEY_SOURCE = 'My key generation bits.'
    , ALGORITHM = AES_256, 
    , IDENTITY_VALUE = 'Key Identity generation bits.'
    , ENCRYPTION BY CERTIFICATE [cert_keyProtection]
    ;
GO

 

Create User

Code

We will create a simple database contained user as the user will only be used to validate access to symmetric keys.


create user [bookViewer] without login
go

 

Access Symmetric Key / As User

Let us attempt to access the symmetric key while connected as our “no privilege” user.

Code


set nocount on
set XACT_ABORT on
go

setuser ''
go

setuser 'bookViewer'
go

OPEN SYMMETRIC KEY [key_DataShare] 
    DECRYPTION BY CERTIFICATE cert_keyProtection;
GO

   SELECT [encrypted]
	= encryptbykey
             (
                  key_guid('key_DataShare')
                , 'MyData' 
             )
	GO


if exists
	(
   	   select *
	   from   sys.openkeys
	   where  key_name = 'key_DataShare'
	)
begin

   CLOSE SYMMETRIC KEY [key_DataShare] 

end
go

setuser ''
go

Error Message


Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'key_DataShare', because it does not exist or you do not have permission.

On Symmetric Key \ Grant View Definition to User

We will grant view definition privilege to the User.

Code


--Grant View Definition
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[key_DataShare] TO [bookViewer]

Error Message

We went back and ran our test code, and came back with a new error message.


Msg 15151, Level 16, State 1, Line 2
Cannot find the certificate 'cert_keyProtection', because it does not exist or you do not have permission.

Explanation

  1. So the original error message stated that “Cannot find the symmetric key“. The new error message states “Cannot find the certificate

 

On Certificate \ Grant View Definition to User

Let us grant same view definition on the certificate.

Code


--Grant View Definition on Certificate
GRANT VIEW DEFINITION ON CERTIFICATE::[cert_keyProtection] TO [bookViewer]

Error


Msg 15151, Level 16, State 1, Line 2
Cannot find the certificate 'cert_keyProtection', because it does not exist or you do not have permission.

Explanation

  1. Same error “Cannot find the certificate

 

On Certificate \ Grant “Control” to User

Let us grant a different privilege; we will replace view definition with grant control on certificate.

Code


--Revoke view definition on certificate
REVOKE VIEW DEFINITION ON CERTIFICATE::[cert_keyProtection] FROM [bookViewer]

--Grant Control Certificate
GRANT CONTROL ON CERTIFICATE::[cert_keyProtection] TO [bookViewer]

Validation Code


set nocount on
set XACT_ABORT on
go

setuser ''
go

setuser 'bookViewer'
go

OPEN SYMMETRIC KEY [key_DataShare] 
   DECRYPTION BY CERTIFICATE cert_keyProtection;
GO

   select 
	  [sourced] = 'sys.openkeys'
	, tblOK.*
   from   sys.openkeys tblOK

   SELECT [encrypted]
	= encryptbykey
              (
                 key_guid('key_DataShare')
                 , 'MyData' 
              )

if exists
	(
	   select *
	   from   sys.openkeys
	   where  key_name = 'key_DataShare'
	)
begin

	CLOSE SYMMETRIC KEY [key_DataShare] 

end
go

setuser ''
go


Output:

validation-code-output

 

Quick Summary

To access a symmetric key, the minimum permissions required are:

  1. On the Symmetric Key, grant View Definition
  2. On the Certificate, grant Control

 

Crediting

Once again crediting Stack Overflow; specifically

  1. Mitolo – Cannot find the symmetric key ‘key_DataShare’, because it does not exist or you do not have permission

 

Listening

Listening to Birds of Tokyo – Lanterns

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s