SQL Server存储过程文本加密与解密过程详解

2 年前 · 来自专栏 SQL ETL Shell案例实战

沈亮

加密概述

SQL Server的存储过程支持对其创建脚本的加密，即即便是数据库管理员也无法查看其脚本内容。本文即介绍一种方法对存储过程的加密和解密。

加密实例

加密存储过程

创建示例加密存储过程见下(即加了WITH ENCRYPTION选项，脚本内容不可见)：

Create PROCEDURE usp_inlight
WITH ENCRYPTION
BEGIN
	print 'Can not see me! 1 '
	print 'Can not see me! 2 '
	print 'Can not see me! 3 '
	print 'Can not see me! 4'
	print 'Can not see me! 5'
	print 'Can not see me! 6'
END

验证加密

此时我们通过SQL Server Management Studio（SSMS）SQL Server图形化管理工具查看该存储过程时无法查看脚本内容，无法点击“修改”按钮。

1）修改按钮灰色

2）查看存储过程脚本

解密

解密需要SQL在专用管理员连接( Dedicated Admin Connection )DAC下运行，首先我们需要打开远程连接。

指定远程连接DAC

指定连接允许远程连接 DAC的模式，其中0是本地、1是可以远程。

SP_CONFIGURE  'remote admin connections';
SP_CONFIGURE 'remote admin connections', 1;
RECONFIGURE WITH OVERRIDE;
GO

SSMS连接DAC

不奏效方法：

SSMS连接DAC时不能直接通过对象连接器直接进入该模式，详见如下演示步骤：

正确方法：

SSMS里正确连接DAC的方法，是先通过普通方式连接进去数据库，新建立个查询，然后在空白处右键“更改连接”，输入DAC模式的连接地址和用户登录信息。

验证DAC连接

在SSMS里执行如下查询，查看DAC连接会话信息。

select 
@@SPID spid,
WHEN es.session_id= @@SPID THEN 'you '
ELSE es.host_name
END AS DAC_runner,
es.original_login_name,
es.session_id,
es.login_time,
es.status
from sys.endpoints as ep
join sys.dm_exec_sessions es on
ep.endpoint_id=es.endpoint_id
where ep.name='Dedicated Admin Connection'

创建解密存储过程

USE [ShenLiang2025]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER OFF
CREATE PROCEDURE [dbo].[usp_decrypt] 
(@procedure  varchar(100)) 
SET NOCOUNT ON 
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int 
select @maxColID = max(subobjid) FROM 
sys.sysobjvalues WHERE objid = object_id(@procedure) 
--select @maxColID as 'Rows in sys.sysobjvalues' 
select @procNameLength = datalength(@procedure) + 29 
DECLARE @real_01 nvarchar(max) 
DECLARE @fake_01 nvarchar(max) 
DECLARE @fake_encrypt_01 nvarchar(max) 
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max) 
declare @objtype varchar(2),@parentname nvarchar(max) 
select @real_decrypt_01a = '' 
--提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称 
select @objtype=type,@parentname=object_name(parent_object_id) 
from sys.objects where [object_id]=object_id(@procedure) 
-- 从sys.sysobjvalues里提出加密的imageval记录 
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid = 
object_id(@procedure) and valclass = 1 order by subobjid) 
--创建一个临时表 
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL , 
[real_decrypt] NVARCHAR(MAX) ) 
--开始一个事务，稍后回滚 
BEGIN TRAN 
--更改原始的存储过程，用短横线替换 
if @objtype='P' 
  SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS 
  '+REPLICATE(cast('-'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength) 
else if @objtype='FN' 
  SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END' 
else if @objtype='V' 
  SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' 
else if @objtype='TR' 
  SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITHENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' 
EXECUTE (@fake_01) 
--从sys.sysobjvalues里提出加密的假的 
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid = 
object_id(@procedure) and valclass = 1 order by subobjid ) 
if @objtype='P' 
  SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS 
  '+REPLICATE(cast('-'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength) 
