Permissions on Trigger Problem

Question

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.

The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the working data table, and has triggers for data changes (insert, update, delete) to update audit.Assesments with a log of changes. As far as I can tell, this has worked for years, so far so good.

Our L2 support has a Role called SupportRole that they get into with WinAuth, and that role has some select rights and execute rights for stored procedures. One of those stored procedures does an update to dbo.Assesments. This is where the problem comes in: The update goes in, the stored procedure fires to insert a audit row to audit.Assesments, and then they promptly get a "Insert denied, insufficient rights to Insert", the transaction roles back, etc.

This is mystifying to me: I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing, but this has me thinking twice. I don't want to give insert rights to the support role for the audit table/schema. What should I do?

Answer 1

Triggers run, by default, under the security context of the principal who caused the trigger to fire.

In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause.

Below is an example which shows how that works. WITH EXECUTE AS OWNER allows the trigger to run in the security context of the triggger owner, instead of the principal who is updating the table. From the docs:

OWNER
Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. OWNER cannot be specified for DDL or logon triggers.

First, we create a test table:

USE tempdb;
IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
    DROP TRIGGER t_trig;
    DROP TABLE dbo.t;
END
GO
CREATE TABLE dbo.t
(
    ID INT NOT NULL
    , ID2 INT NULL
);
GO

Here's the trigger code, with EXECUTE AS OWNER:

CREATE TRIGGER t_trig ON dbo.t
WITH EXECUTE AS OWNER
AFTER INSERT
AS
BEGIN
    UPDATE dbo.t
    SET ID2 = ID
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
END
GO

Now, we'll create a test login with low privileges that can be used to test the hypothesis that EXECUTE AS OWNER allows an under-privileged principal access to functionality they otherwise would not have:

CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;

We'll give them the ability to insert rows into dbo.T, but prevent them from running UPDATE statements:

GRANT INSERT ON dbo.t TO tLogin;
DENY UPDATE ON dbo.t TO tLogin;

Here we do the test:

EXECUTE AS USER = 'tLogin';
/*
    Output here shows we're running under the tLogin
    security context
*/
SELECT SUSER_SNAME();
/* 
    This will fail, with insufficient privileges
    since we've DENY'd the UPDATE privilege to tLogin.
*/
UPDATE dbo.t SET ID2 = ID; 
/*
    this will run the INSERT since the trigger 
    has EXECUTE AS OWNER
*/
INSERT INTO dbo.t(ID) VALUES (1); 
/*
    This takes us out of the tLogin security context
*/
REVERT

Here we can see the row in dbo.T has the changes made by the trigger:

SELECT *
FROM dbo.t;

And here, we cleanup the low-privilege user:

DROP USER tLogin;
DROP LOGIN tLogin;

The output from running the above is:

Answer 2

EXPLANATION

The "SupportRole" Role most likely gets the permission error when the trigger tries to INSERT into audit.Assesments due to:

1. Dynamic SQL is being used for the INSERT statement

   and/or

2. The audit schema has an owner that is not dbo

Both of those scenarios break the "ownership chaining" that you were expecting would allow this to work, which brings us to:

I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing

Triggers are essentially stored procedures that just happen to get automatically executed based on an event, and have access to some of the context of that event (i.e. the inserted and deleted virtual tables). However, it's not true, not even for stored procedures, that they have "the rights to do whatever they are doing". That is a misunderstanding of "ownership chaining".

Ownership chaining is a convenience that grants some permissions on objects that are referenced within an object. These implicit grants are limited as follows:

1. objects need to be owned by the same SID (not user name or principal_id) as the owner of the executing object, and
2. implicit grants are only: INSERT, UPDATE, DELETE, SELECT, and EXECUTE.

Meaning, even if a table referenced in a stored procedure has the same owner (i.e. same SID), there is still no implied permission allowing for either TRUNCATE TABLE or SET IDENTITY INSERT.

SOLUTION

Don't

First, I generally recommend against using WITH EXECUTE AS. The problem is that it completely swaps out the caller's permissions for the specified user's permissions, which might be way more than you are wanting. Also, those new permissions, unless explicitly reverted or changed to another user's, are in effect for all nested calls, no matter how broad or deep. This can also lead to unwanted extending of permissions beyond the original goal.

Second, unless there's a very specific reason for doing so (and there are very few), I also recommend against using OWNER as the EXECUTE AS user. The problem here is that object ownership is volatile. Ownership can change in several ways:

1. change the ownership of the object itself.
2. change the ownership of the schema containing the object (assuming the object does not have an owner explicitly defined)
3. move the object to another schema that has a different owner (assuming the object does not have an owner explicitly defined)

If it's absolutely necessary to use WITH EXECUTE AS, and you plan to specify OWNER because the object is in the dbo schema and you need dbo-level permissions, then it would be more stable to specify WITH EXECUTE AS N'dbo'.

To be clear: I'm not saying that using WITH EXECUTE AS is evil, should never be used, and you're horrible if you recommend it and/or use it. I just have a (very) strong preference for the solution mentioned below for the reasons stated here (above and below).

Do

I recommend Module Signing for a variety of reasons, including:

1. you simply add the permissions you want to the caller's permissions.
2. the permissions only cover the executing module, not nested module references.

The general structure of how to implement this for your situation is:

1. Create a certificate in the database containing these objects.
2. Create a user from that certificate.
3. Grant the certificate-based user whatever permissions are necessary.
4. If the object (trigger, stored procedure, etc) already has a WITH EXECUTE AS clause, ALTER the object to remove that clause (this must be done before signing as any ALTER object statement will automatically drop any signatures on the object being altered).
5. Sign the module(s) that need the permission(s) with the ADD SIGNATURE statement.

For more information, please visit my site: Module Signing Info