Azure SQL: Audit Triggers and Ownership Chaining

I have two tables, T1 and T2, in two different schemas, S1 and S2, respectively. I have written a trigger, TR1 (with no EXECUTE AS clause), on T1 which logs inserts (I), updates (U), and deletes (D) into T2, which has an identical schema as T1, with some additional metadata columns. S1, T1, S2, T2, and TR1 all are owned by dbo.

I have created a role, R1, which has S, I, U, and D rights to S1 (and therefore, T1). The role also allows S on S2 (and therefore T2), but denies I, U, and D. I have created a user, U1, and assigned role R1 to this user.

Under the user context of U1, if I try to I, U, or D on T2, that is denied, as expected. However, if I I, U, or D into T1, the audit rows are successfully inserted into T2. This is the behavior that I wanted, but was wondering the reason for this, as U1 has been explicitly denied these privileges.

Is this because of ownership chaining, such that U1's privileges are never checked on T2 when TR1 runs, or something else?

Trigger code added:

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;

Solution

Ok, yes this is ownership chaining in action, because you are securing at the schema level.

Understanding SQL Server Ownership Chaining
When there’s an ownership chain, security is ignored on the object being referenced.

In this case there is an owership chain because both schemas have the same owner, and this means that the privileges are NOT re-evaluated for the trigger execution when it accesses the assumed secured schema.

To be clear, the execution context has not changed, there is no EXECUTE AS or impersonation going on. The operations against table T2 are still operating within the context of the original caller, but the ownership chaining rules means that access rules are simple not re-evaluated, it doesn't even try to check.

Ownership chaining is an optimisation feature of SQL Server, in many cases it improves query throughput by allowing access rules to be evaluated once rather than re-evaluating every possible securable context it the engine only re-evaluates when the contexts are secured by a different owner.

This is a primary reason why the Owner of a schema matters, and why you might specify different arbitrary owners created specifically for different schemas.

In the case of Audit/Change logging, we can take advantage of this behaviour to maintain the integrity of our data by blocking users who deliberately attempt to modify the audit records, while still allowing those users to execute queries and commands that might have side effects that will insert rows into the audit tables.

Because the user context has not been tampered with we can still capture and record information about the current user context and include that in the metadata that you may be recording about the operation.

For strictly non-audit based scenarios you need to be aware that ownership chaining can expose your secured tables to updates you might not have been expecting.