日期:2014-05-16 浏览次数:20618 次
--create database A
--go
--create database B
go
use a
go
----------创建主密钥
create master key encryption by password=N'Passw0rd'
go
---------打开主密钥
open master key decryption by password=N'Passw0rd'
go
--------创建证书
create certificate cer_T1
Encryption by password=N'Passw0rd'
with
subject='My Certificate',
start_date='2014-01-01',
expiry_date='2200-01-01'
go
--------创建对称加密(使用证书加密)
create symmetric key sym_T1
with algorithm=AES_256
Encryption by Certificate cer_T1
go
---------创建数据表
if OBJECT_ID('T1','U') is not null drop table t1
go
create table T1
(
ID int,
name varchar(100),
EncCol varbinary(max)
)
go
---------打开对称加密
open symmetric key sym_t1
decryption by certificate cer_t1
with password =N'Passw0rd'
go
---------插入加密数据
insert into T1(ID,name,EncCol)
select '01','Name1',ENCRYPTBYKEY(KEY_GUID(N'sym_t1'),'Name1')
union all
select '02','Name2',ENCRYPTBYKEY(KEY_GUID(N'sym_t1'),'Name2')
go
---------关闭对称加密
close symmetric key sym_t1
go
--------再次打开对称加密
open symmetric key sym_t1
decryption by certificate cer_t1
with password=N'Passw0rd'
go
--------把加密后的数据放到另外一个数据库
select * into B..tt1 from t1
go
--------解密数据
select
ID,
name,
EncCol,
convert(varchar(100),DECRYPTBYKEY(EncCol))
from T1
----解密成功
--ID name EncCol (No column name)
--1 Name1 0x00EF9EF3E8CF1D4C9C447BA99170BCD001000000124F6F592845006459B2E6F61FCBDA7226DA80C3BA0550B5CDD5173CDBD61CD5 Name1
--2 Name2 0x00EF9EF3E8CF1D4C9C447BA99170BCD001000000C8600345E10E62509E01B8DFC9B7A66B7F402406A47C0B230DD8C85D4C104DFE Name2
----备份主密钥
go
backup master key to file ='D:\mk.bak'
encryption by password='Passw0rd'
go
----备份证书
backup certificate cer_t1
to file ='D:\cer_t1.bak' --证书备份路径
with private key (
File='D:\cer_t1_PK.bak', --证书私钥文件路径,用来解密
encryption by password=N'Passw0rd', --加密私钥
decryption by password=N'Passw0rd'
)
--drop symmetric key sym_t1
--go
--drop certificate cer_t1
--go
--drop master key
--drop symmetric key sym_t1
--go
--drop certificate cer_t1
--go
--drop master key
go
use B
go
---还原主密钥
restore master key from file='D:\mk.bak'
decryption by password=N'Passw0rd'
encryption by password=N'Passw0rd'
go
----打开主密钥
open master key decryption by password=N'Passw0rd'
go
----加载证书
CREATE CERTIFICATE cer_t1
FROM FILE = 'D:\cer_t1.bak'
WITH PRIVATE KEY (FILE = 'D:\cer_