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).

Skip to main content