triggers - Azure SQL：审核触发器和所有权链接

问题描述

我有两个表 T1 和 T2，分别位于两个不同的模式 S1 和 S2 中。我在 T1 上编写了一个触发器 TR1（没有 EXECUTE AS 子句），它将插入（I）、更新（U）和删除（D）记录到 T2 中，T2 具有与 T1 相同的架构，并带有一些额外的元数据列. S1、T1、S2、T2 和 TR1 都归 dbo 所有。

我创建了一个角色 R1，它对 S1（以及 T1）拥有 S、I、U 和 D 权限。该角色还允许 S2（以及因此 T2）上的 S，但拒绝 I、U 和 D。我创建了一个用户 U1，并为该用户分配了角色 R1。

在 U1 的用户上下文中，如果我在 T2 上尝试 I、U 或 D，则如预期的那样被拒绝。但是，如果将 II、U 或 D 插入 T1，则审计行将成功插入 T2。这是我想要的行为，但想知道其原因，因为 U1 已被明确拒绝这些特权。

这是因为所有权链接，因此当 TR1 运行时，U1 的权限永远不会在 T2 上检查，还是其他原因？

添加了触发代码：

CREATE TRIGGER TRG ON dbo.T1
FOR INSERT, UPDATE, DELETE
AS
BEGIN;

DECLARE @Operation CHAR(1);

SET @Operation =    (
                        CASE
                            WHEN EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED) THEN 'U'
                            WHEN EXISTS(SELECT 1 FROM INSERTED) THEN 'I'
                            WHEN EXISTS(SELECT 1 FROM DELETED) THEN 'D'
                            ELSE NULL
                        END
                    );

IF @Operation = 'I'
    BEGIN;
        INSERT INTO     adt.T1(Operation, ID, C1)
        SELECT          @Operation, ID, C1
        FROM            INSERTED;
    END;

IF @Operation = 'D'
    BEGIN;
        INSERT INTO     adt.T1 (Operation, ID, C1)
        SELECT          @Operation, ID, C1
        FROM            DELETED;
    END;

IF @Operation = 'U'
    BEGIN;
        INSERT INTO     adt.T1 (Operation, ID, C1)
        SELECT          @Operation, i.ID, i.C1
        FROM            INSERTED i
        INNER JOIN      DELETED d
        ON              i.ID = d.ID
                        -- Hash indicated columns of INSERTED and DELETED to determine if there are any real changes.
        WHERE           (SELECT HASHBYTES('MD5', (SELECT i.ID, i.C1 FROM (SELECT NULL AS X) t FOR XML AUTO)))
                        <>
                        (SELECT HASHBYTES('MD5', (SELECT d.ID, d.C1 FROM (SELECT NULL AS X) t FOR XML AUTO)));
    END;

END;

标签： triggersazure-sql-databaseaudit

好的，是的，这是实际的所有权链接，因为您在模式级别进行保护。

了解 SQL Server
所有权链当存在所有权链时，被引用对象的安全性会被忽略。

在这种情况下，存在一个所有权链，因为两个模式具有相同的所有者，这意味着当触发器执行访问假定的安全模式时，不会重新评估权限。

需要明确的是，执行上下文没有改变，没有EXECUTE AS或模拟正在进行。针对表 T2 的操作仍在原始调用者的上下文中运行，但所有权链接规则意味着访问规则很简单，无需重新评估，甚至不会尝试检查。

所有权链接是 SQL Server 的一项优化功能，在许多情况下，它通过允许对访问规则进行一次评估而不是重新评估每个可能的安全上下文来提高查询吞吐量，引擎仅在上下文由不同所有者保护时重新评估.

这是架构的所有者很重要的主要原因，也是为什么您可以指定专门为不同架构创建的不同任意所有者的原因。

在审计/更改日志记录的情况下，我们可以利用这种行为来维护我们数据的完整性，方法是阻止故意尝试修改审计记录的用户，同时仍然允许这些用户执行可能有副作用的查询和命令这会将行插入到审计表中。

因为用户上下文没有被篡改，我们仍然可以捕获和记录有关当前用户上下文的信息，并将其包含在您可能正在记录的有关操作的元数据中。

对于严格不基于审计的场景，您需要注意所有权链接可能会将受保护的表暴露给您可能没有预料到的更新。

triggers - Azure SQL：审核触发器和所有权链接

问题描述

解决方案

推荐阅读