asked
How to modify permissions for SET CONTEXT_INFO?
I have an AFTER UPDATE trigger that does two things: * inserts some audit records * modifies a timestamp on the updated table (thus triggering another update) In order to avoid a circular loop, I have the following in the trigger: CREATE TRIGGER Blah ON MainTable BEGIN declare @Context varbinary(128) SELECT @Context = CONTEXT_INFO() IF (@Context = 0x01) RETURN ... INSERT INTO AuditTable ... SET CONTEXT_INFO = 0x01 UPDATE MainTable SET WhenLastModified = GetDate() SET CONTEXT_INFO = 0x END However, this means that someone could potentially bypass auditing completely simply by setting the CONTEXT_INFO before there own modifications. Is there any way to restrict permissions on SET CONTEXT_INFO, so that it can only be used from within a trigger?
I guess [TRIGGER_NESTLEVEL()][1] could be more helpful then CONTEXT_INFO() in this case. You can pass the objectid of the trigger to check the nested level of this specific trigger. Another option could be to use INSTEAD OF trigger. Hope it helps. [1]:
http://msdn.microsoft.com/en-us/library/ms182737%28v=sql.100%29.aspx
10 People are following this question.
