一、存储过程加密
本方法可用于加密SQL存储过程、函数或者触发器
使用 WITH ENCRYPTION 选项：WITH ENCRYPTION 子句对用户隐藏存储过程的文本
例子：
IF OBJECT_ID(N'[Pro_Encrypt_Test]') IS NOT NULL
DROP PROCEDURE [Pro_Encrypt_Test]
GO
CREATE PROCEDURE [Pro_Encrypt_Test]
WITH ENCRYPTION
AS
BEGIN
SELECT * FROM T_TEST
END
GO
建好存储过程后，用下面的语句查看存储过程
EXEC sp_helptext Pro_Encrypt_Test
结果是：
The text for object 'Pro_Encrypt_Test' is encrypted.
把存储过程加密后，可以不让别人在数据库中直接看到存储过程的内容。
当然网上存在不少解密的方法，所以这样的加密不是真正安全的，只是增加别人查看存储过程的难度而已。
二、存储过程解密
IF OBJECT_ID ( N'SP_SQLJM2008', N'P' ) IS NOT NULL
DROP PROCEDURE SP_SQLJM2008
GO
CREATE PROCEDURE SP_SQLJM2008 ( @ObjectName VARCHAR(50) )
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
--------------------------- CSDN：j9988 copyright:2004.01.05
--------------------------- V3.1
--------------------------- 破解字节不受限制，适用于SQLSERVER2000, SQLSERVER2005存储过程，函数，视图，触发器
--------------------------- 发现有错，请E_MAIL：CSDNj9988@tom.com
--------------------------- 整理SQLSERVER2000、注释：郭长海
--------------------------- 整理SQLSERVER2005、注释：郭长海
--------------------------- 整理SQLSERVER2008、注释：郭长海
DECLARE @Version CHAR(4),
@ObjectID INT
DECLARE @n INT,
@j INT,
@k INT,
@i_copy INT
DECLARE @colid INT,
@encrypted INT,
@MaxNumber INT
DECLARE @ExecSqlTxt NVARCHAR (4000),
@TestSqlTxt NVARCHAR (4000),
@CHR NVARCHAR(4000)
DECLARE @OrigSpText1 NVARCHAR (4000),
@OrigSpText2 NVARCHAR (4000),
@OrigSpText3 NVARCHAR (4000),
@ResultSql NVARCHAR (4000),
@ResultLen INT
DECLARE @i INT,
@status INT,
@type VARCHAR ( 10 ),
@parentid INT
DECLARE @ConnectText NVARCHAR (4000),
@PrintText NVARCHAR (4000),
@Pos INT,
@Temp INT,
@Line INT
------------------------------------------------------------------------------------------------------------
SELECT @ObjectID = OBJECT_ID ( @ObjectName )
SELECT @type = xtype, @parentid = parent_obj FROM SYSOBJECTS WHERE ID = @ObjectID
SELECT @Version = CASE WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2000%' THEN '2000'
WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2005%' THEN '2005'
WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2008%' THEN '2008' END
IF @Version NOT IN ('2000', '2005', '2008')
RETURN
BEGIN TRAN
SELECT id, number, colid, ctext, encrypted, status
INTO #TempSysComments1
FROM SYSCOMMENTS WHERE ID = @ObjectID
SELECT @MaxNumber = MAX ( number ) FROM #TempSysComments1
IF @Version IN ('2005', '2008')
BEGIN
SET @i_copy = 1
WHILE EXISTS(SELECT colid FROM #TempSysComments1 WHERE colid = @i_copy)
BEGIN
UPDATE #TempSysComments1
SET ctext = (SELECT TOP 1 SUBSTRING(imageval, (@i_copy - 1)*8000 + 1, 8000) FROM sys.sysobjvalues WHERE objid = @ObjectID AND valclass = 1 ORDER BY subobjid )
WHERE colid = @i_copy
SET @i_copy = @i_copy + 1
END
END
------ 设计同名测试明文，创建测试密文。
SET @k = 0
WHILE @k <= @MaxNumber
BEGIN
IF EXISTS ( SELECT 1 FROM SYSCOMMENTS WHERE ID = @ObjectID AND number = @k )
BEGIN
------ 设计测试 ALTER 明文
IF @type = 'P'
SET @TestSqlTxt = ( CASE
WHEN @MaxNumber > 1 THEN
'ALTER PROCEDURE ' + @ObjectName + ';' + RTRIM ( @k ) + ' WITH ENCRYPTION AS '
ELSE
'ALTER PROCEDURE ' + @ObjectName + ' WITH ENCRYPTION AS '
END )
IF @type = 'TR'
SET @TestSqlTxt = 'ALTER TRIGGER ' + @ObjectName + ' ON ' + OBJECT_NAME ( @parentid ) + ' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
IF @type = 'FN' OR @type = 'TF' OR @type = 'IF'
SET @TestSqlTxt = ( 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 @TestSqlTxt = 'ALTER VIEW ' + @ObjectName + ' WITH ENCRYPTION AS SELECT 1 AS f'
---------------------------------------------------------------------------
------ 创建测试密文
SET @CHR = REPLICATE(N'-', 4000)
SET @ExecSqlTxt = N'EXEC(@SQL'
SELECT @ExecSqlTxt = @ExecSqlTxt + N'+@' FROM #TempSysComments1 WHERE ID = @ObjectID
SET @ExecSqlTxt = @ExecSqlTxt + N')'
EXEC sp_executesql @ExecSqlTxt, N'@SQL NVARCHAR(4000), @ NVARCHAR(4000)', @SQL = @TestSqlTxt, @ = @CHR
SELECT id, number, colid, ctext, encrypted, status
INTO #TempSysComments2
FROM SYSCOMMENTS WHERE ID = @ObjectID
IF @Version IN ('2005', '2008')
BEGIN
SET @i_copy = 1
WHILE EXISTS(SELECT colid FROM #TempSysComments2 WHERE colid = @i_copy)
BEGIN
UPDATE #TempSysComments2
SET ctext = (SELECT TOP 1 SUBSTRING(imageval, (@i_copy - 1)*8000 + 1, 8000) FROM sys.sysobjvalues WHERE objid = @ObjectID AND valclass = 1 ORDER BY subobjid )
WHERE ID = @ObjectID AND colid = @i_copy
SET @i_copy = @i_copy + 1
END
END
SELECT id, number, colid, ctext, encrypted, status
INTO #TempSysComments3
FROM SYSCOMMENTS WHERE ID = @ObjectID
UPDATE #TempSysComments3 SET ctext = CONVERT(VARBINARY(8000), CONVERT(NVARCHAR(4000), REPLICATE('-', 8000))) WHERE ID = @ObjectID
UPDATE #TempSysComments3 SET ctext = CONVERT(VARBINARY(8000), CONVERT(NVARCHAR(4000), REPLACE(@TestSqlTxt,'ALTER','CREATE') + REPLICATE('-', 8000))) WHERE ID = @ObjectID AND colid = 1
END
SET @k = @k + 1
END
------------------------------------------------------------------------------------------------------------
------ 由测试明文与测试密文进行异或运算得出密匙，由密匙与原密文进行异或运算得出原明文。
------ 取测试密文
SET @Line = 0
SET @ConnectText = N''
SET @k = 0
WHILE @k <= @MaxNumber
BEGIN
IF EXISTS ( SELECT 1 FROM SYSCOMMENTS WHERE ID = @ObjectID AND number = @k )
BEGIN
SELECT @colid = MAX ( colid ) FROM #TempSysComments1 WHERE ID = @ObjectID AND number = @k
SET @n = 1
WHILE @n <= @colid
BEGIN
------ 取原文密文
SELECT @OrigSpText1 = ctext, @encrypted = encrypted, @status = status FROM #TempSysComments1 WHERE ID = @ObjectID AND colid = @n AND number = @k
IF @encrypted = 1
BEGIN
------ 取测试密文
SELECT @OrigSpText2 = ctext FROM #TempSysComments2 WHERE ID = @ObjectID AND colid = @n AND number = @k
------ 取测试明文
SELECT @OrigSpText3 = ctext FROM #TempSysComments3 WHERE ID = @ObjectID AND colid = @n AND number = @k
SET @ResultSql = N''
SET @ResultLen = DATALENGTH ( @OrigSpText1 ) / 2
SET @i = 1
WHILE @i <= @ResultLen
BEGIN
SET @ResultSql = @ResultSql + NCHAR(UNICODE(SUBSTRING(@OrigSpText1, @i, 1 )) ^
UNICODE(SUBSTRING(@OrigSpText2, @i, 1 )) ^
UNICODE(SUBSTRING(@OrigSpText3, @i, 1 )))
SET @i = @i + 1
END
END
ELSE
BEGIN
SELECT @ResultSql = CONVERT ( NVARCHAR (4000), CASE WHEN @status & 2 = 2 THEN uncompress ( ctext ) ELSE ctext END )
FROM #TempSysComments1 WHERE ID = @ObjectID AND colid = @n AND number = @k
END
------------------------------------------------------------------
---- 对解密后的文件进行显示格式还原
SET @Temp = 1
SET @Pos = 0
WHILE @Temp > 0
BEGIN
SET @Temp = CHARINDEX(NCHAR(13)+NCHAR(10), @ResultSql)
IF @Temp > 0
BEGIN
SET @PrintText = SUBSTRING (@ResultSql, 1, @Temp - 1)
SET @PrintText = ISNULL (@PrintText, '')
SET @ResultSql = SUBSTRING (@ResultSql, @Temp + 2, LEN(@ResultSql) - LEN(@PrintText) - 2)
SET @ResultSql = ISNULL (@ResultSql, '')
IF @Pos = 0
BEGIN
SET @PrintText = @ConnectText + @PrintText
SET @ConnectText = N''
END
SET @Pos = CHARINDEX(NCHAR(13)+NCHAR(10), @PrintText)
IF @Pos > 0
BEGIN
SET @ResultSql = SUBSTRING (@PrintText, @Pos + 2, LEN(@PrintText) - @Pos - 1) + NCHAR(13) + NCHAR(10) + @ResultSql
SET @PrintText = SUBSTRING (@PrintText, 1, @Pos - 1)
END
IF @Version IN ('2005', '2008')
BEGIN
SET @Line = (@Line + 10) / 10 * 10
SET @PrintText = REPLACE(@PrintText, N' ', N'^=*')
WHILE LEN(@PrintText) > 0
BEGIN
IF LEN(@PrintText) > 100
BEGIN
PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + SUBSTRING(@PrintText, 1, 100)
SET @PrintText = SUBSTRING(@PrintText, 101, LEN(@PrintText) - 100)
SET @Line = @Line + 1
END
ELSE
BEGIN
PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + @PrintText
SET @PrintText = ''
SET @Line = @Line + 1
END
END
END
IF @Version = '2000'
PRINT @PrintText
SET @Pos = 1
END
ELSE
BEGIN
SET @ConnectText = @ConnectText + @ResultSql
SET @Temp = CHARINDEX(NCHAR(13)+NCHAR(10), @ConnectText)
IF @Temp > 0
BEGIN
SET @ResultSql = @ConnectText
SET @ConnectText = N''
END
END
END
------------------------------------------------------------------
SET @n = @n + 1
END
END
SET @k = @k + 1
END
IF @Version IN ('2005', '2008')
BEGIN
SET @PrintText = @ConnectText
SET @PrintText = REPLACE(@PrintText, N' ', N'^=*')
IF LEN(@PrintText) > 0
SET @Line = (@Line + 10) / 10 * 10
WHILE LEN(@PrintText) > 0
BEGIN
IF LEN(@PrintText) > 100
BEGIN
PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + SUBSTRING(@PrintText, 1, 100)
SET @PrintText = SUBSTRING(@PrintText, 101, LEN(@PrintText) - 100)
SET @Line = @Line + 1
END
ELSE
BEGIN
PRINT RIGHT(N'0000000000' + CONVERT(NVARCHAR(10), @Line), 10 ) + @PrintText
SET @PrintText = ''
SET @Line = @Line + 1
END
END
END
IF @Version = '2000'
PRINT @ConnectText
------------------------------------------------------------------------------------------------------------
DROP TABLE #TempSysComments1
DROP TABLE #TempSysComments2
DROP TABLE #TempSysComments3
ROLLBACK TRAN
END
---------------------------------------------------------------------------------------------------------------------------
GO
IF OBJECT_ID ( N'SP_SQLJM', N'P' ) IS NOT NULL
DROP PROCEDURE SP_SQLJM
GO
CREATE PROCEDURE SP_SQLJM ( @ObjectName VARCHAR(50) )
WITH ENCRYPTION
AS
BEGIN
DECLARE @dossqlcmd VARCHAR(8000)
DECLARE @SQL_PRINT NVARCHAR(4000)
DECLARE @SQL_SYNTAX NVARCHAR(4000)
DECLARE @Version CHAR(4)
DECLARE @Line INT
SELECT @Version = CASE WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2000%' THEN '2000'
WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2005%' THEN '2005'
WHEN SUBSTRING(@@Version, 1, 30) LIKE '%2008%' THEN '2008' END
IF @Version IN ('2005', '2008')
BEGIN
---- SELECT @dossqlcmd = 'sqlcmd -S '+RTRIM(ps.hostname)+' -d '+db.name+' -U '+@DBUser+' -P '+@DBPass+' -A -q "EXEC SP_JM1 ''' + @ObjectName + '''"'
SELECT @dossqlcmd = 'sqlcmd -A -q "EXEC '+db.name+'..SP_SQLJM2008 ''' + @ObjectName + '''"'
FROM master..sysprocesses ps, master..sysdatabases db
WHERE ps.spid = @@spid AND ps.dbid = db.dbid
IF OBJECT_ID('tempdb..#TEMP_SYNTAX') IS NOT NULL
DROP TABLE #TEMP_SYNTAX
CREATE TABLE #TEMP_SYNTAX ( ctext NVARCHAR (4000) )
INSERT INTO #TEMP_SYNTAX( ctext )
EXEC master..xp_cmdshell @dossqlcmd
SET @Line = 10
SET @SQL_PRINT = N''
----- select * from #TEMP_SYNTAX where ISNUMERIC ( SUBSTRING(ctext, 1, 10) ) = 0 ORDER BY ctext
DECLARE CUR_SYNTAX CURSOR FOR
SELECT ISNULL(ctext, N'') FROM #TEMP_SYNTAX WHERE ctext IS NOT NULL ORDER BY ctext
OPEN CUR_SYNTAX
FETCH NEXT FROM CUR_SYNTAX INTO @SQL_SYNTAX
WHILE @@FETCH_STATUS = 0
BEGIN
IF ISNUMERIC ( SUBSTRING(@SQL_SYNTAX, 1, 10) ) = 1
BEGIN
IF CONVERT(INT, SUBSTRING(@SQL_SYNTAX, 1, 10)) % 10 = 0
BEGIN
SET @SQL_PRINT = REPLACE(@SQL_PRINT, N'^=*', N' ')
IF LEN(@SQL_PRINT) > 0
PRINT @SQL_PRINT
SET @SQL_PRINT = SUBSTRING(@SQL_SYNTAX, 11, LEN(@SQL_SYNTAX) - 10)
END
ELSE
BEGIN
IF LEN(@SQL_SYNTAX) > 10
SET @SQL_PRINT = @SQL_PRINT + SUBSTRING(@SQL_SYNTAX, 11, LEN(@SQL_SYNTAX) - 10)
END
END
FETCH NEXT FROM CUR_SYNTAX INTO @SQL_SYNTAX
END
SET @SQL_PRINT = REPLACE(@SQL_PRINT, N'^=*', N' ')
IF LEN(@SQL_PRINT) > 0
PRINT @SQL_PRINT
CLOSE CUR_SYNTAX
DEALLOCATE CUR_SYNTAX
IF OBJECT_ID('tempdb..#TEMP_SYNTAX') IS NOT NULL
DROP TABLE #TEMP_SYNTAX
END
IF @Version = '2000'
BEGIN
EXEC SP_SQLJM2008 @ObjectName
END
END
GO
---- 调用解密对象
---- 如果提示“SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问。”，则执行下面的代码。
/*
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
*/
---- 如果提示“配置选项 'xp_cmdshell' 不存在，也可能是高级选项。”，则执行下面的代码。
/*
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
*/
EXEC SP_SQLJM 'Up_HANDle_Stock'
摘自：