Trigger or Not Trigger

Question

Trigger or Not Trigger

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101101 More actions · Answer 1

LutzM (9/3/2014)
ScottPletcher (9/3/2014)
...
There are 3rd party solutions that do that, but they are rather expensive. At least one we investigated uses its own "black box" to hold the data -- DBAs can't even see it.
From my understanding there's still a chance for an OS system admin to stop the process of the auditing tool (if it's on the same server) or drop the connection (if it's on a separate system), create a database user, disable triggers, make some changes, clean up logs and traces, re-start the tool(s) and leave the system.
The effort it takes to make a undetected change will take quite some time, but is still possible, I think. From my point of view there's no audit technology out there that really is "unbreakable"...

These tools are entirely different servers -- DBAs wouldn't even necessarily know they existed. It's totally separate appliances/boxes. Yes, the tool admins themselves *might* be able to get around it, but no one else could. We didn't get it solely because of the cost :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

TomThomson SSC Guru Points: 104773 More actions · Answer 2

ScottPletcher (9/3/2014)
Triggers can capture the originating user as well if you provide that info to the trigger. This can be passed in several ways, including CONTEXT_INFO, APP_NAME or even a table row. Given pooled connections, it can take some care to make sure the data is set correctly per user.

Take some care; certainly. The design and testing overhead implied by that "some care" is pretty enormous given that app name is generally not an option (each app serves several users from one server process) and many apps share connections rather than dropping and making. SO it will almost always (given full auditing) be better (less likely to be buggy, less cost to design, lest cost to test, less risk of late delivery) to use a stored procedure which the app can call and hand a used identity parameter. ANd of course if auditing read access is required trigger does not work, no matter what you do.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 3

ScottPletcher (9/3/2014)
LutzM (9/3/2014)
ScottPletcher (9/3/2014)
...
There are 3rd party solutions that do that, but they are rather expensive. At least one we investigated uses its own "black box" to hold the data -- DBAs can't even see it.
From my understanding there's still a chance for an OS system admin to stop the process of the auditing tool (if it's on the same server) or drop the connection (if it's on a separate system), create a database user, disable triggers, make some changes, clean up logs and traces, re-start the tool(s) and leave the system.
The effort it takes to make a undetected change will take quite some time, but is still possible, I think. From my point of view there's no audit technology out there that really is "unbreakable"...
These tools are entirely different servers -- DBAs wouldn't even necessarily know they existed. It's totally separate appliances/boxes. Yes, the tool admins themselves *might* be able to get around it, but no one else could. We didn't get it solely because of the cost :-).

It strikes me as impossible to get that level of auditing certainty unless you have control of (a) the operating system, so that you can add functions to it and (b) the DBMS, so that you can make it call those functions at appropriate points. If you talk to a different server, I guarantee a good engineer can detect that, and he can probably disable it too unless you have additional comms tricks buried very deep in the OS. So I reckon MS could maybe build such an auditing system, but tracing who does an update might still be impossible to guarantee since that would require the same level of control over the app code as over the OS. You may be able to find a system that is so hard to beat that probably no-one will be prepared to spend the effort to do it, but that "probably" means there's still some risk: it may be so small a risk that it doesn't matter, and that of course is good enough, but whether the risk is that small depends on how small teh risk has to be to not matter as well as on how unbeatable the system is.

Tom

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

CELKO (9/1/2014)
Could you give some examples of that?
HIPPA. You must record everyone who saw the medical record, not changed it, saw it. I could not carry my own sonogram to the doctor in the next room; it has to be a NURSE! It is a medical record and I am not authorized.
You can only catch INSERT, UPDATE and DELETE (aka database events in SQL-speak), not SELECT. This is as bad as the days I did Cold War era contracts.
I am told that BASEL II, and some other financial things are almost as silly.
What you want to do is put wrap the database in a software wall (aka Iron Curtain) that logs, blocks and filters everything that cross the boundary. Ever work with Multics OS or learn about its ring model in school?

Damn... I forgot about those types of programs. Thanks for the reminder, Joe. Now if I can only find that post I was looking at a while ago that asked a similar question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)