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?

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.

  • 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 '18 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 '18 at 17:34
  • Ah that makes sense. yeah i didn't set up the users or permissions initially, I am still learning on the database administration front. Thanks for your help. – KySoto Jun 28 '18 at 18:27
  • Glad it helped,@KySoto. If this answers your question adequately, see How does accepting an answer work. – Dan Guzman Jun 28 '18 at 20:39
  • i was waiting a little while to make sure no one else writes an answer. – KySoto Jun 29 '18 at 18:12

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.