SQL Server 2005: Yet another column encryption demo – "Clinic"


Here’s another demo I just used for a presentation of SQL Server 2005 encryption features. This shows how multiple keys can be used for encrypting data in a column and also how access to keys can be controlled. It’s a fairly long demo:



— Column Encryption Demo – “Clinic”

use master


— Create two logins that will be used for this demo
— They are logins for two doctors in a clinic

create login Doc1 with password = ‘Yukon90!’
create login Doc2 with password = ‘Yukon90!’


— Create a database that the clinic uses

create database ClinicDB


— Switch to the clinic database

use clinicDB


— Create users for the doctors

create user Doc1
create user Doc2


— Create a table for patient records
— The SSN and health problems of each patient is meant
— to be encrypted. Only the patient’s doctor can see their
— full details. Other doctors can see the patient’s name
— and which doctor is assigned to them

create table PatientTable (
 Id int,
 name nvarchar(30),
 Docname varchar(20),
 SSN varbinary(60),
 Problem varbinary(400))


— Grant access to the table to both doctors

grant select, insert on PatientTable to Doc1
grant select, insert on PatientTable to Doc2


— Create a certificate for each doctor

create certificate Doc1Cert
authorization Doc1 with subject = ‘Doc1cert’


— Notice the error, we need to create a database master key first.
— So lets do that

create master key
ENCRYPTION BY password = ‘ClinicDB1’


— Now let’s create the certificates
— that will protect the data encryption keys

create certificate Doc1Cert
authorization Doc1 with subject = ‘Doc1cert’


create certificate Doc2Cert
authorization Doc2 with subject = ‘Doc2cert’


— View certificates in database

select * from sys.certificates


— Create symmetric keys for each of the doctors

create symmetric key Doc1Key
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc1Cert


create symmetric key Doc2Key
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc2Cert


— Grant permissions on the symmetric keys

grant view definition on symmetric key::Doc1Key to Doc1
grant view definition on symmetric key::Doc2Key to Doc2


— View keys in database

select * from sys.symmetric_keys


 


———————————
— Simulate connecting as Doctor1

execute as login = ‘Doc1’


— Note we cannot open the other doctor’s key

open symmetric key Doc2Key
DECRYPTION BY certificate Doc2cert


— Open symmetric key for usage

open symmetric key Doc1Key
DECRYPTION BY certificate Doc1cert


— View the list of open keys in the session

select * from sys.openkeys


— Use the key to insert doctor’s patient records
— including encrypted values into the table

insert into PatientTable values (
 1,
 ‘Alice’,
 ‘Doc1’,
 encryptByKey(Key_GUID(‘Doc1Key’), ‘111-11-1111’),
 encryptByKey(Key_GUID(‘Doc1Key’), ‘Migraine’))


insert into PatientTable values (
 2,
 ‘Bob’,
 ‘Doc1’,
 encryptByKey(Key_GUID(‘Doc1Key’), ‘222-22-2222’),
 encryptByKey(Key_GUID(‘Doc1Key’), ‘Stomach Ache’))


insert into PatientTable values (
 3,
 ‘Charles’,
 ‘Doc1’,
 encryptByKey(Key_GUID(‘Doc1Key’), ‘333-33-3333’),
 encryptByKey(Key_GUID(‘Doc1Key’), ‘Sore throat’))


— Close all opened keys

close all symmetric keys


— View the list of open keys in the session

select * from sys.openkeys


— Simulate logging out of doctor1’s context

revert


 


———————————
— Simulate connecting as Doctor2

execute as login = ‘Doc2’


— Open symmetric key for usage

open symmetric key Doc2Key
DECRYPTION BY certificate Doc2cert


— View the list of open keys in the session

select * from sys.openkeys


— Use the key to insert doctor’s patient records
— including encrypted values into the table

insert into PatientTable values (
 4,
 ‘Dave’,
 ‘Doc2’,
 encryptByKey(Key_GUID(‘Doc2Key’), ‘444-44-4444’),
 encryptByKey(Key_GUID(‘Doc2Key’), ‘Throat infection’))


insert into PatientTable values (
 5,
 ‘Emily’,
 ‘Doc2’,
 encryptByKey(Key_GUID(‘Doc2Key’), ‘555-55-5555’),
 encryptByKey(Key_GUID(‘Doc2Key’), ‘Asthma’))


insert into PatientTable values (
 6,
 ‘Frank’,
 ‘Doc2’,
 encryptByKey(Key_GUID(‘Doc2Key’), ‘666-66-6666’),
 encryptByKey(Key_GUID(‘Doc2Key’), ‘Eye Pain’))


— Close all opened keys

close all symmetric keys


— Simulate logging out of doctor2’s context

revert


 


— Select from table as an admin

select * from patientTable

— Note how last two columns show up as encrypted text


 


———————————
— Simulate connecting as Doctor1

execute as login = ‘Doc1’


— Open the relevant key for usage

open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert


— Select from the table including decrypting from
— encrypted columns

select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykey(SSN)) as SSN,
 convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable


— Simulate logging out

close all symmetric keys
revert


 


——————————–
— Simulate connecting as Doctor2

execute as login = ‘Doc2’


— Open the relevant key for usage

open symmetric key Doc2Key
DECRYPTION BY certificate Doc2Cert


— Select from the table including decrypting from
— encrypted columns

select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykey(SSN)) as SSN,
 convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable


