关闭顶部展开顶部

SQL Server储过程加密和解密原理深入分析_Mssql数据库教程

编辑Tag赚U币
缂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁惧墽鎳撻—鍐偓锝庝簼閹癸綁鏌i鐐搭棞闂囧鏌ㄥ┑鍡欏⒈闁搞倕顦扮换娑橆啅椤旇崵鍑归梺鍝勬噺缁诲牓寮诲☉姘勃闁绘劦鍓涘▓銈夋⒑閸涘﹥鈷掗柡浣告憸濡叉劙骞樼€涙ê顎撻梺鍦帛鐢﹥绔熼弴銏♀拻濞达絼璀﹂弨鏉棵瑰⿰搴″⒋妞ゃ垺顨婅矾闁绘稓顧€S闂傚倸鍊搁崐鎼佸磹瀹勬噴褰掑炊瑜忛弳锕傛煟閵忊懚鍦玻濡ゅ懏鐓欓柟娈垮枛椤eジ鏌涚€e墎绡€闁哄本娲樺鍕醇濠靛棗顥欐繝鐢靛仦閸ㄦ儼褰滈梺琛″亾濞寸姴顑嗛崐鐢告煥濠靛棗鏆欏┑锛勫厴閺屾稓鈧綆鍋呭畷宀€鈧娲樼敮锟犲箖濞嗘挸钃熼柕澶堝劜閺嗩亪姊婚崒娆戭槮闁圭⒈鍋勭叅闁靛ň鏅涚壕濠氭煟閹邦喖鍔嬮柛濠傜仛閵囧嫰骞掗崱妞惧闂備礁鐤囬~澶愬垂閸ф绠栨繛鍡樻尭閻顭块懜鐬垿鏁嶆笟鈧缁樻媴鐟欏嫬浠╅梺鍛婃煥闁帮絽鐣锋导鏉戝唨鐟滄粓宕甸弴鐐╂斀闁绘ê纾。鏌ユ煛閸涱喗鍊愰柡灞诲姂閹倝宕掑☉姗嗕紦婵犵數濮烽弫鍛婃叏閻戣棄鏋侀柟闂寸绾惧鏌i幇顒佹儓闁搞劌鍊块幃瑙勬姜閹峰矈鍔呭┑鐐插悑閻楃娀骞冨畡鎵虫瀻闊洦鎼╂导鈧梻浣虹帛缁诲倿鎮ユ總绋胯摕婵炴垯鍨归悞娲煕閹板吀绨存俊鎻掔墦濮婅櫣绱掑Ο璇查瀺闂佹寧娲忛崹钘夘嚕婵犳艾鐏抽柟棰佺閹垿鏌熼懖鈺勊夐柍褜鍓欑壕顓㈩敊閹达附鈷掑ù锝囨嚀閳绘洟鏌¢埀顒佹綇閳哄倹娈伴梺璺ㄥ枔婵挳鎷戦悢鍏肩厪濠电偟鍋撳▍鍡涙煕鐎n亝顥㈤柡灞剧〒娴狅箓宕滆濡插牆顪冮妶鍛寸崪闁瑰嚖鎷� 缂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾剧懓顪冪€n亝鎹i柣顓炴閵嗘帒顫濋敐鍛闂佽姤蓱缁诲牆顫忓ú顏嶆晢闁逞屽墰缁梻鈧潧鎽滈悳缁樹繆閵堝懏鍣洪柣鎾寸懇濮婃椽顢橀妸褏鏆犳繝鈷€鍥╃暫闁哄被鍊濆鍫曞箰鎼粹€叉樊闂備礁鎼張顒勬儎椤栨稐绻嗛柣鎴犵摂閺佸﹪鏌i幘鍐茬槰婵炶壈宕电槐鎾诲磼濞嗘帩鍞归梺绋款儐閹告悂鈥﹂崸妤佸殝闂傚牊绋戦~宥夋⒑缂佹ɑ灏柛鐔告尦瀵寮撮悢椋庣獮闂佸壊鍋呯换鍌炩€栨径鎰拺缂備焦岣跨粻銉ッ瑰⿰搴濋偗鐎殿喖顭烽弫鎰板川椤忓懏鏉搁梻浣告贡閸嬫挸岣垮▎鎿冩晜闁跨噦鎷� 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾剧懓顪冪€n亜顒㈡い鎰矙閺屻劑鎮㈤崫鍕戙垻鐥幆褜鐓奸柡灞剧洴瀵挳濡搁妷褉鍋撻鍕厱闁靛绲芥俊鐣岀磽瀹ュ懏鍠橀柡灞界Ч瀹曨偊宕熼锝嗩啀婵$偑鍊х€靛矂宕规潏鈺傚床婵犻潧顑呴~鍛存煥濠靛棙顥犻柕鍡樺姍濮婃椽宕崟顓炩拡闂佸憡鎸鹃崰搴ㄦ偩瀹勬嫈鏃堝焵椤掑嫨鍋戝ù鍏兼綑闁卞洭鏌i弬鎸庢儓妤犵偞鍔欏缁樻媴鐟欏嫬浠╅梺绋垮瘨閸ㄨ泛鐣峰┑鍡╁悑濠㈣泛顑呴崜顓㈡⒑绾懏褰ч梻鍕閸╂盯骞掗幋顓犲數闁荤姾娅g亸銊╁礉閻旂厧鏋侀柛顐犲劜閳锋垿鏌涘┑鍡楊伂妞ゎ偓绠撻弻娑欑節閸愵亜鈪圭€光偓閿濆牆鍔电紒鐘崇☉閳藉螣濠х偓娅婇柡灞诲姂瀵噣宕掑⿰鍐偧婵$偑鍊戦崕鑼矙閹捐埖顫曢柟鎹愵嚙绾惧吋绻涢崱妯虹仴濠碘€茬矙濮婃椽宕妷銉ょ捕濡炪値鍘鹃崗姗€鎮伴璺ㄧ杸婵炴垶鐟﹀▍銏ゆ⒑鐠恒劌娅愰柟鍑ゆ嫹 闂傚倸鍊搁崐鎼佸磹閹间礁纾瑰瀣捣閻棗銆掑锝呬壕濡ょ姷鍋為悧鐘汇€侀弴銏℃櫇闁逞屽墴閹潡顢氶埀顒勫蓟閿濆憘鏃堝焵椤掑嫭鍋嬪┑鐘叉搐閻鐓崶銊р姇闁绘挻鐟х槐鎾存媴闂堟稓浠奸梺鍝勵儐缁嬫帗绌辨繝鍥ㄥ€烽柟娈垮枤閻撴捇姊洪崫鍕槵闁逞屽墯閸撴岸宕ョ€n喖绠圭紒顔煎帨閸嬫捇鎳犻浣规闂傚倸鍊风粈渚€骞夐敓鐘偓鍐幢濞嗗繑鐎抽悗骞垮劚濞诧箑鐣烽崣澶岀闁瑰瓨鐟ラ悘鈺傤殽閻愵亜鐏ǎ鍥э躬椤㈡稑鈹戦幇顒侇唲闂傚倸鍊搁ˇ顖滅矓閹绢喖鐓橀柟杈惧瘜閺佸鏌涢埄鍐噮閽冮亶姊绘担鍛婂暈婵﹤缍婇妴鍐╃節閸パ勬К闂侀€炲苯澧柕鍥у楠炴帡骞嬪┑鍥╀壕婵犵數鍋涢崥瀣礉濞嗘挸钃熼柣鏃傗拡閺佸﹪鏌ゅù瀣珔闁绘挾鍠栭幃妤冩喆閸曨剛顦ㄥ┑鐐跺皺閸犳牕顕f繝姘耿婵°倕锕ら幃鎴︽⒑閸涘﹣绶遍柛銊﹀▕瀹曘垽顢楅崟顑芥嫽婵炶揪缍€婵倗娑甸崼鏇熺厱闁挎繂绻掗悾鍨殽閻愯尙绠婚柡浣规崌閺佹捇鏁撻敓锟�,闂傚倸鍊搁崐鎼佸磹閹间礁纾圭€瑰嫭鍣磋ぐ鎺戠倞妞ゆ帊绀侀崜顓烆渻閵堝棗濮х紒鐘冲灩缁牏鈧綆鍋佹禍婊堟煙閹佃櫕娅呴柣蹇婃櫇閹喖顫滈埀顒€顫忕紒妯诲缂佹稑顑呭▓顓炩攽椤旀枻鍏紒鐘虫崌閺佹劙鎮欓弶鎴犵獮闁诲函缍嗛崜娑㈩敊閺囩喓绡€闁汇垽娼у瓭闂佺ǹ顑呭Λ婵嗙暦閻㈢ǹ鍗抽柕蹇ョ磿閸樺崬顪冮妶鍡楀Ё缂佹彃澧界划鍫ュ焵椤掑倻纾藉ù锝嗗絻娴滈箖姊洪崨濠傚闁哄倸鍊圭粋宥呪堪閸喓鍘搁梺绋挎湰濮樸劍绂掗姀銈嗙厵妞ゆ棁妫勯悘锕傛煏閸パ冾伂缂佺姵鐩鎾倷鐎电硶鍋撻崜褏纾藉ù锝堟鐢盯鏌i埡濠傜仸闁绘侗鍠氶埀顒婄秵閸犳宕愰悜鑺ョ厸濠㈣泛顑呴悘鈺傜箾閸稑鈧繂顫忕紒妯诲闁告盯娼х紞濠囥€侀弽顓炲耿婵炴垶岣块澶愭⒑閹肩偛鍔€閻忕偛澧界粙渚€姊绘担鍛婂暈缂佽鍊婚埀顒佺殰閸パ冨殤闁瑰吋鐣崝宥夋偂濞嗘劑浜滈柡宓嫷妫為柣搴㈠嚬閸犳绮嬪澶樻晜闁割偆鍟块幏缁樼箾鏉堝墽鍒伴柟璇х節瀹曨垶鎮欓悜妯煎幗闂佺粯姊瑰ḿ娆撳礉閵堝鐓欐鐐茬仢閻忊晠鎽堕敐鍛偓鎺戭潩閻撳海浠╁┑鐐茬墑閸斿秶鎹㈠┑瀣仺闂傚牊鍒€閿濆洨妫柡澶庢硶鏁堥悗娈垮枟閻擄繝鐛弽銊﹀闁革富鍘煎鎶芥⒒娴h櫣甯涙繛鍙夌墵瀹曟劙宕烽娑樹壕婵ḿ鍋撶€氾拷!

