I have a SCADA application used for Energy Generation Management for a large Investment Bank. There is one database that controls, documents and resources an application that can contain thousands of independent objects used for bvarious tasks. The objects can have embedded code, scripts, etc. Each object is ID’d in a table using GUID. When an object is changed there is another table that documents the actions carried out on the object(update, checkin, checkout, revised, deleted, etc).
All of the actions take place in the dbo schema. I created audit table and appropriate insert, update and delete triggers.
But the audit team wants me to guarantee that the application user or any of the other roles within this schema cannot modfiy this audit table. I am not so good at security or roles so I am looking for an elegant solution to satisfy their requirements.
I was thinking about a linked server that contains the Audit table.
Has anyone else implemented anything similar?? Any ideas?
jharvey6