When we use cell level encryption, we usually create a symmetric key, do the encryption and close it. And again when we want to query the data, we open the key, perform the decryption and read the data. Here, in this article, I'll talk about the side effects if you forget/ignore to close the keys you opened for decrypting the data.

Let’s do a simple exercise to understand how we do the encryption and decryption and we’ll try to replicate the scenario I was talking about.

First we’ll create 2 logins to play with and a database followed by table with few columns to perform cell level encryption.

Here we have two managers and they’re storing their employee details like id, name, SSN and rating details in a table called EmpTable. We’re encrypting the SSN and the rating details as they’re confidential and should only be visible to their respective managers.

Create login Man1 with password = 'Man1pwd'

Go

Create login Man2 with password = 'Man2pwd'

Go

 

create database CompanyDB

go

 

use CompanyDB

go

create user Man1;

go

create user Man2

go

 

create table EmpTable (Id int, name nvarchar(30), Manname varchar(20), SSN varbinary(60), Rating varbinary(4000))

go

 

--grant access to the table to both doctors

grant select,insert on EmpTable to Man1

grant select,insert on EmpTable to Man2

 

Then we’ll create a Database master key, two certificates for both the users and two symmetric keys using their respective keys

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CompanyDB1'

 

create certificate Man1Cert authorization Man1 with subject = 'Man1cert', START_DATE = '10/09/2013'

go

 

create certificate Man2Cert authorization Man2 with subject = 'Man2cert', START_DATE = '10/09/2013'

go

 

 

create symmetric key Man1Key authorization Man1 with algorithm = TRIPLE_DES ENCRYPTION BY certificate Man1Cert

go

 

create symmetric key Man2Key authorization Man2 with algorithm = TRIPLE_DES ENCRYPTION BY certificate Man2Cert

go

 

Now we’ll do the encryption

 

 

execute as login = 'Man1'

 

open symmetric key Man1Key decryption by certificate Man1cert

 

 

insert into EmpTable values (1, 'Chris','Man1', encryptByKey(Key_GUID('Man1Key'),'111-11-1111'), encryptByKey(Key_GUID('Man1Key'),'Exceeded Expectations'))