推荐:Excel导入数据库时出现的文本截断问题解决方案
在把Excel导入到数据库中时,发生文本截断问题:即导入的数据每行只有一部分,遇到这样的问题,甚是尴尬,接下来介绍此问题的解决方法,感兴趣的朋友可以了解下,希望本文对你有所帮助

开始
--------------------------------------------------------------------------------
在网络上,看到有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"位置:
复制代码 代码如下:www.mb5u.com

if objectproperty(object_id(@Object),'ExecIsAfterTrigger')=0 set @Replace='As' ; else set @Replace='For ';

存储过程完成代码:
复制代码 代码如下:www.mb5u.com

Use 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的时候,对所有的函数,存储过程,视图和触发器进行加密
调用方法:
. Execute sp_EncryptObject 'All'
. Execute sp_EncryptObject 'ObjectName'
*/
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

如果SQL Server 2012,请修改下面两个位置的代码。在SQL Server 2012,建议在使用throw来代替raiserror。

解密方法
解密过程,最重要采用异或方法:
[字符1]经过函数 fn_x(x)加密变成[加密后字符1],如果我们已知[加密后字符1],反过来查[字符1],可以这样:
[字符1] = [字符2] ^ fn_x([字符2]) ^ [加密后字符1]
这里我列举一个简单的例子:
复制代码 代码如下:www.mb5u.com

