0

SQL Server 数据加密与解密

已有 104 阅读此文人 - - 数据库 -

前段时间在客户公司,有一个很活泼的程序员给我展示他写的SQL Server 加密功能。由于测试库和正式库在同一台服务器里,于是一个不小心把正式库的所有存储过程、函数、视图全加密了。还好加密的方式只是在 AS 位置前增加了 WITH ENCRYPTION ,虽然不能查看内容但不影响程序的运行。

即使是这种简单的加密,解密过程也很复杂。为了缓解他焦灼的心情,于是我接下了这个解密的任务。在各种查找和尝试后,问题解决了。顺便也整理了全套加密和解密的方法,以便不时之需。

加密过程

可以参考这篇文章来创建加密的存储过程:(sp_EncryptObject)。此存储过程的方法是在存储过程、函数或视图的“As”位置前加上“with encryption”。如果是触发器的话就在“for”之前加“with encryption”。

具体实现代码如下:(SQL Server 2012 和 2016测试OK)

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

执行加密

use test
go
exec sp_EncryptObject 'all' --加密所有内容,指定名称加密指定内容
go

解密过程

因为我所遇到的情况涉及的存储过程比较多,一个个解密不现实。于是在网上搜到了这个工具:dbForge SQL Decryptor 。这是一款免费软件和SSMS长得比较像。这款软件如果用常规账户连接的话解密速度特别慢,推荐使用DAC方式连接,速度飞快。

服务器默认DAC连接是关闭的,可以在服务器示例右键→Facets→外围应用配置器中启用远程DAC。

SQL Server 数据加密与解密

dbForge SQL Decryptor通过DAC连接服务器:

SQL Server 数据加密与解密

批量解密所有加密存储过程、函数、视图和触发器。

SQL Server 数据加密与解密

SSMS也可以使用DAC连接

SSMS通过DAC连接只能在文件→新建→数据库引擎查询处登录。其中服务器名称前要加 admin: ,登录完成默认只显示空白的编辑窗口。


参考文献:

  1. 批量解密SQLSERVER数据库中的各种对象的工具dbForge SQL Decryptor2.1.11
  2. 对存储过程进行加密和解密(SQL 2008/SQL 2012)
  3. MS SQL专用管理员连接DAC
期待你一针见血的评论,Come on!