对存储过程进行加密和解密(SQL 2008/SQL 2012)
開始:
?在網絡上,看到有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')=0 set @Replace='As' ; else set @Replace='For ';存儲過程完成代碼:
View Code
如果SQL Server 2012,請修改下面兩個位置的代碼。在SQL Server 2012,建議在使用throw來代替raiserror。
?
解密方法:
?解密過程,最重要采用異或方法:
[字符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 (@x nchar(1) )returns nchar(1) as begin return(nchar((65535-unicode(@x)))) end go declare @nchar_1_encrypt nchar(1),@nchar_2 nchar(1)--對字符'A'進行加密,存入變量@nchar_1_encrypt set @nchar_1_encrypt=dbo.fn_x(N'A') --參考的字符@nchar_2 set @nchar_2='x' --算出@nchar_1_encrypt 加密前的字符 select 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):
Use master Go if object_ID('[sp_DecryptObject]') is not nullDrop Procedure [sp_DecryptObject] Go create procedure sp_DecryptObject (@Object sysname, --要解密的對象名:函數,存儲過程,視圖或觸發器@MaxLength int=4000 --評估內容的長度 ) as set nocount on /* 1. 解密 */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 2008raiserror 50001 N'無效的對象!要解密的對象必須是函數,存儲過程,視圖或觸發器。' --SQL Server 2012--throw 50001, N'無效的對象!要解密的對象必須是函數,存儲過程,視圖或觸發器。',1 return endif exists(select 1 from sys.sql_modules a where a.object_id=object_id(@Object) and a.definition is not null) begin--SQL Server 2008raiserror 50001 N'對象沒有加密!' --SQL Server 2012--throw 50001, N'無效的對象!要解密的對象必須是函數,存儲過程,視圖或觸發器。',1 return enddeclare @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) beginset @tmpStr=@tmpStr+ @Space4000Set @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) beginset @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>'' beginset @patindex=patindex('%'+char(13)+char(10)+'%',@sql)if @patindex >0beginprint substring(@sql,1,@patindex-1)set @sql=stuff(@sql,1,@patindex+1,'')end else beginset @patindex=patindex('%'+char(13)+'%',@sql)if @patindex >0beginprint substring(@sql,1,@patindex-1)set @sql=stuff(@sql,1,@patindex,'')endelsebeginset @patindex=patindex('%'+char(10)+'%',@sql)if @patindex >0beginprint substring(@sql,1,@patindex-1)set @sql=stuff(@sql,1,@patindex,'')end elsebeginprint @sqlset @sql=''end end endendGo exec sp_ms_marksystemobject 'sp_DecryptObject' --標識為系統對象 go
?如果SQL Server 2012,請修改下面兩個位置的代碼。方法類似于前面的加密過程:
?
搭建測試環境:
在一個測試環境中(DB: Test),先執行上面的加密存儲過程(sp_EncryptObject)和解密存儲過程(sp_EncryptObject);再創建兩個表:TableA & TableB
use test go --創建表: TableA & TableB if object_id('myTableA') is not null drop table myTableA if object_id('myTableB') is not null drop table myTableB go create table myTableA (ID int identity,data nvarchar(50),constraint PK_myTableA primary key(ID)) create table myTableB (ID int ,data nvarchar(50),constraint PK_myTableB primary key(ID)) go接下來,我們要創建6個未加密的對象(對象類型包含 'P','V','TR','FN','IF','TF'):
?1.視圖(myView):
View Code
2.觸發器(MyTrigger):
View Code
3.存儲過程(MyProc):
View Code
4.用戶定義表值函數(TF)(MyFunction_TF):
View Code
5.內聯表值函數(IF) (MyFunction_IF):
View Code
6.標量函數(FN)(MyFunction_FN):
View Code
?當執行完了上面的1-6步驟的腳本,我們通過查詢系統視圖sys.sql_modules,可以看到未加密前的定義信息:
select b.name as object,b.type,a.definitionfrom sys.sql_modules a inner join sys.objects b on b.object_id=a.object_idwhere b.create_date>=convert(date,getdate())order by b.object_id加密測試:
下面我就通過調用加密存儲過程(sp_EncryptObject),一次性對它們進行加密:
use test go exec sp_EncryptObject 'all' go當我們再查回系統視圖sys.sql_modules,會發現definition列返回的是null值,說明定義內容已經給加密:
?解密測試:
解密過程,必須在DAC連接SQL Server,我們這里例子是從 SSMS(SQL Server Management Studio) 查詢編輯器啟動 DAC,如圖:
解密存儲過程(sp_DecryptObject),只能一次對一個存儲過程、函數、視圖或觸發器,進行解密:
use test go exec sp_DecryptObject MyTrigger go?
當定義內容長度超過4000,我們可以指定@MaxLength的值,如:
exec sp_DecryptObject fn_My,20000 go這里(fn_My)是一個函數,定義內容超過了8000:
... ...
小結:
雖然,上面的腳本,我已經在SQL Server 2008 R2 和SQL Server 2012測試過,但無法避免一些未知錯誤 。如果你自己在測試上面的腳本,請不要在生產環境上。如果你在應用過程,碰到有什么問題或有什么意見和建議可以發email聯系我或跟帖,在此非常感謝!
總結
以上是生活随笔為你收集整理的对存储过程进行加密和解密(SQL 2008/SQL 2012)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 过年倒计时
- 下一篇: android资料转移到iphone,安