— Simulate logging out

close all symmetric keys
revert


 


— Now let’s get rid of the need to open the key

create view Doc1PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id(‘Doc1Cert’), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id(‘Doc1Cert’), NULL, Problem)) as Ailment
from PatientTable


create view Doc2PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id(‘Doc2Cert’), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id(‘Doc2Cert’), NULL, Problem)) as Ailment
from PatientTable


— Grant permissions on these views

grant select on Doc1PatientView to Doc1
grant select on Doc2PatientView to Doc2


— These are for demo, will not work

grant select on Doc1PatientView to Doc2
grant select on Doc2PatientView to Doc1


 


———————————
— Simulate connecting as Doctor1

execute as login = ‘Doc1’


— Note no keys are opened

select * from sys.openkeys


— Select from Doc1’s view

select * from Doc1PatientView


— Also note, no keys are left opened

select * from sys.openkeys


— Select from Doc2’s view
— will not decrypt anything

select * from Doc2PatientView


— Simulate logging out

revert


 


———————————
— Simulate connecting as Doctor2

execute as login = ‘Doc2’


— Select from Doc2’s view

select * from Doc2PatientView


— Select from Doc1’s view
— will not decrypt anything

select * from Doc1PatientView


— Simulate logging out

revert


 


— What if we need to share information
— Add an extra key that both doctors have access to
— and encrypt data with it

create symmetric key DocsKey
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc1Cert


open symmetric key DocsKey
DECRYPTION BY certificate Doc1Cert


alter symmetric key DocsKey
add ENCRYPTION BY certificate Doc2Cert


— Insert a common patient record

insert into PatientTable values (
 7,
 ‘George’,
 ‘Doc1 or Doc2’,
 encryptByKey(Key_GUID(‘DocsKey’), ‘777-77-7777’),
 encryptByKey(Key_GUID(‘DocsKey’), ‘Imaginary Disease, likes to visit our clinic’))


close symmetric key DocsKey


— Check the encryptions for the key
— Each one allows one doctor access to the key

select c.name as ‘Encrypted By’ from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk
where ke.thumbprint = c.thumbprint and sk.name = ‘DocsKey’ and ke.key_id = sk.symmetric_key_id;


— Allow both doctors access to the common key

grant view definition on symmetric key::DocsKey to Doc1
grant view definition on symmetric key::DocsKey to Doc2


 


———————————
— Simulate connecting as Doctor1

execute as login = ‘Doc1’


— Select from Doc1’s view

select * from Doc1PatientView


— Simulate logging out

revert


 


———————————
— Simulate connecting as Doctor2

execute as login = ‘Doc2’


— Select from Doc2’s view

select * from Doc2PatientView


— Simulate logging out

revert


 


— How can the admin get access to data without opening each key?
— Create a master certificate that allows access to all keys

create certificate MasterCert
with subject = ‘mastercert’


open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert
open symmetric key Doc2Key
DECRYPTION BY certificate Doc2Cert
open symmetric key DocsKey
DECRYPTION BY certificate Doc1Cert


select * from sys.openkeys


alter symmetric key Doc1Key
add ENCRYPTION BY certificate MasterCert
alter symmetric key Doc2Key
add ENCRYPTION BY certificate MasterCert
alter symmetric key DocsKey
add ENCRYPTION BY certificate MasterCert


close all symmetric keys


select * from sys.openkeys


— Now create a view that automatically decrypts using the master certificate

create view PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id(‘MasterCert’), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id(‘MasterCert’), NULL, Problem)) as Ailment
from PatientTable


select * from PatientView


— these are for the demo; to show they’re not sufficient

grant select on PatientView to Doc1
grant select on PatientView to Doc2


 


———————————
— Note that the doctors can’t use this view

— Simulate connecting as Doctor1

execute as login = ‘Doc1’


— Select from Doc1’s view

select * from PatientView


— Simulate logging out

revert


 


———————————
— Simulate connecting as Doctor2

execute as login = ‘Doc2’


— Select from Doc2’s view

select * from PatientView


— Simulate logging out

revert


 


— Cleanup

use master


drop database ClinicDB
drop login Doc1
drop login Doc2
–EOD

Comments (5)

  1. Bob says:

    Very nice article!  I learned a lot from this.

    Thank you very much

  2. hervema says:

    Excellent example to understand encryption !! Thks a lot.

  3. Hao says:

    Very useful sample.

  4. abashirtx says:

    Very nice article.  One thing I noticed was that sysadmin and dbo were able to use the key and decrypt data.  How can this be prevented.  There can be situations where even the DBA should not have access to some data.  I followed your article in creating the certs and keys and encrypting the data.  However all sysadmins, dbo and the key owner can view the data.  Any way to prevent others from viewing this data?  

    Thanks,

    Dakku

  5. Hi Dakku,

    I touched on what you are asking in the following posts:

    http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx

    http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx

    In a nutshell: the key owner will always be able to see the data encrypted with the key; you can prevent the dbo to access the data by rooting your encryption chain in a password; the last method will usually protect your key from a sysadmin as well, but there is a caveat: sysadmins may be machine admins, in which case they can, for example, debug the server process and catch your password in clear.

    For more details, see the above two posts and if you have any questions after that, let me know. Also, note that this topic has come up in several threads on the SQL Server Security Forum (you can get to this forum from the Microsoft Technical Forums link on my main blog page).