如何做对称加密解密？？？？

Question

请帮忙看一下下面的代码哪里有问题，为何解密后的数据为空呢？？？
use master 
if exists(select * from sys.databases where name='HumanResources')
drop database HumanResourcesgo
create database HumanResources
go
use HumanResourcesif exists(select * from sys.objects where name='Employee')
drop table Employeecreate table Employee
(
id int identity(1,1),
Numbers nvarchar(20)
)
go
insert into Employee values('q')
insert into Employee values('sdfad')
insert into Employee values('asdf')
insert into Employee values('asdf')
insert into Employee values('dsaf')
insert into Employee values('adsf')
insert into Employee values('gfds')
insert into Employee values('dsfd')select * from Employee
go
USE HumanResources;
GO--If there is no master key, create one now 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GOCREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GOCREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GOUSE HumanResources;
GO-- Create a column in which to store the encrypted data
--ALTER TABLE Employee
--    ADD EncryptedNationalIDNumber varbinary(128); 
GO-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;-- Encrypt the value in column NationalIDNumber with symmetric 
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Employee
SET Numbers = EncryptByKey(Key_GUID('SSN_Key_01'), Numbers);
GO-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO-- Now list the original ID, the encrypted ID, and the 
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT Numbers FROM Employee;
GOUPDATE EmployeeSET Numbers = DecryptByKey(Numbers);select * from Employee

Answer 1

转贴自teched讲师:  牛可  基本概念:第一层 服务主密钥 备份服务主密钥
backup service master key to file='c:\smk.bak'
encryption by password='P@ssw0rd'
 
restore service master key from file='c:\smk.bak'
decryption by password='P@ssw0rd'
 
第二层 数据库主密钥
1)必须先在该数据库上创建数据库主密钥才能使用
create master key encryption by password='P@ssw0rd'
 
2)使用数据库主密钥
－如果数据库主密钥使用服务密钥进行保护，则在使用时会自动打开
opren master key decryption by password='P@ssw0rd'
 
3)查看数据库主密钥状态
sys.symmetric_keys
 
4)备份数据库主密钥
backup master key to file='c:\smk.bak'
encryption by password='P@ssw0rd'
 
restore master key from file='c:\smk.bak'
decryption by password='P@ssw0rd'
 
 
数字证书
创建自签名
create certificate cert_myCert
encryption by password='P@ssw0rd'
with subject='Self Signed Cert',
start_date='1/31/2006'
expiry_date='1/31/2008'
 
 
非对称密钥
创建新的密钥对
create asymmetric key asy_Key1
with algorithm=RSA_2048
encryption by password='P@ssw0rd'
 
 
对称密钥
创建新的密钥对
create symmetric key SymKeyMarketing3
with algorithm=AES_2048
encryption by certificate asy_Key1
 
使用对称密钥
使用前必须打开
open symmetric SymKeyMarketing3
decryption by certificate asy_Key1
 
sys.open_keys
 
 
数据列加密
－使用对称密钥加密大量的列数据
－考虑使用证书，非对称密钥保护对称密钥
 
防止绕过加密数据列的攻击－使用验证器
 
注:
在加密列上的索引将变得无效
加密数据列的长度增长，建议使用varbinary（max）数据类型
修改已有的dml语句以支持加密的数据列
 
-----***********示例1 了解数据库加密体系结构*****-----
 
--************(1) 服务主密钥
--准备工作
--创建测试数据库TestDB
--1)备份服务主密钥
backup service master key to file='g:\smk.bak'
encryption by password='p@ssw0rd'
 
--2)生成新的主密钥
Alter service master key regenerate
 
--3)从备份文件还原服务主密钥
Restore service master key from file= file='g:\smk.bak'
encryption by password='p@ssw0rd'
 
--*************(2) 数据库主密钥
--1)为数据库创建数据库主密钥
create master key encryption by password='p@ssw0rd'
go
--2)查看数据库加密状态
select [name],is_master_key_encrypted_by_server 
from sys.databases where name='TestDB'
 
