mysql字段加密存储过程_MsSql数据库存储过程加密解密

----------------------------------------------------------------------------------------------------------------------------------------------------------------存储过程、存储函数的加密：WITHENCRYPTIONCREATEproceduredbo.sp_XML_main@table_namenvarchar(260)='',@dirnamenvarchar(20)=''WITHENCRYPTIONasbegin....................................................endgo------------------------------------------------------------------------------------------------------------------------------------------------------------------解密指定存储过程execsp_decrypt'存储过程名'createPROCEDUREsp_decrypt(@objectNamevarchar(50))ASbeginbegintrandeclare@objectname1varchar(100),@orgvarbinvarbinary(8000)declare@sql1nvarchar(4000),@sql2nvarchar(4000),@sql3nvarchar(4000),@sql4nvarchar(4000),@sql5nvarchar(4000),@sql6nvarchar(4000),@sql7nvarchar(4000),@sql8nvarchar(4000),@sql9nvarchar(4000),@sql10nvarchar(4000)DECLARE@OrigSpText1nvarchar(4000),@OrigSpText2nvarchar(4000) ,@OrigSpText3nvarchar(4000),@resultspnvarchar(4000)declare@iint,@statusint,@typevarchar(10),@parentidintdeclare@colidint,@nint,@qint,@jint,@kint,@encryptedint,@numberintselect@type=xtype,@parentid=parent_objfromsysobjectswhereid=object_id(@ObjectName)createtable#temp(numberint,colidint,ctextvarbinary(8000),encryptedint,statusint)insert#tempSELECTnumber,colid,ctext,encrypted,statusFROMsyscommentsWHEREid=object_id(@objectName)select@number=max(number)from#tempset@k=0while@k<=@numberbeginifexists(select1fromsyscommentswhereid=object_id(@objectname)andnumber=@k)beginif@type='P'set@sql1=(casewhen@number>1then'ALTER PROCEDURE'+@objectName+';'+rtrim(@k)+'WITH ENCRYPTION AS'else'ALTER PROCEDURE'+@objectName+'WITH ENCRYPTION AS'end)if@type='TR'set@sql1='ALTER TRIGGER'+@objectname+'ON'+OBJECT_NAME(@parentid)+'WITH ENCRYPTION FOR INSERT AS PRINT 1'if@type='FN'or@type='TF'or@type='IF'set@sql1=(case@typewhen'TF'then'ALTER FUNCTION'+@objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end'when'FN'then'ALTER FUNCTION'+@objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'when'IF'then'ALTER FUNCTION'+@objectName+'(@a char(1)) returns table with encryption as return select @a as a'end)if@type='V'set@sql1='ALTER VIEW'+@objectname+'WITH ENCRYPTION AS SELECT 1'set@q=len(@sql1)set@sql1=@sql1+REPLICATE('-',4000-@q)select@sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)endset@k=@k+1endset@k=0while@k<=@numberbeginifexists(select1fromsyscommentswhereid=object_id(@objectname)andnumber=@k)beginselect@colid=max(colid)from#tempwherenumber=@kset@n=1while@n<=@colidbeginselect@OrigSpText1=ctext,@encrypted=encrypted,@status=statusFROM#tempWHEREcolid=@nandnumber=@kSET@OrigSpText3=(SELECTctextFROMsyscommentsWHEREid=object_id(@objectName)andcolid=@nandnumber=@k)if@n=1beginif@type='P'SET@OrigSpText2=(casewhen@number>1then'CREATE PROCEDURE'+@objectName+';'+rtrim(@k)+'WITH ENCRYPTION AS'else'CREATE PROCEDURE'+@objectName+'WITH ENCRYPTION AS'end)if@type='FN'or@type='TF'or@type='IF'--刚才有错改一下SET@OrigSpText2=(case@typewhen'TF'then'CREATE FUNCTION'+@objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end'when'FN'then'CREATE FUNCTION'+@objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'when'IF'then'CREATE FUNCTION'+@objectName+'(@a char(1)) returns table with encryption as return select @a as a'end)if@type='TR'set@OrigSpText2='CREATE TRIGGER'+@objectname+'ON'+OBJECT_NAME(@parentid)+'WITH ENCRYPTION FOR INSERT AS PRINT 1'if@type='V'set@OrigSpText2='CREATE VIEW'+@objectname+'WITH ENCRYPTION AS SELECT 1'set@q=4000-len(@OrigSpText2)set@OrigSpText2=@OrigSpText2+REPLICATE('-',@q)endelsebeginSET@OrigSpText2=REPLICATE('-',4000)end--start counterSET@i=1--fill temporary variableSET@resultsp=replicate(N'A', (datalength(@OrigSpText1)/2))--loopWHILE@i<=datalength(@OrigSpText1)/2BEGINSET@resultsp=stuff(@resultsp,@i,1,NCHAR(UNICODE(substring(@OrigSpText1,@i,1))^(UNICODE(substring(@OrigSpText2,@i,1))^UNICODE(substring(@OrigSpText3,@i,1)))))SET@i=@i+1ENDset@orgvarbin=cast(@OrigSpText1asvarbinary(8000))set@resultsp=(casewhen@encrypted=1then@resultspelseconvert(nvarchar(4000),casewhen@status&2=2thenuncompress(@orgvarbin)else@orgvarbinend)end)print@resultsp--execute( @resultsp)set@n=@n+1endendset@k=@k+1enddroptable#temprollbacktranendGO