SQL Server 2005加密体系(二)

--创建实验用数据库
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