--3)查看数据库主密钥的信息
select * from sys.symmetric_keys 
 
--4)备份数据库主密钥
backup master key
to file='g:\testdbkey.bak'
encryption by password='p@ssw0rd'
 
--5)删除服务主密钥对数据库主密钥的保护
--创建非对称密钥成功,自动使用服务主密钥解密并使用该数据库主密钥
create asymmetric key asy_Testkey1 with algorithm=RSA_1024
go
--删除服务主密钥对数据库主密钥的保护
alter master key
drop encryption by service master key
go
 
--查看数据库加密状态
select [name],is_master_key_encrypted_by_server 
from sys.databases where name='TestDB'
 
--创建非对称密钥失败,因为数据库主密钥未打开
create asymmetric key asy_Testkey2 with algorithm=RSA_1024
go
--打开数据库主密钥
open master key decryption by password='p@ssw0rd'
select * from sys.openkeys
go
--创建非对称密钥成功
create asymmetric key asy_Testkey2 with algorithm=RSA_1024
go
 
--恢复服务主密钥对数据库主密钥的保护
alter master key
add encryption by service master key
close master key
go
 
--*****(3)证书
--1)创建自签名证书
create certificate cert_Testcert
encryption by password='p@ssw0rd'
with subject='TestCert1',
start_date='1/31/2006',
expiry_date='1/31/2008'
go
select * from sys.certificates
 
 
--2)从文件导入证书
Create certificate cert_TestCert2
From file=’g:\MSCert.cer’
Go
 
-- 3)备份导出证书和密钥
backup certificate cert_Testcert
to file='g:\Testcert.cer'
with private key
(decryption by password='p@ssw0rd',
file='g:\TestCert_pvt',--私密钥
encryption by password='p@ssw0rd' )
go
 
--4)使用证书加解密数据
--加密:使用证书的公钥
declare @cleartext varbinary(200)
declare @cipher varbinary(200)
set @cleartext=convert( varbinary(200),'Test text string')
set @cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)
select @cipher
 
--解密:使用证书的私钥
select convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd')) as [cleartext]
 
 
--5) 删除证书私钥
alter certificate cert_TestCert
remove private key
go
 
--加密成功
declare @cleartext varbinary(200)
declare @cipher varbinary(200)
set @cleartext=convert( varbinary(200),'Test text string')
set @cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)
select @cipher
 
--解密失败:因为私钥被删除
select convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd')) as [cleartext]
 
 
--***(4) 非对称密钥
--1)使用sn.exe生成非对称密钥,安装vs2005后有sn.exe, 在命令行方式下执行
sn -k g:\asy_Test.key
 
--2)从文件创建非对称密钥
create asymmetric key asm_Test
from file='g:\asy_Test.key'
encryption by password='p@ssw0rd'
go
 
select * from sys.asymmetric_keys
 
 
 
--***********示例2 使用加密保护列数据
-----*****(1) 准备工作
--1) 创建示例表
create table empsalary
(EmpID int,
 Title nvarchar(50),
 Salary varbinary(500)
)
go
 
--2) 创建数据库主密钥
create master key encryption by password='p@ssw0rd'
go
 
--3) 创建用于加密的对称密钥
create symmetric key sym_Salary
with algorithm=AES_192
encryption by password='p@ssw0rd'
go
 
select * from sys.symmetric_keys where [name]='sym_Salary'
 
------****(2)加密列数据
--1)打开对称密钥
open symmetric key sym_Salary
decryption by password='p@ssw0rd'
go
select * from sys.openkeys
 
--2)向表中插入数据，并对salary列加密
insert into empsalary values (1,'CEO',EncryptByKey(KEY_GUID('sym_Salary'),'20000'))
insert into empsalary values (2,'Manager',EncryptByKey(KEY_GUID('sym_Salary'),'10000'))
insert into empsalary values (3,'DB Admin',EncryptByKey(KEY_GUID('sym_Salary'),'5000'))
 
--3)关闭打开的对称密钥
close symmetric key sym_Salary
go
select * from sys.openkeys
 
 
--4) 查看表中的数据
select * from empsalary
 
