SQL Server 2005: column encryption demo part 2

I modified the last column encryption demo by protecting the encryption key with a certificate instead of using a password. The advantage to this approach is that a password is no longer needed when opening the key. The advantage of the password approach was the additional protection coming from the fact that only the user who knew the password could use the key. In this version of the demo, any db_owner can use the key.

-- Goals of this demo:
-- Show how a column can be encrypted and decrypted without using passwords
-- The encryption key will be encrypted using a certificate instead of a password

create database demo;

use demo;

-- create the database master key
create master key encryption by password = 'Pufd&s@))%';

-- create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

-- create a certificate to protect the symmetric key that will encrypt the data
-- the certificate will be encrypted by the database master key
create certificate cert_sk_admin with subject = 'Certificate for accessing symmetric keys - for use by admin';
-- create a second certificate that will be used by another user (Charles) to get access to the key
create certificate cert_sk_charles with subject = 'Certificate for accessing symmetric keys - for use by Charles';

-- create a key to protect the employee sensitive data, in this case - the salary
create symmetric key sk_employees with algorithm = aes_192 encryption by certificate cert_sk_admin;

-- open the key so that we can use it
open symmetric key sk_employees decryption by certificate cert_sk_admin;

-- verify key was opened
select * from sys.openkeys;

-- add another encryption to the key using the certificate we created for Charles
alter symmetric key sk_employees add encryption by certificate cert_sk_charles;

-- check the catalog to see the list of certificates that encrypt our key
select c.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and sk.name = 'sk_employees' and ke.key_id = sk.symmetric_key_id;

-- insert some data
-- we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees values (101, 'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000', 1, '101'));
insert into t_employees values (102, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000', 1, '102'));

-- see the result; salary is encrypted
select * from t_employees;

-- create a view to automatically do the decryption
-- note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

-- create another view for charles that will automatically open the key using his certificate and do the decryption
create view v_employees_auto as select id, name, convert(varchar(10), decryptbykeyautocert(cert_id('cert_sk_charles'), NULL, salary, 1, convert(varchar(30), id))) as salary from t_employees;

-- see the result, the decrypted data is available
select * from v_employees;

-- now close the key
close all symmetric keys;

-- verify key was closed
select * from sys.openkeys;

-- use the view that will automatically open the key
select * from v_employees_auto;

-- note that the key is also automatically closed by the above query
-- it is only opened for the duration of the query
select * from sys.openkeys;

-- also note that if the key is not opened, the v_employees view will not be able to decrypt the data
select * from v_employees;

-- create a login and user for Charles
create login charles with password = 'Pufd&s@))%';
create user charles;

-- grant control on the certificate to charles
grant control on certificate::cert_sk_charles to charles;
grant view definition on symmetric key::sk_employees to charles;
grant select on v_employees to charles;
grant select on v_employees_auto to charles;

-- impersonate charles
execute as login = 'charles';

-- verify context
select suser_sname();
select user_name();

-- select from view that will open the key
select * from v_employees_auto;

-- open key
open symmetric key sk_employees decryption by certificate cert_sk_charles;

-- now we can use the v_employees view as well
select * from v_employees;

-- close key
close all symmetric keys;

-- revert impersonation of charles
revert;

-- if we ever want to prevent charles from accessing the data,
-- we can just remove the encryption of the symmetric key
-- and revoke view definition on the key
-- any of these is sufficient, but doing both is a better cleanup
-- charles may continue to use the certificate for other purposes
-- (maybe to access some other key)
open symmetric key sk_employees decryption by certificate cert_sk_admin;
alter symmetric key sk_employees drop encryption by certificate cert_sk_charles;
close symmetric key sk_employees;
revoke view definition on symmetric key::sk_employees to charles;
-- we can also revoke permission to access the views if we don't want Charles to use them anymore
revoke select on v_employees to charles;
revoke select on v_employees_auto to charles;

-- cleanup
drop user charles;
drop login charles;
drop view v_employees;
drop view v_employees_auto;
delete from t_employees;
drop table t_employees;
drop symmetric key sk_employees;
drop certificate cert_sk_charles;
drop certificate cert_sk_admin;

use master;

drop database demo;
-- EOD