Networks

Question

Gravatar
0 Votes
Locked

Secure Audit Table in SQL SERVER

By jharvey6 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

You could just show them

by robo_dev In reply to Secure Audit Table in SQL ...

Login as a regular user and try to change the table, and see the error message.

Pushing a table to another server could create problems, like the fact that the audit process stops working when the other server goes offline for maintenance.

You could have the database logging level turned up all the way, and send the logs to a server that only your security team has admin access to. That's the only way to ensure that somebody with elevated access (like your DBAs) do not make changes and then can cover their tracks.

If the permissions are set properly in the table, simply show them that the permissions are set properly; they could validate that by logging on as a limited user and trying to change that table. If it's set properly, they will not be able to do that.

Back to Networks Forum

Start or search

Related Discussions

Related Forums