一 存储过程的加密
1 使用方法
use test
go
exec sp_EncryptObject 'sp_list'
go
2 完整的加密存储过程
USE [master]
GO
if object_ID('[sp_EncryptObject]') is not null
Drop Procedure
[sp_EncryptObject]
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure
[dbo].[sp_EncryptObject]
(
@Object
sysname='All'
)
as
begin
set nocount on
if @Object
<>'All'
begin
if not exists(select 1 from sys.objects a where
a.object_id=object_id(@Object) And a.type
in('P','V','TR','FN','IF','TF'))
begin
--SQL
Server 2008
raiserror
50001 N'无效的加密对象!加密对象必须是函数,存储过程,视图或触发器。'
--SQL
Server 2012
--throw
50001,
N'无效的加密对象!加密对象必须是函数,存储过程,视图或触发器。',1
return
end
if exists(select 1 from sys.sql_modules a where
a.object_id=object_id(@Object) and a.definition is null)
begin
--SQL
Server 2008
raiserror
50001 N'对象已经加密!'
--SQL
Server 2012
--throw
50001, N'对象已经加密!',1
return
end
end
declare @sql
nvarchar(max),@C1 nchar(1),@C2 nchar(1),@type nvarchar(50),@Replace
nvarchar(50)
set @C1=nchar(13)
set @C2=nchar(10)
declare
cur_Object
cursor for
select
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(select 1
from sys.extended_properties x
where x.major_id=b.object_id
and
x.minor_id=0
and
x.class=1
and
x.name='microsoft_database_tools_support'
)
where b.type
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
Case
when b.type ='V' then
1
when b.type ='TR' then
2
when b.type
in('FN','IF','TF') then 3
else 4
end,b.create_date,b.object_id
open cur_Object
fetch next from
cur_Object into @Object,@sql
while
@@fetch_status=0
begin
Begin Try
if
objectproperty(object_id(@Object),'ExecIsAfterTrigger')=0 set
@Replace='As' ; else set @Replace='For ';
if
(patindex('%'+@C1+@C2+@Replace+@C1+@C2+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C1+@C2+@Replace+@C1+@C2,@C1+@C2+'With
Encryption'+@C1+@C2+@Replace+@C1+@C2)
end
else
if(patindex('%'+@C1+@Replace+@C1+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C1+@Replace+@C1,@C1+'With
Encryption'+@C1+@Replace+@C1)
end
else
if(patindex('%'+@C2+@Replace+@C2+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C2+@Replace+@C2,@C2+'With
Encryption'+@C2+@Replace+@C2)
end
else
if(patindex('%'+@C2+@Replace+@C1+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C2+@Replace+@C1,@C1+'With
Encryption'+@C2+@Replace+@C1)
end
else
if(patindex('%'+@C1+@C2+@Replace+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C1+@C2+@Replace,@C1+@C2+'With
Encryption'+@C1+@C2+@Replace)
end
else
if(patindex('%'+@C1+@Replace+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C1+@Replace,@C1+'With
Encryption'+@C1+@Replace)
end
else
if(patindex('%'+@C2+@Replace+'%',@sql)>0)
begin
set
@sql=Replace(@sql,@C2+@Replace,@C2+'With
Encryption'+@C2+@Replace)
end
set @type
=
case
when object_id(@Object,'P')>0 then
'Proc'
when object_id(@Object,'V')>0 then
'View'
when
object_id(@Object,'TR')>0 then 'Trigger'
when object_id(@Object,'FN')>0 or
object_id(@Object,'IF')>0 or object_id(@Object,'TF')>0 then
'Function'
end
set
@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
close cur_Object
deallocate
cur_Object
end
Go
exec sp_ms_marksystemobject 'sp_EncryptObject' --标识为系统对象
go
二 存储过程的解密
1 使用方法
use test
go
exec sp_DecryptObject sp_list1,20000
go
默认长度 4000,上例是20000,就是被解密的存储过程本身的长度 范围
2 完整的解密存储过程
USE [master]
GO
if object_ID('[sp_DecryptObject]') is not null
Drop Procedure
[sp_DecryptObject]
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure
[dbo].[sp_DecryptObject]
(
@Object
sysname,
--要解密的对象名:函数,存储过程,视图或触发器
@MaxLength int=4000
--评估内容的长度
)
as
set nocount on
if not exists(select 1 from sys.objects a where
a.object_id=object_id(@Object) And a.type
in('P','V','TR','FN','IF','TF'))
begin
--SQL Server 2008
raiserror 50001
N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。'
--SQL Server 2012
--throw 50001,
N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。',1
return
end
if exists(select 1 from sys.sql_modules a where
a.object_id=object_id(@Object) and a.definition is not null)
begin
--SQL Server 2008
raiserror 50001
N'对象没有加密!'
--SQL Server 2012
--throw 50001,
N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。',1
return
end
declare @sql nvarchar(max)
--解密出来的SQL语句
,@imageval nvarchar(max)
--加密字符串
,@tmpStr nvarchar(max)
--临时SQL语句
,@tmpStr_imageval nvarchar(max)
--临时SQL语句(加密后)
,@type char(2)
--对象类型('P','V','TR','FN','IF','TF')
,@objectID int
--对象ID
,@i int
--While循环使用
,@Oject1 nvarchar(1000)
set @objectID=object_id(@Object)
set @type=(select a.type from sys.objects a where
a.object_id=@objectID)
declare @Space4000 nchar(4000)
set @Space4000=replicate('-',4000)
-------------------------------------------------------------------------------
--@tmpStr 会构造下面的SQL语句
--alter trigger Tr_Name on Table_Name with encryption for
update as return
--alter proc Proc_Name with encryption as
select 1 as col
--alter view View_Name with encryption as select 1 as
col
--alter function Fn_Name() returns int with encryption as
begin return(0) end
-------------------------------------------------------------------------------
set
@Oject1=quotename(object_schema_name(@objectID))+'.'+quotename(@Object)
set @tmpStr=
case
when @type
='P ' then N'Alter Procedure '+@Oject1+' with encryption as select
1 as column1 '
when @type
='V ' then N'Alter View '+@Oject1+' with encryption as select 1 as
column1 '
when @type
='FN' then N'Alter Function '+@Oject1+'() returns int with
encryption as begin return(0) end '
when @type
='IF' then N'Alter Function '+@Oject1+'() returns table with
encryption as return(Select a.name from sys.types a) '
when @type
='TF' then N'Alter Function '+@Oject1+'() returns @t table(name
nvarchar(50)) with encryption as begin return end '
else
'Alter Trigger '+@Oject1+'on
'+quotename(object_schema_name(@objectID))+'.'+(select Top(1)
quotename(object_name(parent_id)) from sys.triggers a where
a.object_id=@objectID)+' with encryption for update as return
'
end
set @tmpStr=@tmpStr+'/ *'+@Space4000 -- / *
之间的有空格,防止博客过滤,使用时去掉
set @i=0
while @i < (ceiling(@MaxLength*1.0/4000)-1)
begin
set @tmpStr=@tmpStr+
@Space4000
Set @i=@i+1
end
set @tmpStr=@tmpStr+'* /'
-- * /
之间的有空格,防止博客过滤,使用时去掉
------------
set @imageval =(select top(1) a.imageval from sys.sysobjvalues
a where a.objid=@objectID and a.valclass=1)
begin tran
exec(@tmpStr)
set @tmpStr_imageval =(select top(1) a.imageval from
sys.sysobjvalues a where a.objid=@objectID and a.valclass=1)
rollback tran
-------------
set @tmpStr=stuff(@tmpStr,1,5,'create')
set @sql=''
set @i=1
while @i<= (datalength(@imageval)/2)
begin
set
@sql=@sql+isnull(nchar(unicode(substring(@tmpStr,@i,1)) ^
unicode(substring(@tmpStr_imageval,@i,1))^unicode(substring(@imageval,@i,1))
),'')
Set @i+=1
end
-- 2 打印
declare @patindex int
while @sql>''
begin
set
@patindex=patindex('%'+char(13)+char(10)+'%',@sql)
if @patindex >0
begin
print substring(@sql,1,@patindex-1)
set @sql=stuff(@sql,1,@patindex+1,'')
end
else
begin
set
@patindex=patindex('%'+char(13)+'%',@sql)
if @patindex >0
begin
print
substring(@sql,1,@patindex-1)
set
@sql=stuff(@sql,1,@patindex,'')
end
else
begin
set
@patindex=patindex('%'+char(10)+'%',@sql)
if
@patindex >0
begin
print
substring(@sql,1,@patindex-1)
set
@sql=stuff(@sql,1,@patindex,'')
end
else
begin
print @sql
set @sql=''
end
end
end
end
Go
exec sp_ms_marksystemobject 'sp_DecryptObject' --标识为系统对象
go
加载中,请稍候......