对存储过程进⾏加密和解密（SQL2008SQL2012）
开始：
在⽹络上，看到有SQL Server 2000和SQL Server 2005 的存储过程加密和解密的⽅法，后来分析了其中的代码，发现它们的原理都是⼀样的。后来⾃⼰根据实际的应⽤环境，编写了两个存储过程，⼀个加密存储过程（sp_EncryptObject），和⼀个解密存储过程
（sp_EncryptObject），它们可以应⽤于SQL Server中的储过程，函数，视图，以及触发器。
感觉这两个存储过程蛮有意思的，拿来与⼤家分享；如果你看过类似的，就当作重温⼀下也好。
⽤于加密的存储过程 （sp_EncryptObject） ：
存储过程（sp_EncryptObject）加密的⽅法是在存储过程，函数，视图的“As”位置前加上“with encryption”；如果是触发器，就
在“for”位置前加“with encryption”。
如果触发器是{ AFTER | INSTEAD OF} 需要修改下⾯代码"For"位置：
if objectproperty(object_id(@Object),'ExecIsAfterTrigger')=0t@Replace='As' ; el t@Replace='For ';
存储过程完成代码：
View Code
U master
Go
if object_ID('[sp_EncryptObject]') is not null
Drop Procedure[sp_EncryptObject]
Go
create procedure sp_EncryptObject
(
@Object sysname='All'
)
as
/*
当@Object=All的时候，对所有的函数，存储过程，视图和触发器进⾏加密
hunband调⽤⽅法：
1. Execute sp_EncryptObject 'All'
2. Execute sp_EncryptObject 'ObjectName'
*/
begin
t nocount on
if@Object<>'All'
begin
if not exists(lect1from sys.objects a where a.object_id=object_id(@Object) pe in('P','V','TR','FN','IF','TF'))
begin
--SQL Server 2008
rairror50001 N'⽆效的加密对象！加密对象必须是函数，存储过程，视图或触发器。'
--SQL Server 2012
atmel--throw 50001, N'⽆效的加密对象！加密对象必须是函数，存储过程，视图或触发器。',1
return
end
if exists(lect1from sys.sql_modules a where a.object_id=object_id(@Object) and a.definition is null)
begin
--SQL Server 2008
--SQL Server 2008
rairror50001 N'对象已经加密！'
--SQL Server 2012
--throw 50001, N'对象已经加密！',1
return
end
end
declare@sql nvarchar(max),@C1nchar(1),@C2nchar(1),@type nvarchar(50),@Replace nvarchar(50)
t@C1=nchar(13)
t@C2=nchar(10)
declare cur_Object
cursor for
lect object_name(a.object_id) As ObjectName,a.definition
from sys.sql_modules a
inner join sys.objects b on b.object_id=a.object_id
and b.is_ms_shipped=0
and not exists(lect1
ded_properties x
where x.major_id=b.object_id
and x.minor_id=0
and x.class=1
and x.name='microsoft_databa_tools_support'
)
pe in('P','V','TR','FN','IF','TF')
and (b.name=@Object or@Object='All')
and b.name <>'sp_EncryptObject'
and a.definition is not null
order by Ca
pe ='V'then1
pe ='TR'then2
pe in('FN','IF','TF') then3
ate_date,b.object_id
open cur_Object
hadfetch next from cur_Object into@Object,@sql
while@@fetch_status=0
coast
begin
Begin Try
if objectproperty(object_id(@Object),'ExecIsAfterTrigger')=0t@Replace='As' ; el t@Replace='For ';
if (patindex('%'+@C1+@C2+@Replace+@C1+@C2+'%',@sql)>0)
begin
t@sql=Replace(@sql,@C1+@C2+@Replace+@C1+@C2,@C1+@C2+'With Encryption'+@C1+@C2+@Replace+@C1+@C2) end
el if(patindex('%'+@C1+@Replace+@C1+'%',@sql)>0)
begin
t@sql=Replace(@sql,@C1+@Replace+@C1,@C1+'With Encryption'+@C1+@Replace+@C1)
end
el if(patindex('%'+@C2+@Replace+@C2+'%',@sql)>0)
explodebegin
t@sql=Replace(@sql,@C2+@Replace+@C2,@C2+'With Encryption'+@C2+@Replace+@C2)
end
el if(patindex('%'+@C2+@Replace+@C1+'%',@sql)>0)
begin
妈妈英文t@sql=Replace(@sql,@C2+@Replace+@C1,@C1+'With Encryption'+@C2+@Replace+@C1)
end
el if(patindex('%'+@C1+@C2+@Replace+'%',@sql)>0)
begin
t@sql=Replace(@sql,@C1+@C2+@Replace,@C1+@C2+'With Encryption'+@C1+@C2+@Replace)
t@sql=Replace(@sql,@C1+@C2+@Replace,@C1+@C2+'With Encryption'+@C1+@C2+@Replace)
end
el if(patindex('%'+@C1+@Replace+'%',@sql)>0)
begin
t@sql=Replace(@sql,@C1+@Replace,@C1+'With Encryption'+@C1+@Replace)
end
el if(patindex('%'+@C2+@Replace+'%',@sql)>0)
begin
t@sql=Replace(@sql,@C2+@Replace,@C2+'With Encryption'+@C2+@Replace)
end
t@type=
ca
when object_id(@Object,'P')>0then'Proc'
when object_id(@Object,'V')>0then'View'
when object_id(@Object,'TR')>0then'Trigger'
when object_id(@Object,'FN')>0or object_id(@Object,'IF')>0or object_id(@Object,'TF')>0then'Function'
end
t@sql=Replace(@sql,'Create '+@type,'Alter '+@type)
Begin Transaction
exec(@sql)
print N'已完成加密对象('+@type+')：'+@Object
Commit Transaction
End Try
Begin Catch
Declare@Error nvarchar(2047)
Set@Error='Object: '+@Object+@C1+@C2+'Error: '+Error_message()
Rollback Transaction
print@Error
print@sql
End Catch
fetch next from cur_Object into@Object,@sql
end
clo cur_Object
deallocate cur_Object
end
Go
exec sp_ms_marksystemobject 'sp_EncryptObject'--标识为系统对象
go
如果SQL Server 2012，请修改下⾯两个位置的代码。在SQL Server 2012，建议在使⽤throw来代替rairror。
解密⽅法：
解密过程，最重要采⽤异或⽅法：
[字符1]经过函数 fn_x（x）加密变成[加密后字符1]，如果我们已知[加密后字符1]，反过来查[字符1]，可以这样：[字符1] = [字符2] ^ fn_x([字符2]) ^ [加密后字符1]
这⾥我列举⼀个简单的例⼦：
--创建加密函数（fn_x）
if object_id('fn_x') is not null drop function fn_x
go
create function fn_x
(
fairyland@x nchar(1)
)returns nchar(1)
as
begin
honey意思return(nchar((65535-unicode(@x))))
end
go
declare@nchar_1_encrypt nchar(1),@nchar_2nchar(1)
--对字符'A'进⾏加密，存⼊变量@nchar_1_encrypt
t@nchar_1_encrypt=dbo.fn_x(N'A')
--參考的字符@nchar_2
t@nchar_2='x'
--算出@nchar_1_encrypt 加密前的字符
ogallala
lect nchar(unicode(@nchar_2)^unicode(dbo.fn_x(@nchar_2))^unicode(@nchar_1_encrypt)) as[@nchar_1]
/*
@nchar_1
--------------------
A
*/
[注]： 从SQL Server 2000⾄ SQL Server 2012 采⽤异或⽅法都可以解密
⽤于解密的存储过程（sp_DecryptObject）：
View Code
U master
Go
if object_ID('[sp_DecryptObject]') is not null
Drop Procedure[sp_DecryptObject]
Go
create procedure sp_DecryptObject
(
@Object sysname, --要解密的对象名：函数，存储过程，视图或触发器
@MaxLength int=4000--评估内容的长度
)
as
t nocount on
/* 1. 解密 */sympathy
if not exists(lect1from sys.objects a where a.object_id=object_id(@Object) pe in('P','V','TR','FN','IF','TF')) begin
--SQL Server 2008
rairror50001 N'⽆效的对象！要解密的对象必须是函数，存储过程，视图或触发器。'
--SQL Server 2012
--throw 50001, N'⽆效的对象！要解密的对象必须是函数，存储过程，视图或触发器。',1
