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 Doc1create 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 Doc1grant 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 keyENCRYPTION 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 Doc1grant 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 Doc2KeyDECRYPTION BY certificate Doc2cert
-- Open symmetric key for usage--open symmetric key Doc1KeyDECRYPTION 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
-- Simulate logging out of doctor1's context--revert
----------------------------------- Simulate connecting as Doctor2--execute as login = 'Doc2'
-- Open symmetric key for usage--open symmetric key Doc2KeyDECRYPTION BY certificate Doc2cert
-- 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'))
-- 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
-- Open the relevant key for usage--open symmetric key Doc1KeyDECRYPTION 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 keysrevert
---------------------------------- Simulate connecting as Doctor2--execute as login = 'Doc2'
-- Open the relevant key for usage--open symmetric key Doc2KeyDECRYPTION BY certificate Doc2Cert
-- Now let's get rid of the need to open the key--create view Doc1PatientViewasselect 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 Doc2PatientViewasselect 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 Doc1grant select on Doc2PatientView to Doc2
-- These are for demo, will not work--grant select on Doc1PatientView to Doc2grant select on Doc2PatientView to 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
-- Select from Doc2's view--select * from Doc2PatientView
-- Select from Doc1's view-- will not decrypt anything--select * from Doc1PatientView
-- 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 DocsKeyDECRYPTION BY certificate Doc1Cert
alter symmetric key DocsKeyadd 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 skwhere 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 Doc1grant view definition on symmetric key::DocsKey to Doc2
-- 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 Doc1KeyDECRYPTION BY certificate Doc1Certopen symmetric key Doc2KeyDECRYPTION BY certificate Doc2Certopen symmetric key DocsKeyDECRYPTION BY certificate Doc1Cert
select * from sys.openkeys
alter symmetric key Doc1Keyadd ENCRYPTION BY certificate MasterCertalter symmetric key Doc2Keyadd ENCRYPTION BY certificate MasterCertalter symmetric key DocsKeyadd ENCRYPTION BY certificate MasterCert
close all symmetric keys
-- Now create a view that automatically decrypts using the master certificate--create view PatientViewasselect 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 Doc1grant 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
-- Select from Doc2's view--select * from PatientView
-- Cleanup--use master
drop database ClinicDBdrop login Doc1drop login Doc2--EOD