insert into EmpTable values (2, 'Charles','Man1', encryptByKey(Key_GUID('Man1Key'),'222-22-2222'), encryptByKey(Key_GUID('Man1Key'),'Met Expectations’))

insert into EmpTable values (3, 'James','Man1', encryptByKey(Key_GUID('Man1Key'),'333-33-3333'), encryptByKey(Key_GUID('Man1Key'),'Partially Met Expectations'))

 

close all symmetric keys

 

Revert

 

execute as login = 'Man2'

 

open symmetric key Man2Key decryption by certificate Man2cert

 

insert into EmpTable values (991, 'Dave','Man2',encryptByKey(Key_GUID('Man2Key'),'444-44-4444'), encryptByKey(Key_GUID('Man2Key'), 'Partially Met Expectations'))

insert into EmpTable values (1001, 'Dan','Man2',encryptByKey(Key_GUID('Man2Key'),'555-55-5555'), encryptByKey(Key_GUID('Man2Key'), 'Exceeded Expectations'))

insert into EmpTable values (1011, 'Steve','Man2', encryptByKey(Key_GUID('Man2Key'),'666-66-6666'), encryptByKey(Key_GUID('Man2Key'), 'Exceeded Expectations'))

 

close all symmetric keys

 

revert

 

 

If we try to select the data from these two columns which contain encrypted data, it looks like

 

select Id, name, Manname, convert(varchar,decryptbykey(SSN)) as SSN, convert (varchar,decryptbykey(Rating)) as Rating from EmpTable

 

Id     name   Manname SSN    Rating

1      Chris  Man1   NULL   NULL

2      CharlesMan1   NULL   NULL

3      James  Man1   NULL   NULL

4      Dave   Man2   NULL   NULL

5      Dan    Man2   NULL   NULL

6      Steve  Man2   NULL   NULL

Now we’ll try to decrypt the data while querying it.

 

execute as login = 'Man1'

open symmetric key Man1Key decryption by certificate Man1Cert

select Id, name, Manname, convert(varchar,decryptbykey(SSN)) as SSN, convert (varchar,decryptbykey(Problem)) as Rating from EmpTable

 

Id     name   Manname        SSN           Rating

1      Chris   Man1          111-11-1111   Exceeded Expectations

2      Charles Man1          222-22-2222   Met Expectations

3      James   Man1          333-33-3333   Partially Met Expectations

4      Dave    Man2          NULL          NULL

5      Dan     Man2          NULL          NULL

6      Steve   Man2          NULL          NULL

 

If we observe the user1 is seeing NULLs for the data rows that were encrypted by user2

Now we’re not closing the key opened and trying to see the data as user2

 

revert

execute as login = 'Man2'

select Id, name, Manname, convert(varchar,decryptbykey(SSN)) as SSN, convert (varchar,decryptbykey(Problem)) as Rating from EmpTable

 

Id     name   Manname       SSN           Rating

1      Chris  Man1          111-11-1111   Exceeded Expectations

2      CharlesMan1          222-22-2222   Met Expectations

3      James  Man1          333-33-3333   Partially Met Expectations

4      Dave   Man2          NULL          NULL

5      Dan    Man2          NULL          NULL

6      Steve  Man2          NULL          NULL

 

So, user2 is able to see the data that was encrypted by user1.

This is because, the keys that is required to decrypt this data is still open. And even though the login Doc 2 does not have the permission can still see the data that was encrypted using the Man1’s keys. This is only possible on the same session with impersonation.

 The query processor don’t validate if the owner of the key or the user who encrypted the data is doing the decryption or not. i.e. if a key, protecting the data/used to encrypt the data is open, whoever issues a select query against the encrypted columns will get the data in decrypted form, in the same session.

The point here is that when the keys are opened in a session and if there is a login or user where the caller has IMPERSONATE permissions on the another login/User, and if he changes the execution context to the new login, which really does not have permissions on the keys used for encryption, will still be able to see the data if the keys were not closed before changing the context. As in the above example.

The better way of changing the execution context in such cases where you do not want the data to be read would be to close the keys and then change the execution context as shown below:

execute as login = 'Man1'

open symmetric key Man1Key decryption by certificate Man1Cert

select Id, name, Manname, convert(varchar,decryptbykey(SSN)) as SSN, convert (varchar,decryptbykey(Problem)) as Rating from EmpTable

 

Id     name   Manname       SSN           Rating

1      Chris  Man1          111-11-1111   Exceeded Expectations

2      CharlesMan1          222-22-2222   Met Expectations

3      James  Man1          333-33-3333   Partially Met Expectations

4      Dave   Man2          NULL          NULL

5      Dan    Man2          NULL          NULL

6      Steve  Man2          NULL          NULL

 

close all symmetric keys

 

Revert

 

execute as login = 'Man2'

select Id, name, Manname, convert(varchar,decryptbykey(SSN)) as SSN, convert (varchar,decryptbykey(Problem)) as Rating from EmpTable

 

Id          name        Manname    SSN      Rating

----------- ----------- ---------- -------- -----------

1           Chris       Man1       NULL     NULL

2           Charles     Man1       NULL     NULL

3           James       Man1       NULL     NULL

991         Dave        Man2       NULL     NULL

1001        Dan         Man2       NULL     NULL

1011        Steve       Man2       NULL     NULL

(6 row(s) affected)

 

So it’s always suggestible to close your keys after reading the data, especially in the environments where same session’s state is shared by multiple client connections/users.

p.s. This feature is also useful in cases where a user want to share some/all of his data with the other users. For e.g. Manager1 want to share some/all of his employee’s details with other Managers. In this case instead of sharing his keys to those guys, he can just open his key and can allow others to read data from his session. He can close his key after the other guys completed querying the data.

 

As always, please feel free to get in touch with me with your comments or feedback.

Thanks,

Chandra