sql server 對儲存過程和函數的加密解密

加密使用關鍵字：ENCRYPTION

CREATE procedure dbo.sp_EytTest</p><p>@para varchar(50)<br />WITH ENCRYPTION<br />as<br />begin<br /> select @para<br />end<br />go<br />

 

解密：

解密的儲存過程都來自網路，需要在DAC下使用.

sql server 2000 解密 :

create PROCEDURE [dbo].[sp_decrypt](@objectname varchar(50))<br />AS<br />begin<br />set nocount on<br />begin tran<br />declare @objectname1 varchar(100),@orgvarbin varbinary(8000)<br />declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)<br />DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)<br />declare @i int,@status int,@type varchar(10),@parentid int<br />declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int<br />select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@objectname)<br />create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)<br />insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectname)<br />select @number=max(number) from #temp<br />set @k=0<br />while @k<=@number<br />begin<br />if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)<br />begin<br />if @type='P'<br />set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectname +';'+rtrim(@k)+' WITH ENCRYPTION AS '<br /> else 'ALTER PROCEDURE '+ @objectname+' WITH ENCRYPTION AS '<br /> end)<br />if @type='TR'<br />begin<br />declare @parent_obj varchar(255),@tr_parent_xtype varchar(10)<br />select @parent_obj=parent_obj from sysobjects where id=object_id(@objectname)<br />select @tr_parent_xtype=xtype from sysobjects where id=@parent_obj<br />if @tr_parent_xtype='V'<br />begin<br />set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 '<br />end<br />else<br />begin<br />set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '<br />end<br />end<br />if @type='FN' or @type='TF' or @type='IF'<br />set @sql1=(case @type when 'TF' then<br />'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '<br />when 'FN' then<br />'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns char(1) with encryption as begin return @a end'<br />when 'IF' then<br />'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns table with encryption as return select @a as a'<br />end)</p><p>if @type='V'<br />set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'</p><p>set @q=len(@sql1)<br />set @sql1=@sql1+REPLICATE('-',4000-@q)<br />select @sql2=REPLICATE('-',8000)<br />set @sql3='exec(@sql1'<br />select @colid=max(colid) from #temp where number=@k<br />set @n=1<br />while @n<=CEILING(1.0*(@colid-1)/2) and len(@sql3)<=3996<br />begin<br />set @sql3=@sql3+'+@'<br />set @n=@n+1<br />end<br />set @sql3=@sql3+')'<br />exec sp_executesql @sql3,N'@sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2</p><p>end<br />set @k=@k+1<br />end</p><p>set @k=0<br />while @k<=@number<br />begin</p><p>if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)<br />begin<br />select @colid=max(colid) from #temp where number=@k<br />set @n=1</p><p>while @n<=@colid<br />begin<br />select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k</p><p>SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectname) and colid=@n and number=@k)<br />if @n=1<br />begin<br />if @type='P'<br />SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectname +';'+rtrim(@k)+' WITH ENCRYPTION AS '<br /> else 'CREATE PROCEDURE '+ @objectname +' WITH ENCRYPTION AS '<br /> end)</p><p>if @type='FN' or @type='TF' or @type='IF'<br />SET @OrigSpText2=(case @type when 'TF' then<br />'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '<br />when 'FN' then<br />'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns char(1) with encryption as begin return @a end'<br />when 'IF' then<br />'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns table with encryption as return select @a as a'<br />end)</p><p>if @type='TR'<br />begin</p><p>if @tr_parent_xtype='V'<br />begin<br />set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 '<br />end<br />else<br />begin<br />set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '<br />end</p><p>end</p><p>if @type='V'<br />set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'</p><p>set @q=4000-len(@OrigSpText2)<br />set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)<br />end<br />else<br />begin<br />SET @OrigSpText2=REPLICATE('-', 4000)<br />end<br />SET @i=1</p><p>SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))</p><p>WHILE @i<=datalength(@OrigSpText1)/2<br />BEGIN</p><p>SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^<br /> (UNICODE(substring(@OrigSpText2, @i, 1)) ^<br /> UNICODE(substring(@OrigSpText3, @i, 1)))))<br /> SET @i=@i+1<br />END<br />set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))<br />set @resultsp=(case when @encrypted=1<br /> then @resultsp<br /> else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)<br /> end)<br />print @resultsp</p><p>set @n=@n+1</p><p>end</p><p>end<br />set @k=@k+1<br />end</p><p>drop table #temp<br />rollback tran<br />end

