如何加密数据库的所有现有存储过程

Is there any possibility to encrypt all existing stored procedures of a SQL Server 2008 database AFTER they have been created via an SQLCMD script?

是否有可能在通过SQLCMD脚本创建SQL Server 2008数据库之后加密它们的所有现有存储过程?

The reason I want to do this is the following:
I'd like to develop the stored procedures without encryption so I can easily click on "Modify" in SQL Server Management Studio to check their contents.
However, for the deployment I'd like to encrypt them so I thought that maybe I could write a script which encrypts them only after they're created. For dev systems I simply wouldn't run the script while on end-user systems the script would be run.

我想这样做的原因如下:我想开发没有加密的存储过程,所以我可以轻松点击SQL Server Management Studio中的“修改”来检查它们的内容。但是,对于部署,我想加密它们,所以我想也许我可以编写一个脚本,只有在它们被创建后加密它们。对于开发系统,我不会在最终用户系统上运行脚本,而是运行脚本。

8 个解决方案

#1

I have the same problem.

我也有同样的问题。

My solution is to put "-- WITH ENCRYPTION" in all of my stored procedures. This version is used by developers and stored in source control.

我的解决方案是在所有存储过程中加上“ - WITH ENCRYPTION”。此版本由开发人员使用并存储在源代码管理中。

I then use a tool (like sed) in my build to replace "-- WITH ENCRYPTION" with "WITH ENCRYPTION" on the files before I send them to be installed.

然后我在我的构建中使用一个工具(如sed)在我发送它们之前将文件中的“WITH WITH ENCRYPTION”替换为“WITH ENCRYPTION”。

For a pure SQL solution you could use REPLACE.

对于纯SQL解决方案,您可以使用REPLACE。

#2

You might want to check Encrypting all the Stored Procedures of a Database :

您可能需要检查加密数据库的所有存储过程:

If you ever decide that you need to protect your SQL Stored Procedures, and thought encrypting was a good idea, BE VERY CAREFUL!!! Encrypting Database stored procedures SHOULD NOT be done without having backup files or some sort of Source Control for the stored procedures. The reason I say this is because, once they are encrypted, there is no turning around. (Yes, there are third party tools that will decrypt your code, but Why go through that trouble.)

如果你决定需要保护你的SQL存储过程,并认为加密是一个好主意,请非常小心!加密数据库存储过程不应该在没有备份文件或存储过程的某种源代码控制的情况下完成。我说这是因为,一旦加密,就没有转机。 (是的,有第三方工具可以解密您的代码,但为什么要经历这个麻烦。)

This trick is something I developed because my company needed to host the application on a different server, and we were concerned about our code being compromised. So, to deliver the database, we decided to encrypt all out stored procedures. Having over a hundred procedures written, I didn't want to open each procedure and paste 'WITH ENCRYPTION' in each and every stored procedure. (For those of you who do not know how to encrypt, refer How Do I Protect My Stored Procedure Code[^]). So I decided to make my own little C# application that did the same.

这个技巧是我开发的,因为我的公司需要在不同的服务器上托管应用程序,我们担心我们的代码被泄露。因此,为了交付数据库,我们决定加密所有存储过程。编写了一百多个程序,我不想打开每个程序并在每个存储过程中粘贴“WITH ENCRYPTION”。 (对于那些不知道如何加密的人,请参阅如何保护我的存储过程代码[^])。所以我决定制作自己的小C#应用程序。

This application is a console application made using Visual Studio 2005 and SQL server 2005. The input parameters are database name, Server address, database username and password. Once you are able to provide these details, you are ready to have all your stored procedures encrypted.

此应用程序是使用Visual Studio 2005和SQL Server 2005创建的控制台应用程序。输入参数是数据库名称,服务器地址,数据库用户名和密码。一旦您能够提供这些详细信息,您就可以加密所有存储过程。

I have put the code of my application here as is. For this code to work, you will need to add an "Microsft.SQlserver.SMO" refrence to the application, so that the classes such as "Database" and "StoredProcedure" are accessible.

我把我的应用程序的代码放在这里。要使此代码起作用,您需要向应用程序添加“Microsft.SQlserver.SMO”参考,以便可以访问诸如“Database”和“StoredProcedure”之类的类。

BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!!