--创建加密函数(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):
复制代码 代码如下:www.mb5u.com

Use 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
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 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

如果SQL Server 2012,请修改下面两个位置的代码。方法类似于前面的加密过程:

搭建测试环境
--------------------------------------------------------------------------------
在一个测试环境中(DB: Test),先执行上面的加密存储过程(sp_EncryptObject)和解密存储过程(sp_EncryptObject);再创建两个表:TableA & TableB
复制代码 代码如下:www.mb5u.com

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):
复制代码 代码如下:www.mb5u.com

if object_id('myView') is not null drop view myView
go
create view myView
As
select * from TableA;
go

2.触发器(MyTrigger):
复制代码 代码如下:www.mb5u.com

if object_id('MyTrigger') is not null drop Trigger MyTrigger
go
create trigger MyTrigger
on TableA
for update
As
insert into TableB(ID,data) select a.ID,a.Data From Inserted a
go

3.存储过程(MyProc):
复制代码 代码如下:www.mb5u.com

if object_id('MyProc') is not null drop proc MyProc
go
create proc MyProc
(
@data nvarchar(50)
)
As
insert into TableA(data) values(@data)
go

4.用户定义表值函数(TF)(MyFunction_TF):
复制代码 代码如下:www.mb5u.com

if object_id('MyFunction_TF') is not null drop function MyFunction_TF
go
create function MyFunction_TF
(
)
returns @t table
(
id int,
data nvarchar(50)
)
As
begin
insert @t(id,data) select id,data from TableA
return
end
go