sql server 2005/2008 解密 :

create PROCEDURE [dbo].[sp__windbi$decrypt]<br />(@procedure sysname = NULL, @revfl int = 1)<br />AS<br />SET NOCOUNT ON<br />IF @revfl = 1<br />BEGIN<br />PRINT '警告：該預存程序會刪除並重建原始的預存程序。'<br />PRINT ' 在運行該預存程序之前確保你的資料庫有一個備份。'<br />PRINT ' 該預存程序通常應該運行在產品環境的一個備份的非產品環境下。'<br />PRINT ' 為了運行這個預存程序，將參數@refl的值更改為0。'<br />RETURN 0<br />END<br />DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted<br />tinyint,@procNameLength int<br />select @maxColID = max(subobjid),@intEncrypted = imageval FROM<br />sys.sysobjvalues WHERE objid = object_id(@procedure)<br />GROUP BY imageval<br />--select @maxColID as 'Rows in sys.sysobjvalues'<br />select @procNameLength = datalength(@procedure) + 29<br />DECLARE @real_01 nvarchar(max)<br />DECLARE @fake_01 nvarchar(max)<br />DECLARE @fake_encrypt_01 nvarchar(max)<br />DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)<br />declare @objtype varchar(2),@ParentName nvarchar(max)<br />select @real_decrypt_01a = ''<br />--提取對象的類型如是預存程序還是函數，如果是觸發器，還要得到其父物件的名稱<br />select @objtype=type,@parentname=object_name(parent_object_id)<br />from sys.objects where [object_id]=object_id(@procedure)<br />-- 從sys.sysobjvalues裡提出加密的imageval記錄<br />SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =<br />object_id(@procedure) and valclass = 1 order by subobjid)<br />--建立一個暫存資料表<br />create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,<br />[real_decrypt] NVARCHAR(MAX) )<br />--開始一個事務，稍後復原<br />BEGIN TRAN<br />--更改原始的預存程序，用虛線替換<br />if @objtype='P'<br /> SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS<br /> '+REPLICATE('-', 40003 - @procNameLength)<br />else if @objtype='FN'<br /> SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/ END'<br />else if @objtype='V'<br /> SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/'<br />else if @objtype='TR'<br /> SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+' WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/'<br />EXECUTE (@fake_01)<br />--從sys.sysobjvalues裡提出加密的假的<br />SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =<br />object_id(@procedure) and valclass = 1 order by subobjid )<br />if @objtype='P'<br /> SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS<br /> '+REPLICATE('-', 40003 - @procNameLength)<br />else if @objtype='FN'<br /> SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/ END'<br />else if @objtype='V'<br /> SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/'<br />else if @objtype='TR'<br /> SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+' WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)<br /> /*'+REPLICATE('*', datalength(@real_01) /2 - @procNameLength)+'*/'<br />--開始計數<br />SET @intProcSpace=1<br />--使用輸入鍵台臨時變數<br />SET @real_decrypt_01 = replicate(N'A', (datalength(@real_01) /2 ))<br />--迴圈設定每一個變數，建立真正的變數<br />--每次一個位元組<br />SET @intProcSpace=1<br />--如有必要，遍曆每個@real_xx變數並解密<br />WHILE @intProcSpace<=(datalength(@real_01)/2)<br />BEGIN<br />--真的和假的和加密的假的進行異或處理<br />SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,<br />NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^<br />(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^<br />UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))<br />SET @intProcSpace=@intProcSpace+1<br />END<br />--通過sp_helptext邏輯向表#output裡插入變數<br />insert #output (real_decrypt) select @real_decrypt_01<br />-- select real_decrypt AS '#output chek' from #output --測試<br />-- -------------------------------------<br />--開始從sp_helptext提取<br />-- -------------------------------------<br />declare @dbname sysname<br />,@BlankSpaceAdded int<br />,@BasePos int<br />,@CurrentPos int<br />,@TextLength int<br />,@LineId int<br />,@AddOnLen int<br />,@LFCR int --斷行符號換行的長度<br />,@DefinedLength int<br />,@SyscomText nvarchar(4000)<br />,@Line nvarchar(255)<br />Select @DefinedLength = 255<br />SELECT @BlankSpaceAdded = 0 --跟蹤行結束的空格。注意Len函數忽略了多餘的空格<br />CREATE TABLE #CommentText<br />(LineId int<br />,Text nvarchar(255) collate database_default)<br />--使用#output代替sys.sysobjvalues<br />DECLARE ms_crs_syscom CURSOR LOCAL<br />FOR SELECT real_decrypt from #output<br />ORDER BY ident<br />FOR READ ONLY<br />--擷取文本<br />SELECT @LFCR = 2<br />SELECT @LineId = 1<br />OPEN ms_crs_syscom<br />FETCH NEXT FROM ms_crs_syscom into @SyscomText<br />WHILE @@fetch_status >= 0<br />BEGIN<br />SELECT @BasePos = 1<br />SELECT @CurrentPos = 1<br />SELECT @TextLength = LEN(@SyscomText)<br />WHILE @CurrentPos != 0<br />BEGIN<br />--通過斷行符號尋找行的結束<br />SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,<br />@BasePos)<br />--如果找到斷行符號<br />IF @CurrentPos != 0<br />BEGIN<br />--如果@Lines的長度的新值比設定的大就插入@Lines目前的內容並繼續<br />While (isnull(LEN(@Line),0) + @BlankSpaceAdded +<br />@CurrentPos-@BasePos + @LFCR) > @DefinedLength<br />BEGIN<br />SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +<br />@BlankSpaceAdded)<br />INSERT #CommentText VALUES<br />( @LineId,<br />isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,<br />@BasePos, @AddOnLen), N''))<br />SELECT @Line = NULL, @LineId = @LineId + 1,<br />@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0<br />END<br />SELECT @Line = isnull(@Line, N'') +<br />isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')<br />SELECT @BasePos = @CurrentPos+2<br />INSERT #CommentText VALUES( @LineId, @Line )<br />SELECT @LineId = @LineId + 1<br />SELECT @Line = NULL<br />END<br />ELSE<br />--如果斷行符號沒找到<br />BEGIN<br />IF @BasePos <= @TextLength<br />BEGIN<br />--如果@Lines長度的新值大於定義的長度<br />While (isnull(LEN(@Line),0) + @BlankSpaceAdded +<br />@TextLength-@BasePos+1 ) > @DefinedLength<br />BEGIN<br />SELECT @AddOnLen = @DefinedLength -<br />(isnull(LEN(@Line),0) + @BlankSpaceAdded)<br />INSERT #CommentText VALUES<br />( @LineId,<br />isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,<br />@BasePos, @AddOnLen), N''))<br />SELECT @Line = NULL, @LineId = @LineId + 1,<br />@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =<br />0<br />END<br />SELECT @Line = isnull(@Line, N'') +<br />isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')<br />if LEN(@Line) < @DefinedLength and charindex(' ',<br />@SyscomText, @TextLength+1 ) > 0<br />BEGIN<br />SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1<br />END<br />END<br />END<br />END<br />FETCH NEXT FROM ms_crs_syscom into @SyscomText<br />END<br />IF @Line is NOT NULL<br />INSERT #CommentText VALUES( @LineId, @Line )<br />select Text from #CommentText order by LineId<br />CLOSE ms_crs_syscom<br />DEALLOCATE ms_crs_syscom<br />DROP TABLE #CommentText<br />-- -------------------------------------<br />--結束從sp_helptext提取<br />-- -------------------------------------<br />--刪除用虛線建立的預存程序並重建原始的預存程序<br />ROLLBACK TRAN<br />DROP TABLE #output<br />