//Connect to the local, default instance of SQL Server. 
string DB = "";
ServerConnection objServerCOnnection = new ServerConnection();
objServerCOnnection.LoginSecure = false;
Console.WriteLine("Enter name or IP Address of the Database Server.");
objServerCOnnection.ServerInstance = Console.ReadLine();
Console.WriteLine("Enter name of the Database");
DB = Console.ReadLine();
Console.WriteLine("Enter user id");
objServerCOnnection.Login = Console.ReadLine();
Console.WriteLine("Enter Password");
objServerCOnnection.Password = Console.ReadLine();
Console.WriteLine(" ");
Server srv = new Server();
try // Check to see if server connection details are ok.
{
   srv = new Server(objServerCOnnection);
   if (srv == null)
   {
      Console.WriteLine("Server details entered are wrong,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Server details entered are wrong,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
Database db = new Database();
try // Check to see if database exists.
{
   db = srv.Databases[DB];
   if (db == null)
   {
      Console.WriteLine("Database does not exist on the current server,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Database does not exist on the current server,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
string allSP = "";

for (int i = 0; i < db.StoredProcedures.Count; i++)
{
   //Define a StoredProcedure object variable by supplying the parent database 
   //and name arguments in the constructor. 
   StoredProcedure sp;
   sp = new StoredProcedure();
   sp = db.StoredProcedures[i];
   if (!sp.IsSystemObject)// Exclude System stored procedures
   {
      if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
      {
         string text = "";// = sp.TextBody;
         sp.TextMode = false;
         sp.IsEncrypted = true;
         sp.TextMode = true;
         sp.Alter();

         Console.WriteLine(sp.Name); // display name of the encrypted SP.
         sp = null;
         text = null;
      }
   }
}

#3

WITH ENCRYPTION means that the code behind the proc is not stored in the SysComments table.

WITH ENCRYPTION意味着proc后面的代码不存储在SysComments表中。

You could write a script that does a exec sp_helptext 'MyProcName' and gets the contents into a VarChar (MAX) so it can hold multiline / large procedures easily and then modifiy the procedure from it's original state

您可以编写一个执行exec sp_helptext'MyProcName'的脚本并将内容放入VarChar(MAX)中,以便它可以轻松地保存多行/大型过程,然后从原始状态修改过程

CREATE MyProcName AS

SELECT SecretColumns From TopSecretTable

change CREATE to ALTER and AS surrounded by space or tab or newline (good place to use Regular Expressions) to WITH ENCRYPTION AS

将CREATE改为ALTER和AS,用空格或制表符或换行符(使用正则表达式的好地方)改为WITH ENCRYPTION AS

ALTER MyProcName WITH ENCRYPTION AS

SELECT SecretColumns From TopSecretTable

This will hide all code for the stored proc on the production server.

这将隐藏生产服务器上存储过程的所有代码。

You can put this in a LOOP or a CURSOR (not really a set based operation IMHO) for all objects of a specific type and/or naming convention that you want to encrypt, and run it every time you deploy.

对于要加密的特定类型和/或命名约定的所有对象,可以将其放在LOOP或CURSOR(实际上不是基于集合的操作IMHO)中,并在每次部署时运行它。

#4

I would recommend creating the sproc in a multi-line string variable and then inserting or altering it using sp_executesql. The only annoying downside to this approach is doubling of single quotes for strings.

我建议在多行字符串变量中创建sproc,然后使用sp_executesql插入或更改它。这种方法唯一令人讨厌的缺点是字符串的单引号加倍。

DECLARE @action varchar(max);
SET @action = 'CREATE'; /* or "ALTER" */

DECLARE @withEncryption varchar(max);
SET @withEncryption = ''; /* or "WITH ENCRYPTION" */

DECLARE @sql varchar(max);
SET @sql = @action + ' PROCEDURE dbo.Something'
    (
        ....
    ) ' + @withEncryption +
    ' AS
    BEGIN
        DECLARE @bob varchar(10);
        SET @bob = ''Bob'';
        ....
    END;
    ';

EXEC sp_executesql @statement = @sql;

[Note the whitespace around the variables.]

[注意变量周围的空格。]

All of my scripts use this method, which works well once you get used to the quote doubling thing.

我的所有脚本都使用这种方法,一旦你习惯了引用加倍的东西,它就能很好地运行。

I also use a batch file to call the script, and SQLCMD-mode command line variables to select various behaviours, which makes it repeatable and easy to test.

我还使用批处理文件来调用脚本,并使用SQLCMD-mode命令行变量来选择各种行为,这使得它可重复且易于测试。

#5

Use This Query which Encrypt All Procedures in database

使用此查询加密数据库中的所有过程

    CREATE TABLE #backup
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX) NOT NULL,
spname NVARCHAR(100) NOT NULL,
encrypttext NVARCHAR(MAX) NULL,
encryptstatus BIT NOT NULL
DEFAULT ( 0 )
)
DECLARE @sptexttable TABLE
(
id BIGINT IDENTITY(1, 1),
sptext NVARCHAR(MAX),
spname NVARCHAR(100)
)
INSERT INTO @sptexttable ( sptext, spname )
SELECT [text],
[name]
FROM syscomments
JOIN sysobjects ON syscomments.id = sysobjects.id
AND sysobjects.xtype = 'p'
DECLARE @sptext NVARCHAR(MAX)
DECLARE @spname NVARCHAR(100)
DECLARE @counter INT
SET @counter = 1
WHILE @counter <= ( SELECT MAX(id)
FROM @sptexttable
)
BEGIN
BEGIN TRY

INSERT INTO #backup ( sptext, spname )
SELECT sptext,
spname
FROM @sptexttable
WHERE id = @counter
END TRY
BEGIN CATCH
END CATCH

IF NOT EXISTS ( SELECT [name]
FROM sysobjects
WHERE [name] = 'ce_LastIndexOf'
AND xtype = 'FN' ) 
BEGIN


EXEC
( 'CREATE FUNCTION ce_LastIndexOf
(
@strValue VARCHAR(4000),
@strChar VARCHAR(50)
)
RETURNS INT
AS BEGIN
DECLARE @index INT


SET @index = 0


WHILE CHARINDEX(@strChar, @strValue) > 0
BEGIN
SET @index = @index
+ CASE WHEN CHARINDEX(@strChar, @strValue) > 1
THEN ( LEN(@strValue) - LEN(SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar),
LEN(@strValue))) )
ELSE 1
END
SET @strValue = SUBSTRING(@strValue,
CHARINDEX(@strChar, @strValue)
+ LEN(@strChar), LEN(@strValue))
END
RETURN @index
END'
)


END
DECLARE @tempproc NVARCHAR(MAX)
DECLARE @procindex INT
DECLARE @beginindex INT
DECLARE @header NVARCHAR(MAX)
DECLARE @asindex INT
DECLARE @replacetext NVARCHAR(MAX)

SET @tempproc = ( SELECT sptext
FROM @sptexttable
WHERE id = @counter
)

IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))
) > 0 
BEGIN
BEGIN TRY
SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))
PRINT @procindex
SELECT @beginindex = CHARINDEX('BEGIN', UPPER(@tempproc))
PRINT @beginindex
SELECT @header = SUBSTRING(@tempproc, @procindex,
@beginindex - @procindex)
SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')
- 2
)
SELECT @replacetext = STUFF(@header, @asindex, 10,
CHAR(13) + 'WITH ENCRYPTION'
+ CHAR(13) + 'AS' + CHAR(13))
SET @tempproc = REPLACE(@tempproc, @header, @replacetext)

