Why is it important to close the keys after retrieving the data from an encrypted table, using Cell level encryption?
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