September 3, 2014 at 2:39 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 3, 2014 at 5:47 pm
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
September 3, 2014 at 6:08 pm
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
September 3, 2014 at 11:33 pm
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply