--創建實驗用數據庫
use master
if exists(select [name] from sys.databases where [name] = 'sales')
 drop database sales
create database sales
if exists(select principal_id from sys.server_principals where [name] = 'ryan' and [type] = 's')
 drop login ryan
create login ryan with password = '[email protected]'
if exists(select principal_id from sys.server_principals where [name] = 'teddy' and [type] = 's')
 drop login teddy
create login teddy with password = '[email protected]'
go

--創建用戶ryan，并創建數據庫主密鑰
use sales
if exists(select * from sys.database_principals where [name] = 'ryan' and [type] = 's')
 drop user ryan
create user ryan for login ryan with default_schema = dbo
go
if exists(select * from sys.database_principals where [name] = 'teddy' and [type] = 's')
 drop user teddy
create user teddy for login teddy with default_schema = dbo
go
create master key encryption by password = '[email protected]'
go
--使用服務主密鑰加密數據庫主密鑰，
--在此刪除，因為發現數據庫主密鑰創建時默認及利用服務主密鑰加密
--利用服務主密鑰加密的數據庫主密鑰稱為自動密鑰管理
--可以利用以下查詢語句是否啟用數據庫主密鑰的自動密鑰管理
select [name], is_master_key_encrypted_by_server from sys.databases where [name] = 'sales'
--以下語句用于啟用數據庫主密鑰的自動管理
--alter master key add encryption by service master key
go

--為ryan創建證書
if exists(select [name] from sys.certificates where [name] = 'cert_ryan')
 drop certificate cert_ryan
create certificate cert_ryan authorization ryan
 --encryption by password = '[email protected]'
   --建議不要使用密碼，因為經過測試，是用密碼的證書是利用密碼保護，
   --而非數據庫主密鑰，可用以下語句測試證書的加密方法
   --select [name], pvt_key_encryption_type_desc from sys.certificates
   --where [name] = 'cert_db'
 with subject = 'certificate for database',
 start_date = '01/01/2006',
 expiry_date = '12/31/2015'
go
--為teddy創建證書
if exists(select [name] from sys.certificates where [name] = 'cert_teddy')
 drop certificate cert_teddy
create certificate cert_teddy authorization teddy
 --encryption by password = '[email protected]'
   --建議不要使用密碼，因為經過測試，是用密碼的證書是利用密碼保護，
   --而非數據庫主密鑰，可用以下語句測試證書的加密方法
   --select [name], pvt_key_encryption_type_desc from sys.certificates
   --where [name] = 'cert_db'
 with subject = 'certificate for database',
 start_date = '01/01/2006',
 expiry_date = '12/31/2015'
go

select * from sys.certificates

--為ryan和teddy分別創建利用證書保護的對稱密碼
create symmetric key key_sym_ryan authorization ryan
 with algorithm = triple_des
 encryption by certificate cert_ryan
go
create symmetric key key_sym_teddy authorization teddy
 with algorithm = triple_des
 encryption by certificate cert_teddy
go

--創建測試用表
if exists(select [name] from sys.tables where [name] = 'encryption')
 drop table encryption
create table dbo.encryption
(
 pt nchar(10),       --plain text
 et varbinary(128),  --encrypted text
)
go
grant select, insert on encryption to ryan
grant select, insert on encryption to teddy

--完成準備工作，開始測試加密
execute as login = 'ryan'
open symmetric key key_sym_ryan decryption by certificate cert_ryan
insert into encryption
 values (n'ryan',encryptbykey(key_guid('key_sym_ryan'), n'ryan'))
close all symmetric keys
revert

execute as login = 'teddy'
open symmetric key key_sym_teddy decryption by certificate cert_teddy
insert into encryption
 values (n'teddy',encryptbykey(key_guid('key_sym_teddy'), n'teddy'))
close all symmetric keys
revert

--測試數據已經被加密
select * from encryption

--解密數據
execute as login = 'ryan'
open symmetric key key_sym_ryan decryption by certificate cert_ryan
select pt, convert(nchar,decryptbykey(et)) as et from encryption
close all symmetric keys
revert
execute as login = 'teddy'
open symmetric key key_sym_teddy decryption by certificate cert_teddy
select pt, convert(nchar,decryptbykey(et)) as et from encryption
close all symmetric keys
revert