--数据库加密解密
----加密存储过程：
CREATE PROCEDURE po
WITH ENCRYPTION
AS
SELECT * FROM ta
GO
--查看加密信息
EXEC sp_helptext po
---创建解密存储过程
CREATE PROCEDURE sp_decrypt(@objectname VARCHAR(50))
AS
BEGIN
SET nocount ON
--CSDN：j9988 copyright:2004.07.15
--V3.2
--破解字节不受限制，适用于SQLSERVER2000存储过程，函数，视图，触发器
--修正上一版"视图触发器"不能正确解密错误
--发现有错，请E_MAIL：CSDNj9988@tom.com
BEGIN tran
DECLARE @objectname1 VARCHAR(100),@orgvarbin varbinary(8000)
DECLARE @sql1 nvarchar(4000),@sql2 VARCHAR(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
DECLARE @i INT,@STATUS INT,@TYPE VARCHAR(10),@parentid INT
DECLARE @colid INT,@n INT,@q INT,@j INT,@k INT,@encrypted INT,@NUMBER INT
SELECT @TYPE=xtype,@parentid=parent_obj FROM sysobjects WHERE id=object_id(@objectname)
CREATE TABLE #temp(NUMBER INT,colid INT,ctext varbinary(8000),encrypted INT,STATUS INT)
INSERT #temp SELECT NUMBER,colid,ctext,encrypted,STATUS FROM syscomments WHERE id = object_id(@objectname)
SELECT @NUMBER=MAX(NUMBER) FROM #temp
SET @k=0
while @k<=@NUMBER
BEGIN
IF EXISTS(SELECT 1 FROM syscomments WHERE id=object_id(@objectname) AND NUMBER=@k)
BEGIN
IF @TYPE='P'
SET @sql1=(CASE WHEN @number>1 THEN 'Alter PROCEDURE '+ @objectname +';'+rtrim(@k)+' WITH ENCRYPTION AS '
ELSE 'Alter PROCEDURE '+ @objectname+' WITH ENCRYPTION AS '
END)
IF @TYPE='TR'
BEGIN
DECLARE @parent_obj VARCHAR(255),@tr_parent_xtype VARCHAR(10)
SELECT @parent_obj=parent_obj FROM sysobjects WHERE id=object_id(@objectname)
SELECT @tr_parent_xtype=xtype FROM sysobjects WHERE id=@parent_obj
IF @tr_parent_xtype='V'
BEGIN
SET @sql1='Alter TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF Insert AS PRINT 1 '
END
ELSE
BEGIN
SET @sql1='Alter TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR Insert AS PRINT 1 '
END
END
IF @TYPE='FN' OR @TYPE='TF' OR @TYPE='IF'
SET @sql1=(CASE @TYPE WHEN '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 as f'
SET @q=len(@sql1)
SET @sql1=@sql1+REPLICATE('-',4000-@q)
SELECT @sql2=REPLICATE('-',8000)
SET @sql3='exec(@sql1'
SELECT @colid=MAX(colid) FROM #temp WHERE NUMBER=@k
SET @n=1
while @n<=CEILING(1.0*(@colid-1)/2) AND len(@sql3)<=3996
BEGIN
SET @sql3=@sql3+'+@'
SET @n=@n+1
END
SET @sql3=@sql3+')'
EXEC sp_executesql @sql3,N'@sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2
END
SET @k=@k+1
END
SET @k=0
while @k<=@NUMBER
BEGIN
IF EXISTS(SELECT 1 FROM syscomments WHERE id=object_id(@objectname) AND NUMBER=@k)
BEGIN
SELECT @colid=MAX(colid) FROM #temp WHERE NUMBER=@k
SET @n=1
while @n<=@colid
BEGIN
SELECT @OrigSpText1=ctext,@encrypted=encrypted,@STATUS=STATUS FROM #temp WHERE colid=@n AND NUMBER=@k
SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectname) AND colid=@n AND NUMBER=@k)
IF @n=1
BEGIN
IF @TYPE='P'
SET @OrigSpText2=(CASE WHEN @number>1 THEN '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 @TYPE WHEN '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'
BEGIN
IF @tr_parent_xtype='V'
BEGIN
SET @OrigSpText2='Create TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF Insert AS PRINT 1 '
END
ELSE
BEGIN
SET @OrigSpText2='Create TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR Insert AS PRINT 1 '
END
END
IF @TYPE='V'
SET @OrigSpText2='Create VIEW '+@objectname+' WITH ENCRYPTION AS Select 1 as f'
SET @q=4000-len(@OrigSpText2)
SET @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
END
ELSE
BEGIN
SET @OrigSpText2=REPLICATE('-', 4000)
END
SET @i=1
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(SUBSTRING(@OrigSpText1, @i, 1)) ^
(UNICODE(SUBSTRING(@OrigSpText2, @i, 1)) ^
UNICODE(SUBSTRING(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
SET @orgvarbin=CAST(@OrigSpText1 AS varbinary(8000))
SET @resultsp=(CASE WHEN @encrypted=1
THEN @resultsp
ELSE CONVERT(nvarchar(4000),CASE WHEN @status&2=2 THEN uncompress(@orgvarbin) ELSE @orgvarbin END)
END)
print @resultsp
SET @n=@n+1
END
END
SET @k=@k+1
END
DROP TABLE #temp
ROLLBACK tran
END
--调用解密存储过程将加密的存储过程进行解密
EXEC sp_decrypt po
//SQL/818