5.内联表值函数(IF) (MyFunction_IF):
复制代码 代码如下:www.mb5u.com

if object_id('MyFunction_IF') is not null drop function MyFunction_IF
go
create function MyFunction_IF
(
)
returns table
As
return(select top(3) id, data from TableA order by id desc)
go

6.标量函数(FN)(MyFunction_FN):
复制代码 代码如下:www.mb5u.com

if object_id('MyFunction_FN') is not null drop function MyFunction_FN
go
create function MyFunction_FN
(
)
returns nvarchar(50)
As
begin
return(select top(1) data from TableA order by id desc)
end
go

当执行完了上面的1-6步骤的脚本,我们通过查询系统视图sys.sql_modules,可以看到未加密前的定义信息:
复制代码 代码如下:www.mb5u.com

select b.name as object,b.type,a.definition
from sys.sql_modules a
inner join sys.objects b on b.object_id=a.object_id
where b.create_date>=convert(date,getdate())
order by b.object_id


加密测试
--------------------------------------------------------------------------------
下面我就通过调用加密存储过程(sp_EncryptObject),一次性对它们进行加密:
复制代码 代码如下:www.mb5u.com

use test
go
exec sp_EncryptObject 'all'
go


当我们再查回系统视图sys.sql_modules,会发现definition列返回的是null值,说明定义内容已经给加密:


解密测试
--------------------------------------------------------------------------------
解密过程,必须在DAC连接SQL Server,我们这里例子是从 SSMS(SQL Server Management Studio) 查询编辑器启动 DAC,如图:

解密存储过程(sp_DecryptObject),只能一次对一个存储过程、函数、视图或触发器,进行解密:
复制代码 代码如下:www.mb5u.com

use test
go
exec sp_DecryptObject MyTrigger
go


当定义内容长度超过4000,我们可以指定@MaxLength的值,如:
复制代码 代码如下:www.mb5u.com

exec sp_DecryptObject fn_My,20000
go

这里(fn_My)是一个函数,定义内容超过了8000:

... ...


