Azure SQL:审计触发器和所有权链

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

我创建了一个角色R1，它对S1有S、I、U和D权限(因此对T1也有)。角色还允许S在S2上运行(因此也允许在T2上运行)，但是拒绝I、U和d。我创建了一个用户U1，并将角色R1分配给该用户。

在U1的用户上下文中，如果我在T2上尝试I, U，或D，则会被拒绝，正如预期的那样。但是，如果I I、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;

好的，这是所有权链的作用，因为你在模式级别保护。

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

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

要清楚，执行上下文没有改变，没有EXECUTE AS或模拟正在进行。对表T2的操作仍然在原始调用者的上下文中操作，但是所有权链规则意味着访问规则简单，不重新求值，甚至不尝试检查。

所有权链是SQL Server的一个优化特性，在许多情况下，它通过允许访问规则被评估一次而不是重新评估每个可能的安全上下文来提高查询吞吐量，如果引擎只在上下文由不同的所有者保护时重新评估。

这就是为什么模式的Owner很重要，以及为什么您可以为不同的模式指定不同的任意所有者的主要原因。

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

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

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

相关内容

最新更新

热门标签：