0

I have a table that I am using Insert/Update/Delete triggers to audit. I want to restrict access to the audit table so that only the triggers can write to it. What I am trying to find out is, what user would those triggers count as for inserting records into the audit table.

EDIT: Since phil showed me the documentation in his comment for how it is determined, this changes my question a little.

What is a good method to restrict access to the audit table so that only the triggers can insert records?

Improve this question
2

1 Answer 1

Reset to default
0

There is no need to elevate permissions with EXECUTE AS with ownership chaining. To ensure rows are inserted into the audit table only via the trigger, simply don't grant INSERT permissions on the audit table. As long as all the objects are owned by the same user (e.g. dbo), the ownership chain will be unbroken and permit the trigger to insert into the audit tables without direct permissions.

Improve this answer
2
  • Currently the users who have access to the database this table resides in mostly have the db_datawriter role. Wouldn't that allow them access to the table by default?
    – KySoto
    Jun 28, 2018 at 15:03
  • Yes, db_datawriter implictly grants insert permissions on all user tables. Personally, I prefer avoiding the built-in roles and explictly grant permissions to roles on only those objects needed, following the security principles of role-based access conrol and least privilege. But you could still use db_datawriter and explictly DENY INSERT ON YourAuditTable TO public; to get the job done.
    – Dan Guzman
    Jun 28, 2018 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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