小结
--------------------------------------------------------------------------------
虽然,上面的脚本,我已经在SQL Server 2008 R2 和SQL Server 2012测试过,但无法避免一些未知错误 。如果你自己在测试上面的脚本,请不要在生产环境上。如果你在应用过程,碰到有什么问题或有什么意见和建议可以发email联系我或跟帖,在此非常感谢!

分享:SQL2008定时任务作业创建教程
本文将详细介绍SQL2008定时任务作业创建步骤,需要的朋友可以参考下

来源:模板无忧//所属分类:Mssql数据库教程/更新时间:2013-04-23
loading.. 评论加载中....
相关Mssql数据库教程
闂傚倷鐒﹂惇褰掑春閸曨垰鍨傞梺顒€绉甸崑銈夋煛閸ヨ埖绶涚紓宥嗙墵閺屾盯鍩勯崘顏佸闂佹娊鏀辩敮妤呭Φ閸曨垰鍗虫俊銈傚亾濞存粍鍎抽—鍐Χ韫囨洜肖闂佺懓鍤栭幏锟�
濠电姷鏁搁崑娑㈩敋椤撶喐鍙忔い鎾卞灩绾惧鏌熼悙顒佺伇婵℃彃鐗撻弻锟犲磼濠靛洨銆婃繝鈷€鍕姇濞e洤锕幊鐘活敆婢跺棗浜炬繝闈涱儐閻撶喖鏌曡箛濠冩珔闁诲繑鐓¢弻鐔煎礄閵堝棗顏�
濠电姷鏁告慨鐑姐€傛禒瀣劦妞ゆ巻鍋撻柛鐔锋健閸┾偓妞ゆ帒瀚峰Λ鎴犵磼鏉堚晛浠滄い鎾冲悑瀵板嫮鈧綆浜濋楣冩煟鎼达絾鍤€閻庢凹鍘界粩鐔煎幢濞嗘劕搴婃繛鎴炴煣缁舵岸寮婚敐澶婎潊闁宠桨鑳舵导鍫ユ⒑閻熸澘娈╅柟鍑ゆ嫹
濠电姷鏁告慨鐑姐€傛禒瀣劦妞ゆ巻鍋撻柛鐔锋健閸┾偓妞ゆ帒瀚峰Λ鎴犵磼鏉堚晛浠滄い鎾冲悑瀵板嫭绻濋崟闈涙倯闂傚倷娴囬鏍垂鎼淬劌绀嬫い鎾楀嫅姘舵⒒娴g瓔鍤欓梺甯到宀h儻顦查摶鐐寸箾閹存瑥鐏柛瀣樀閺屻劑鎮ら崒娑橆伓
闂傚倸鍊烽懗鑸电仚缂備胶绮崝娆撶嵁婵犲啯鍎熼柕濞垮劤閸旓箑顪冮妶鍡楃瑨闁稿﹤鎽滈弫顕€宕奸弴鐐殿啇闁诲孩绋掑玻鍧楁儗閹烘柡鍋撶憴鍕缂佽鐗撳顐﹀箻缂佹ɑ娅㈤梺璺ㄥ櫐閹凤拷
闂傚倸鍊烽懗鑸电仚缂備胶绮崝娆撶嵁婵犲啯鍎熼柕濞垮劤閸旓箑顪冮妶鍡楀潑闁稿鎹囬弻娑橆潩椤掑鍓崇紓渚囧枛閿曨亪寮崒鐐茬鐟滃繘鎮¢幘缁樼厽閹兼惌鍨冲畝娑㈡煛閸涱喚绠為柟顖氳嫰閳诲酣骞嬮悩纰夌闯濠电偞鎸婚懝楣冩晝閵壯€鍋撳鐐
闂傚倸鍊峰ù鍥Υ閳ь剟鏌涚€n偅灏伴柕鍥у瀵粙濡歌濡插牓姊烘导娆忕槣闁革綇缍佸璇测槈濡攱鏂€闂佸綊鍋婃禍鐐烘嚄閾忓湱纾藉ù锝夋涧婵¤姤淇婇悙鑸殿棄妞ゆ洩缍佹俊鎼佸煛婵犲啯娅栨繝鐢靛仜濡瑩宕归悽鐢电當闁跨喓濮甸埛鎴︽煕濠靛棗顏╅柍褜鍓欓…鐑界嵁婵犲洤绠婚柤鍛婎問濞肩喖姊虹捄銊ユ珢闁瑰嚖鎷�
闂傚倸鍊风粈渚€骞栭锕€鐤柣妤€鐗婇崣蹇擃渻鐎n亝鎹g紒鈧繝鍌ょ唵闁兼悂娼ф慨鍫ユ煟閹垮嫮绉柟顔款潐濞碱亪骞忓畝濠傚Τ婵犵數鍋涢惇浼村磹閺囥垺绠掗梻浣瑰缁诲倸螞濞嗘垹鐭嗛柍褜鍓熷娲川婵炴碍鍨块幃褔鎮╅懠顒佹闂佽澹嗘晶妤呭疾閹间焦鐓ラ柣鏇炲€圭€氾拷
婵犵數濮烽。钘壩i崨鏉戝瀭妞ゅ繐鐗嗙粈鍫熺節闂堟稒锛嶉柣鏂挎娣囧﹪顢涘顒佸€柣搴㈡皑缁垶濡甸崟顖氱閻庨潧鎽滈悾娲煟鎼淬垻鍟查柟鍑ゆ嫹
濠电姷鏁告慨鐑姐€傛禒瀣劦妞ゆ巻鍋撻柛鐔锋健閸┾偓妞ゆ帒瀚峰Λ鎴犵磼鏉堚晛浠滄い鎾炽偢瀹曞爼濡搁妷顔荤穿濠碉紕鍋戦崐鏍箰閼姐倖宕查柛鎰典簴閸嬫挸顫濋搹顐ゅ涧婵烇絽娲ら敃顏堛€佸☉妯滄棃鍩€椤掍焦娅犻柛娆忣槺缁♀偓濡炪値鍋掗崰妤冣偓姘炬嫹
婵犵數濮烽。钘壩i崨鏉戝瀭妞ゅ繐鐗嗙粈鍫熺節闂堟稒锛嶉柣鏂挎閹便劌顪冪拠韫闁诲氦顫夊ú锕傚礈閻旂鈧礁鈻庨幘宕囶槯闂佺粯鎸哥花鍫曞磻閹剧粯鍋ㄩ柛娑橈功閸樻悂姊洪崜鎻掍簼缂佽瀚悺顓炩攽閻樻剚鍟忛柛锝庡灦楠炲繘鏁撻敓锟�
婵犵數濮烽。钘壩i崨鏉戝瀭妞ゅ繐鐗嗙粈鍫熺節闂堟稒锛嶉柣鏂挎閹便劌顪冪拠韫闂備礁鎼懟顖炲箠濮椻偓瀹曟椽鍩€椤掍降浜滈柟鍝勬娴滈箖姊虹€圭媭鍤欓梺甯秮閻涱噣骞嬮敃鈧粻娑㈡⒒閸喓鈯曟い鏂挎濮婄粯鎷呴崨濠冨創闁诲孩纰嶉幃鍌炵嵁韫囨稒鏅搁柨鐕傛嫹
婵犵數濮烽。钘壩i崨鏉戝瀭妞ゅ繐鐗嗙粈鍫熺節闂堟稒锛嶉柣鏂挎閹便劌顪冪拠韫闁诲氦顫夊ú鈺冪礊娓氣偓閻涱喚鈧綆浜栭弨浠嬫煕椤愵偄浜楃紒鎲嬫嫹
濠电姷鏁告慨鐑姐€傛禒瀣劦妞ゆ巻鍋撻柛鐔锋健閸┾偓妞ゆ帒瀚峰Λ鎴犵磼鏉堚晛浠滄い鎾冲悑瀵板嫭绻濋崟顓炲箑闂傚倷鑳堕幊鎾绘偤閵娧勫床闁告洦鍨奸弫鍌炴倵濞戞鑲╂崲閸℃ǜ浜滈柡宥庣厛濞堟柨顭胯瀵墎鎹㈠┑瀣剁稏妞ゆ棁妫勯锟�
闂傚倷娴囬褎顨ラ幖浣稿偍婵犲﹤鐗嗙粈鍫熺節闂堟侗鍎愰柛瀣儔閺岀喖骞嗛悧鍫闂佹椿鍘介〃鍛村煘閹达附鍋愰柟缁樺笚濞堫參姊烘總鍛婃锭缂傚秴锕濠氬Χ婢跺娈熼梺闈涱槶閸庨亶锝炲澶嬧拺缂備焦岣挎竟鍕磽瀹ュ嫮顦﹂柣锝呭槻閳诲酣骞樼€涙ɑ顏熼梻浣芥硶閸o箓骞忛敓锟�
UB闂傚倸鍊风粈浣革耿鏉堚晛鍨濇い鏍仜缁€澶嬩繆閵堝懏鍣界€规挷绶氶弻娑㈠Ψ閵忊剝鐝曢柣搴㈢瀹€鎼佸箖濡も偓閳藉鈻嶉搹顐㈢伌闁挎繄鍋ら弫鎾绘晸閿燂拷
闂傚倸鍊峰ù鍥ㄧ珶閸喆浠堢紒瀣儥濞兼牕鈹戦悩宕囶暡闁绘帡绠栭幃妤呮偨閻㈢偣鈧﹪鏌涚€n偅宕岄柣娑卞櫍瀹曞綊顢欓崣銉ф/濠电姷鏁告慨顓㈠磻閹剧粯鐓ラ柣鏇炲€圭€氾拷
闂傚倷娴囬褎顨ラ崫銉т笉鐎广儱顦崹鍌炴煕濠靛棗顏甸柤鏉挎健閺屾盯濮€閵忕姵顔�
闂傚倸鍊风粈渚€骞夐垾鎰佹綎缂備焦蓱閸欏繐顪冪€n亝鎹g紒鈧繝鍌ょ唵閻犻缚娅i悘閬嶆煕濡や胶顣茬紒缁樼洴瀹曞崬螖閳ь剟顢楅姀銈嗙厱闁绘劕妯婂Σ鍦磼缂佹ḿ銆掔紒杈ㄧ懇閹晠宕崟顐㈣厫闂傚倷鑳舵灙濡ょ姴绻橀獮蹇涙晸閿燂拷
缂傚倸鍊搁崐鎼佸磹閻戣姤鍊块柨鏇炲€归崑锟犳煥閺囩偛鈧悂宕归崒娑氱瘈濠电姴鍊搁顐c亜閳哄懏鏁遍柕鍥у楠炴帒顓奸崶鑸敌滅紓鍌欑劍瑜板啫锕㈤柆宥呯劦妞ゆ帒鍊告禒顖炴煕婵犲啰绠樼紒杈ㄦ尭椤繃锛愬┑鍥ㄦ珦闂備浇娉曢崳锕傚箯閿燂拷
©2017 www.mb5u.com婵犵數濮烽。钘壩i崨鏉戝瀭妞ゅ繐鐗嗙粈鍫熺節闂堟稒锛嶉柣鏂挎閹便劌顪冪拠韫闁诲孩顔栭崳顕€宕抽敐澶婄畺闁冲搫鎳庣粻鐢告煙閻戞ɑ灏柣锔兼嫹
闂傚倸鍊峰ù鍥Υ閳ь剟鏌涚€n偅宕岄柡宀€鍠栭、娑樷堪閸愮偓姣夋俊鐐€戦崕濠氬箯閿燂拷&闂傚倸鍊风粈渚€骞夐敍鍕殰闁圭儤鍤﹀☉銏犵睄闁割偆鍣ュΛ鍛存⒑鐠恒劌娅愰柟鍑ゆ嫹