使用SqlDependency创建的存储过程进行加密

Question

使用SqlDependency创建的存储过程进行加密

7

我创建了一个SqlDependency，当特定查询的结果发生变化时，会触发一个事件。

// Create a command
SqlConnection conn = new SqlConnection(connectionString);
string query = "SELECT MyColumn FROM MyTable;";
SqlCommand cmd = new SqlCommand(query, conn)
cmd.CommandType = CommandType.Text;

// Register a dependency
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += DependencyOnChange;

当此代码执行时，将自动创建一个名称类似于SqlQueryNotificationStoredProcedure-82ae1b92-21c5-46ae-a2a1-511c4f849f76的存储过程。该过程未加密，违反了我收到的要求。我有两个选择： 1.说服客户说，自动生成的过程未加密并不重要，因为它只做清理工作并不包含任何实际信息（感谢ScottChamberlain指出）。 2.找到一种方法来加密由SqlDependency生成的存储过程。如何完成选项2？

有问题的存储过程内容：

CREATE PROCEDURE [dbo].[SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b]
AS
BEGIN
    BEGIN TRANSACTION;

    RECEIVE TOP (0) conversation_handle
    FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

    IF (
        SELECT COUNT(*)
        FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b]
        WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
    ) > 0
    BEGIN
        IF (
            ( 
                SELECT COUNT(*)
                FROM sys.services
                WHERE NAME = 'SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b'
            ) > 0
        )
        DROP SERVICE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

        IF (OBJECT_ID('SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b', 'SQ') IS NOT NULL)
            DROP QUEUE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

        DROP PROCEDURE [SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b];
    END

    COMMIT TRANSACTION;
END
GO

- Rainbolt

这个过程需要加密的是什么操作？我没有我的开发环境在面前，但我认为该过程只是在断开连接时进行清理。它不包含任何需要加密的信息（但正如我所说，我没有测试环境在面前，所以我可能是错的）。 - Scott Chamberlain

@ScottChamberlain 我已经将存储过程的内容添加到问题中，以便您可以看到它。我知道存储过程不需要加密，但我有一个要求规定必须加密。我可以反驳，并且客户可能会为我做出例外，但这不是问题的重点。 - Rainbolt

你试过使用WITH ENCRYPTION吗？ - idstam

2

创建过程时未加密，然后立即加密以便只有具有分析器访问权限的人才能查看其定义，这样做是否可接受？如果可以，您可以使用DDL触发器来实现。 - Jeroen Mostert

@JeroenMostert 实际上我认为你是指一个_DDL_触发器。 - Rhumborl

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jeroen Mostert · Accepted Answer

创建一个DDL触发器，检查是否正在创建名称类似于“SqlQueryNotificationStoredProcedure-”的存储过程，如果是，则立即将其更改为WITH ENCRYPTION：

CREATE TRIGGER [TR_EncryptQueryNotificationProcedures] 
ON DATABASE
AFTER CREATE_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    SET ARITHABORT ON;
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    -- For debugging purposes only
    PRINT CONVERT(NVARCHAR(MAX), EVENTDATA());

    DECLARE @DatabaseName NVARCHAR(128);
    SET @DatabaseName = EVENTDATA().value(
        '(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Schema NVARCHAR(128);
    SET @Schema = EVENTDATA().value(
        '(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Name NVARCHAR(128);
    SET @Name = EVENTDATA().value(
        '(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'
    );

    DECLARE @Definition NVARCHAR(MAX);
    SELECT @Definition = 
        OBJECT_DEFINITION(
            OBJECT_ID(
                QUOTENAME(@DatabaseName) + '.' + 
                QUOTENAME(@Schema) + '.' + 
                QUOTENAME(@Name),
                'P'
            )
        )
    ;

    -- If the sproc is already encrypted, we can't do anything with it
    IF @Definition IS NULL RETURN;  

    SELECT @Definition = STUFF(
        @Definition, 
        CHARINDEX('CREATE', @Definition), 
        LEN('CREATE'), 
        'ALTER'
    );

    IF 
        @Name LIKE 'SqlQueryNotificationStoredProcedure-%' AND
        -- this should always be false since we can't read encrypted definitions, 
        -- but just to make sure 
        @Definition NOT LIKE '%WITH ENCRYPTION AS BEGIN%' 
    BEGIN;
        SET @Definition = REPLACE(
            @Definition, 'AS' + CHAR(13) + CHAR(10) + 'BEGIN', 
            'WITH ENCRYPTION AS BEGIN'
        );
        EXEC (@Definition);
    END;
END;
GO
ENABLE TRIGGER [TR_EncryptQueryNotificationProcedures] ON DATABASE;

免责声明：未经实际依赖通知测试，但基本想法是正确的。当然，它非常脆弱，因为它取决于过程的确切形式 -- 提高其健壮性是可能的，但很繁琐。