4

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?

7

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 database owner, instead of the principal who is updating the table.

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 UPDATE 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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.