else if @objtype='FN' 
  SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END' 
else if @objtype='V' 
  SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' 
else if @objtype='TR' 
  SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITHENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) 
  /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' 
--开始计数 
SET @intProcSpace=1 
--使用字符填充临时变量 
SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 )) 
--循环设置每一个变量，创建真正的变量 
--每次一个字节 
SET @intProcSpace=1 
--如有必要，遍历每个@real_xx变量并解密 
WHILE @intProcSpace <=(datalength(@real_01)/2) 
BEGIN 
--真的和假的和加密的假的进行异或处理 
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1, 
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^ 
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^ 
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1))))) 
SET @intProcSpace=@intProcSpace+1 
--通过sp_helptext逻辑向表#output里插入变量 
insert #output (real_decrypt) select @real_decrypt_01 
-- select real_decrypt AS '#output chek' from #output --测试 
-- ------------------------------------- 
--开始从sp_helptext提取 
-- ------------------------------------- 
declare @dbname sysname 
,@BlankSpaceAdded int 
,@BasePos int 
,@CurrentPos int 
,@TextLength int 
,@LineId int 
,@AddOnLen int 
,@LFCR int --回车换行的长度 
,@DefinedLength int 
,@SyscomText nvarchar(max) 
,@Line nvarchar(4000) 
Select @DefinedLength = 4000 
SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格 
CREATE TABLE #CommentText 
(LineId int 
,Text nvarchar(4000) collate database_default) 
--使用#output代替sys.sysobjvalues 
DECLARE ms_crs_syscom CURSOR LOCAL 
FOR SELECT real_decrypt from #output 
ORDER BY ident 
FOR READ ONLY 
--获取文本 
SELECT @LFCR = 2 
SELECT @LineId = 1 
OPEN ms_crs_syscom 
FETCH NEXT FROM ms_crs_syscom into @SyscomText 
WHILE @@fetch_status >= 0 
BEGIN 
SELECT @BasePos = 1 
SELECT @CurrentPos = 1 
SELECT @TextLength = LEN(@SyscomText) 
WHILE @CurrentPos != 0 
BEGIN 
--通过回车查找行的结束 
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, 
@BasePos) 
--如果找到回车 
IF @CurrentPos != 0 
BEGIN 
--如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续 
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + 
@CurrentPos-@BasePos + @LFCR) > @DefinedLength 
BEGIN 
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + 
@BlankSpaceAdded) 
INSERT #CommentText VALUES 
( @LineId, 
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, 
@BasePos, @AddOnLen), N'')) 
SELECT @Line = NULL, @LineId = @LineId + 1, 
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 
SELECT @Line = isnull(@Line, N'') + 
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'') 
SELECT @BasePos = @CurrentPos+2 
INSERT #CommentText VALUES( @LineId, @Line ) 
SELECT @LineId = @LineId + 1 
SELECT @Line = NULL 
--如果回车没找到 
BEGIN 
IF @BasePos <= @TextLength 
BEGIN 
--如果@Lines长度的新值大于定义的长度 
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + 
@TextLength-@BasePos+1 ) > @DefinedLength 
BEGIN 
SELECT @AddOnLen = @DefinedLength - 
(isnull(LEN(@Line),0) + @BlankSpaceAdded) 
INSERT #CommentText VALUES 
( @LineId, 
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, 
@BasePos, @AddOnLen), N'')) 
SELECT @Line = NULL, @LineId = @LineId + 1, 
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 
SELECT @Line = isnull(@Line, N'') + 
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'') 
if LEN(@Line) < @DefinedLength and charindex(' ', 
@SyscomText, @TextLength+1 ) > 0 
BEGIN 
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1 
FETCH NEXT FROM ms_crs_syscom into @SyscomText 
IF @Line is NOT NULL 
INSERT #CommentText VALUES( @LineId, @Line ) 
select Text from #CommentText order by LineId 
CLOSE ms_crs_syscom 
DEALLOCATE ms_crs_syscom 
DROP TABLE #CommentText