END TRY
BEGIN CATCH
END CATCH
END

UPDATE @sptexttable
SET sptext = @tempproc
WHERE id = @counter


--PLAY HERE TO M AKE SURE ALL PROCS ARE ALTERED
UPDATE @sptexttable
SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC',
'ALTER PROC')
FROM @sptexttable
WHERE id = @counter
)
WHERE id = @counter
SELECT @sptext = sptext,
@spname = spname
FROM @sptexttable
WHERE id = @counter
BEGIN TRY
EXEC ( @sptext
)
UPDATE #backup
SET encrypttext = @sptext,
encryptstatus = 1
WHERE id = @counter
END TRY
BEGIN CATCH
PRINT 'the stored procedure ' + @spname
+ ' cannot be encrypted automatically'
END CATCH
SET @counter = @counter + 1
END
SELECT *
FROM #backup

#6

I wrote a cursor, steps through and encrypts most objects.

我写了一个游标,逐步完成并加密大多数对象。

                            DECLARE cur_ENCRYPT_ANTHING CURSOR READ_ONLY
                            FOR
                                    SELECT  STUFF(src.definition,
                                                  CASE WHEN CHARINDEX('AS' + CHAR(13),src.definition,1) = 0
                                                       THEN CASE WHEN CHARINDEX('AS ' + CHAR(13),src.definition,1) = 0 THEN CHARINDEX('AS ',src.definition,1)
                                                                 ELSE CHARINDEX('AS ' + CHAR(13),src.definition,1)
                                                            END
                                                       ELSE CHARINDEX('AS' + CHAR(13),src.definition,1)
                                                  END,3,'WITH ENCRYPTION AS' + CHAR(13))
                                    FROM    (SELECT o.name
                                             ,      STUFF(RIGHT(sm.definition,LEN(sm.definition) - CHARINDEX('CREATE ',sm.definition,1) + 1),1,6,'ALTER') AS definition
                                             FROM   sys.sql_modules AS sm
                                                    JOIN sys.objects AS o ON sm.object_id = o.object_id
                                             WHERE  CAST(CASE WHEN sm.definition IS NULL THEN 1
                                                              ELSE 0
                                                         END AS BIT) = 0
                                                    AND type <> 'TR'
                                            ) AS src








                            DECLARE @VLS NVARCHAR(MAX)
                            OPEN cur_ENCRYPT_ANTHING

                            FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
                            WHILE (@@fetch_status <> -1)
                                  BEGIN
                                        IF (@@fetch_status <> -2)
                                           BEGIN
                                                 BEGIN TRY
                                                       EXEC (@VLS)

                                                 END TRY
                                                 BEGIN CATCH
                                                       PRINT ERROR_MESSAGE()
                                                       PRINT ''

                                                       PRINT @VLS
                                                 END CATCH
                                           END
                                        FETCH NEXT FROM cur_ENCRYPT_ANTHING INTO @VLS
                                  END

                            CLOSE cur_ENCRYPT_ANTHING
                            DEALLOCATE cur_ENCRYPT_ANTHING