--(3)解密并访问被加密的数据列
--1)打开对称密钥
open symmetric key sym_Salary decryption by password='p@ssw0rd'
go
--2)使用对称密钥解密并访问被加密的列
select empid,title,cast(DecryptByKey(Salary) as varchar(20)) as salary from empsalary
 
--3) 关闭对称密钥
close symmetric key sym_Salary
go
 
--(4)绕过加密数据的攻击
--1)攻击者使用其他数据行的加密数据替换某一行的数据
update empsalary 
set salary=(select salary from empsalary where empid=1)
where empid=3
 
--2)查看被攻击后解密的数据
open symmetric key sym_Salary decryption by password='p@ssw0rd'
select empid,title,cast(DecryptByKey(Salary) as varchar(20)) as salary from empsalary
close symmetric key sym_Salary
 
 
--(5)使用验证器防止绕过加密数据的攻击
--1)删除前面添加的数据行
delete empsalary
 
--2)向表插入数据，并对salary列的数据使用验证器进行加密
open symmetric key sym_Salary decryption by password='p@ssw0rd'
insert into empsalary values (1,'CEO',EncryptByKey(KEY_GUID('sym_Salary'),'20000',1,'1'))
insert into empsalary values (2,'Manager',EncryptByKey(KEY_GUID('sym_Salary'),'10000',1,'2'))
insert into empsalary values (3,'DB Admin',EncryptByKey(KEY_GUID('sym_Salary'),'5000',1,'3'))
 
--3)解密并访问被加密的数据
select empid,title,cast(DecryptByKey(Salary,1,cast(empid as varchar(3))) as varchar(20)) as salary from empsalary
 
--4)用同样的方法篡改数据
update empsalary 
set salary=(select salary from empsalary where empid=1)
where empid=3
 
--5)被篡改后的加密了的数据列变成无效
select empid,title,cast(DecryptByKey(Salary,1,cast(empid as varchar(3))) as varchar(20)) as salary from empsalary
 
 
 
--***********示例3 使用数字证书签署存储过程
 
--*****(1)准备
--1)创建数据库主密钥
create master key encryption by password='p@ssw0rd'
 
--2)创建签署存储过程所需要的证书
create certificate cert_Product
with subject='Products Sign',
start_date='1/31/2006',
expiry_date='1/31/2008'
go
 
--3)创建SPDeveloper登录帐户和用户，该用户创建并访问Products表的存储过程
create login [SPDeveloper] with password='p@ssw0rd',default_database=[TestDB]
go
create user [SPDeveloper] for login SPDeveloper with default_schema=[SPDeveloper]
go
create schema products authorization SPDeveloper
go
exec sp_addrolemember @rolename='db_owner',@membername='SPDeveloper'
 
--4)以SPDeveloper的身份创建存储过程products.usp_Products
execute as user='SPDeveloper'
go
create procedure products.usp_Products
as
select * from dbo.Products
go
 
revert
select user
 
--5)创建普通用户jerry
create login [jerry] with password='p@ssw0rd',default_database=[TestDB]
go
create user [jerry] for login jerry 
go
 
--*******(2)使用证书签署存储过程
--1)授予用户jerry执行存储过程的权限
grant execute on products.usp_Products to jerry
 
--2)以jerry的身份执行存储过程失败，因为拥有权链的断裂的
execute as user='jerry'
select user
go
 
execute products.usp_Products
go
revert
 
--3)使用证书在当前数据库创建用户ProductsReader,并为该用户赋予读取products表的权限
create user ProductsReader for certificate cert_Products
go
grant select on products To ProductsReader
 
--4)使用证书签署当前存储过程
add signature to products.usp_Products by certificate cert_Products
 
--5)以jerry的身份重新执行存储过程，成功
--因为存储过程将以ProductsReader的权限上下文执行
execute as user='jerry'
select user
go
 
execute products.usp_Products
go
revert
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/cuoguo1111/archive/2006/11/29/1419515.aspx
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574249.aspx