查看: 1665|回复: 0

[SQLServer] SQL Server解密存储过程

发表于 2018-3-10 08:00:01

SQL Server 对象加密方法:在存储过程,函数,视图的“As”位置前加上“with encryption”;如果是触发器,就在“for”位置前加“with encryption”。


解密过程:


1.执行如下脚本,开启管理员连接(DAC),创建解密存储过程。

  1. USE master
  2. GO
  3. -- enable DAC
  4. sp_configure 'show advanced options', 1
  5. GO
  6. sp_configure 'remote admin connections', 1
  7. GO
  8. RECONFIGURE WITH OVERRIDE
  9. GO
  10. SELECT * FROM sys.configurations where name = 'remote admin connections'
  11. -- create decrypt sp sp_DecryptObject
  12. if object_ID('[sp_DecryptObject]') is not null
  13. Drop Procedure [sp_DecryptObject]
  14. Go
  15. create procedure sp_DecryptObject
  16. (
  17. @Object sysname, --要解密的对象名:函数,存储过程,视图或触发器
  18. @MaxLength int=4000 --评估内容的长度
  19. )
  20. as
  21. set nocount on
  22. /* 1. 解密 */
  23. 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'))
  24. begin
  25. --SQL Server 2008
  26. --raiserror 50001 N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。'
  27. --SQL Server 2012/2014
  28. throw 50001, N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。',1
  29. return
  30. end
  31. if exists(select 1 from sys.sql_modules a where a.object_id=object_id(@Object) and a.definition is not null)
  32. begin
  33. --SQL Server 2008
  34. --raiserror 50001 N'对象没有加密!'
  35. --SQL Server 2012/2014
  36. throw 50001, N'无效的对象!要解密的对象必须是函数,存储过程,视图或触发器。',1
  37. return
  38. end
  39. declare @sql nvarchar(max) --解密出来的SQL语句
  40. ,@imageval nvarchar(max) --加密字符串
  41. ,@tmpStr nvarchar(max) --临时SQL语句
  42. ,@tmpStr_imageval nvarchar(max) --临时SQL语句(加密后)
  43. ,@type char(2) --对象类型('P','V','TR','FN','IF','TF')
  44. ,@objectID int --对象ID
  45. ,@i int --While循环使用
  46. ,@Oject1 nvarchar(1000)
  47. set @objectID=object_id(@Object)
  48. set @type=(select a.type from sys.objects a where a.object_id=@objectID)
  49. declare @Space4000 nchar(4000)
  50. set @Space4000=replicate('-',4000)
  51. /*
  52. @tmpStr 会构造下面的SQL语句
  53. -------------------------------------------------------------------------------
  54. alter trigger Tr_Name on Table_Name with encryption for update as return /**/
  55. alter proc Proc_Name with encryption as select 1 as col /**/
  56. alter view View_Name with encryption as select 1 as col /**/
  57. alter function Fn_Name() returns int with encryption as begin return(0) end/**/
  58. */
  59. set @Oject1=quotename(object_schema_name(@objectID))+'.'+quotename(@Object)
  60. set @tmpStr=
  61. case
  62. when @type ='P ' then N'Alter Procedure '+@Oject1+' with encryption as select 1 as column1 '
  63. when @type ='V ' then N'Alter View '+@Oject1+' with encryption as select 1 as column1 '
  64. when @type ='FN' then N'Alter Function '+@Oject1+'() returns int with encryption as begin return(0) end '
  65. when @type ='IF' then N'Alter Function '+@Oject1+'() returns table with encryption as return(Select a.name from sys.types a) '
  66. when @type ='TF' then N'Alter Function '+@Oject1+'() returns @t table(name nvarchar(50)) with encryption as begin return end '
  67. 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 '
  68. end
  69. set @tmpStr=@tmpStr+'/*'+@Space4000
  70. set @i=0
  71. while @i < (ceiling(@MaxLength*1.0/4000)-1)
  72. begin
  73. set @tmpStr=@tmpStr+ @Space4000
  74. Set @i=@i+1
  75. end
  76. set @tmpStr=@tmpStr+'*/'
  77. ------------
  78. set @imageval =(select top(1) a.imageval from sys.sysobjvalues a where a.objid=@objectID and a.valclass=1)
  79. begin tran
  80. exec(@tmpStr)
  81. set @tmpStr_imageval =(select top(1) a.imageval from sys.sysobjvalues a where a.objid=@objectID and a.valclass=1)
  82. rollback tran
  83. -------------
  84. set @tmpStr=stuff(@tmpStr,1,5,'create')
  85. set @sql=''
  86. set @i=1
  87. while @i<= (datalength(@imageval)/2)
  88. begin
  89. set @sql=@sql+isnull(nchar(unicode(substring(@tmpStr,@i,1)) ^ unicode(substring(@tmpStr_imageval,@i,1))^unicode(substring(@imageval,@i,1)) ),'')
  90. Set @i+=1
  91. end
  92. /* 2. 列印 */
  93. declare @patindex int
  94. while @sql>''
  95. begin
  96. set @patindex=patindex('%'+char(13)+char(10)+'%',@sql)
  97. if @patindex >0
  98. begin
  99. print substring(@sql,1,@patindex-1)
  100. set @sql=stuff(@sql,1,@patindex+1,'')
  101. end
  102. else
  103. begin
  104. set @patindex=patindex('%'+char(13)+'%',@sql)
  105. if @patindex >0
  106. begin
  107. print substring(@sql,1,@patindex-1)
  108. set @sql=stuff(@sql,1,@patindex,'')
  109. end
  110. else
  111. begin
  112. set @patindex=patindex('%'+char(10)+'%',@sql)
  113. if @patindex >0
  114. begin
  115. print substring(@sql,1,@patindex-1)
  116. set @sql=stuff(@sql,1,@patindex,'')
  117. end
  118. else
  119. begin
  120. print @sql
  121. set @sql=''
  122. end
  123. end
  124. end
  125. end
  126. Go
  127. exec sys.sp_MS_marksystemobject 'sp_DecryptObject' --标识为系统对象
  128. go
复制代码


2.打开SSMS,新建DAC连接,如下图,在实例名“sqlclust\testal”前加上“admin:”,点连接:

1.jpg

2.jpg


3.运行如下T-SQL语句来解密:

  1. USE xxxxx
  2. GO
  3. EXEC sp_DecryptObject 'sp_xxxxxxx'
  4. GO
复制代码




回复

使用道具 举报