#7

1) I export Create code for SP and functions. Keep it backed up. for example D:\SP2.sql"

1)我导出为SP和函数创建代码。保持备份。例如D:\ SP2.sql“

2) this transact SQL code, generate the script to delete existing sP & Functions

2)这个transact SQL代码,生成删除现有sP&Functions的脚本

SELECT 'DROP PROCEDURE  [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'  as A
FROM sys.procedures p
union
SELECT  'DROP FUNCTION ' + [name]  
FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0
order by a

3) This Poweshell code replace

3)此Poweshell代码替换

AS
BEGIN

by

WITH ENCRYPTION 
AS
BEGIN

The code

$File = "D:\SP2.sql"
$File2 = $File.Replace("SP2.sql","SP-WithEncrypt.sql")
$sortie=""
$SP = get-content -path $file
echo $SP.Count
For ($i = 0 ; $i -le $SP.Count)
{ if ($sp[$i] -eq "AS" -and $sp[$i+1] -eq "BEGIN")
   { $AEcrire = "`nWITH ENCRYPTION `n AS `n BEGIN"
   $i+=1 
          }
   else
   {$AEcrire =$sp[$i]
   }
   $sortie += "`n$AEcrire"

 $i+=1 
 $SP.Count-$i
}

$sortie| out-file $File2

Would be faster with a .replace( ,), but problem with End of lines...

使用.replace(,)会更快,但行结束的问题......

4) run the SP-WithEncrypt.sql in SSMS

4)在SSMS中运行SP-WithEncrypt.sql

#8

I have made an update to one of the above answers by removing the dependency on the initial Begin Tag. I had a situation where not all my stored procedures had BEGIN and END.

我通过删除对初始Begin标记的依赖来更新上述答案之一。我遇到的情况是我的所有存储过程都没有BEGIN和END。

I used the AS clause instead and also used a case sensitive version of the charindex (by adding a collation)

我使用了AS子句,并使用了一个区分大小写的charindex(通过添加排序规则)

Its not a perfect solution but helped in getting more of my stored procedures encrypted.

它不是一个完美的解决方案,但帮助我加密了更多的存储过程。

Here is my updated code:

这是我更新的代码:

            IF OBJECT_ID('tempdb..#backup', 'U') IS NOT NULL 

             BEGIN

             DROP TABLE #backup

             END

            CREATE TABLE #backup

             (

             id BIGINT IDENTITY(1, 1),

             sptext NVARCHAR(MAX) NOT NULL,

             spname NVARCHAR(100) NOT NULL,

             encrypttext NVARCHAR(MAX) NULL,

             encryptstatus BIT NOT NULL

             DEFAULT ( 0 )

             )

            DECLARE @sptexttable TABLE

             (

             id BIGINT IDENTITY(1, 1),

             sptext NVARCHAR(MAX),

             spname NVARCHAR(100)

             )

            INSERT INTO @sptexttable ( sptext, spname )

             SELECT [text],

             [name]

             FROM syscomments

             JOIN sysobjects ON syscomments.id = sysobjects.id

             AND sysobjects.xtype = 'p'

            DECLARE @sptext NVARCHAR(MAX)

            DECLARE @spname NVARCHAR(100)

            DECLARE @counter INT

            SET @counter = 1

            WHILE @counter <= ( SELECT MAX(id)

             FROM @sptexttable

             )

             BEGIN





             BEGIN TRY





             INSERT INTO #backup ( sptext, spname )

             SELECT sptext,

             spname

             FROM @sptexttable

             WHERE id = @counter

             END TRY

             BEGIN CATCH

             END CATCH

              IF NOT EXISTS ( SELECT [name]

             FROM sysobjects

             WHERE [name] = 'CaseSensitiveIndex'

             AND xtype = 'FN' ) 

             BEGIN
                

             EXEC (
             'CREATE FUNCTION dbo.CaseSensitiveIndex(@source nvarchar(max), @pattern VARCHAR(50))
            RETURNS int
            BEGIN  
                return   CHARINDEX(@pattern COLLATE Latin1_General_CS_AS, @source COLLATE Latin1_General_CS_AS) 
            END; '
            )
            end


             IF NOT EXISTS ( SELECT [name]

             FROM sysobjects

             WHERE [name] = 'ce_LastIndexOf'

             AND xtype = 'FN' ) 

             BEGIN

                

             EXEC

             ( 'CREATE FUNCTION ce_LastIndexOf 

                (@strValue VARCHAR(max),

                @strChar VARCHAR(50)) 

            RETURNS INT

            AS

            BEGIN

            DECLARE @index INT

                

            SET @index = 0



            WHILE CHARINDEX(@strChar, @strValue) > 0

                BEGIN

                    SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1 

                                 THEN 

                                    (LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue)))) 

                                 ELSE 

                                    1 

                                 END

                    SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))    

                END



                RETURN @index 

            END'

             )



             END 

             DECLARE @tempproc NVARCHAR(MAX) 

             DECLARE @procindex INT

             DECLARE @beginindex INT

             DECLARE @header NVARCHAR(MAX)

             DECLARE @asindex INT

             DECLARE @replacetext NVARCHAR(MAX)



             SET @tempproc = ( SELECT sptext

             FROM @sptexttable

             WHERE id = @counter

             )



             IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))

             ) > 0 

             BEGIN

             BEGIN TRY

             SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))

             PRINT @procindex

             SELECT @beginindex=(select dbo.CaseSensitiveIndex(@tempproc, 'AS'))


             if(@beginindex=0) begin set @beginindex=( SELECT dbo.ce_lastindexof(@tempproc, 'AS'))end
             SELECT @header = SUBSTRING(@tempproc, @procindex,

             @beginindex )

             SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS')

             - 2

             )

             SELECT @replacetext = STUFF(@header, @asindex, 3,

             CHAR(13) + 'WITH ENCRYPTION'

             + CHAR(13) + 'AS' + CHAR(13))

             SET @tempproc = REPLACE(@tempproc, @header, @replacetext)



                                



             END TRY

             BEGIN CATCH

             END CATCH



                

             END



             UPDATE @sptexttable

             SET sptext = @tempproc

             WHERE id = @counter 



            --PLAY HERE TO MAKE SURE ALL PROCS ARE ALTERED

             UPDATE @sptexttable

             SET sptext = ( SELECT REPLACE(sptext, 'CREATE PROC',

             'ALTER PROC')

             FROM @sptexttable

             WHERE id = @counter

             )

             WHERE id = @counter 



             SELECT @sptext = sptext,

             @spname = spname

             FROM @sptexttable

             WHERE id = @counter


             BEGIN TRY


             EXEC ( @sptext)

             UPDATE #backup

             SET encrypttext = @sptext,

             encryptstatus = 1

             WHERE id = @counter

             END TRY

             BEGIN CATCH

             PRINT 'the stored procedure ' + @spname

             + ' cannot be encrypted automatically'

             END CATCH





             SET @counter = @counter + 1

             END

            SELECT *

            FROM #backup where encryptstatus =0