use master
--Create two logins that will be used for this demo
--Essentially logins for two doctors in a clinic
Create login Doc1 with password = 'Yukon90'
go
Create login Doc2 with password = 'Yukon90'
go
--Create a database that the clinic uses
create database ClinicDB
go
--switch to the clinic database
use clinicDB
go
--create users for to the doctors
create user Doc1
go
create user Doc2
go
--create a table for patient records
--The SSN and health problems of each patient is meant
--to be encrypted. Only the patients doctor can see their
--full details. Other doctors can see the patients name
--and which doctor is assigned to them
create table PatientTable (Id int, name nvarchar(30), 
Docname varchar(20), SSN varbinary(60), 
Problem varbinary(4000))
go
--grant access to the table to both doctors
grant select,insert on PatientTable to Doc1
grant select,insert on PatientTable to Doc2

--Now to all the encryption stuff.
--Create a certificate for each doctor
create certificate Doc1Cert 
authorization Doc1 with subject = 'Doc1cert'
go
--Notice the error, we need to create a database master key first. 
--So lets do that.
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'ClinicDB1'
--Now lets create the certificates
create certificate Doc1Cert 
authorization Doc1 with subject = 'Doc1cert'
go

create certificate Doc2Cert 
authorization Doc2 with subject = 'Doc2cert'
go
--create symmetric keys for each of the doctors

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

create symmetric key Doc2Key 
authorization Doc2 
with algorithm = TRIPLE_DES 
ENCRYPTION BY certificate Doc2Cert
go
--View the list of keys in the database
select * from sys.symmetric_keys

--Simulate connecting as Doctor1
execute as login = 'Doc1'
--open your symmetric key for usage
open symmetric key Doc1Key using certificate Doc1cert
--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

--use the key to insert doctor's patient records
--including encrypted values into the table
insert into PatientTable values (1, 'Girish','Doc1', 
encryptByKey(Key_GUID('Doc1Key'),'111-11-1111'), 
encryptByKey(Key_GUID('Doc1Key'),'Migraine'))

insert into PatientTable values (2, 'Tanmoy','Doc1', 
encryptByKey(Key_GUID('Doc1Key'),'222-22-2222'), 
encryptByKey(Key_GUID('Doc1Key'),'Stomach Ache'))

insert into PatientTable values (3, 'James','Doc1', 
encryptByKey(Key_GUID('Doc1Key'),'333-33-3333'), 
encryptByKey(Key_GUID('Doc1Key'),'Insanity'))

--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 your symmetric key for usage
open symmetric key Doc2Key using certificate Doc2cert
--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

--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, 'Istvan','Doc2',
encryptByKey(Key_GUID('Doc2Key'),'555-55-5555'), 
encryptByKey(Key_GUID('Doc2Key'),'Asthma'))

insert into PatientTable values (6, 'Al','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 doctor1's context
revert

--select from table as an admin
select * from patientTable
--notice how last two columns show up as encrypted text

--simulate connecting as Doc1
execute as login = 'Doc1'
--open the relevant keys for usage
open symmetric key Doc1Key using 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 Doc1
execute as login = 'Doc2'
--open the relevant keys for usage
open symmetric key Doc2Key using 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

