December 4, 2013 at 8:32 pm
Comments posted to this topic are about the item The Future of Auditing
December 5, 2013 at 1:58 am
All the auditing solutions that I have seen in play stink of being a technical resolution. Until it becomes visible and a concern at a business level they will remain so. Other examples I have seen demonstrate this too e.g. the use of Windows Event Log for this purpose.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
December 5, 2013 at 5:48 am
We use three different monitoring solutions outside of SQL Server: one that monitors activity against the database instance, one that gathers event logs, and one that monitors changes to critical system files and directory locations.
The central problem with crafting a SQL Server-based monitoring is just that: it's SQLServer-based and exposed to whichever admin account has sway over it. I can't touch any of the three solutions we use, and Audit is more than happy with that.
December 5, 2013 at 6:45 am
"Careful consideration must be given regarding where to send the audit data. Ideally, the audit information should be sent to a location that cannot be modified or tampered with, even by a sysadmin."
http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
Obviously the audit servers need administrated, but at least the concern is recognised and options are there for those who need this level of auditing.
December 5, 2013 at 7:31 am
Worst idea ever inside a DB - integrated secure logs.
Any bug will make a restart impossible.
Outside monitoring and logs is a great way to do this.
Americans tend to microcontrol and log too much by default already.
How much of data gathered is really used productively?
December 5, 2013 at 7:48 am
I've enjoyed the Nathan Lowell stuff too - d/l them as podio books years ago. Not sure if it went as far as Captain back then though - I'll have to check in on that series again. Thanks for the reminder!
December 5, 2013 at 8:07 am
When I was a student in College, one of my tasks was to manage a shared Linux system that was used by students in a "how to get started with Linux" course. It was a learning opportunity, in which I found out the importance of maintaining log integrity.
I happened to notice a user doing some "poking around" that I considered unusual. After a little digging, I found out he was attempting to use our system as a platform for accessing other systems on campus in an unauthorized manner.
Within a few days of this discovery, I accomplished the following:
* Set up remote logging solution, so anything headed to the log files was intercepted and sent elsewhere
* On the remote system, created a web interface for the faculty and admins with basic search functions for the logs (by user, by activity, etc)
* Recompiled all the shell binaries on the system (Ksh, Csh, Bash, etc) with code to log all commands executed
* Set up alert system so if a flagged user logged in, I was paged
All students were reminded about not using the system for unauthorized activities, and it listed all those things, etc etc. At least one continued to attempt malicious activities, up to elevating permissions and removed entries from the local log files. Based on the evidence in the remote log files, he was kicked out.
This is why it is not just important to have a separate logging server, but it is also important to provide easy access to that data to the people that make the decisions.
December 5, 2013 at 8:08 am
patrickmcginnis59 10839 (12/5/2013)
"... Ideally, the audit information should be sent to a location that cannot be modified or tampered with, even by a sysadmin."http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
Windows in general, and SQL Server in specific, are spectacularly unsuitable for any kind of permanent, tamper-resistant logging. Tamper-resistant audit trails can include a lot of protections, but the #1 protection must be that once written, the original audit data can be recovered despite anything that happens at a logical level.
Note also that for those systems where logging is required, the system must fail if the logging fails - logging becomes, in database parlance, part of the ACID compliant transaction. If your system is allowed to continue operating when logging is failing, logging is not required - it may be desired, or requested, or wanted, or a best practice, but it is not required, since you may operate without it.
Devices that can store permanent, tamper-resistant logging include, but are not limited to:
Line printers with fan-fold paper
WORM platter jukeboxes*
CD/DVD/Blu-Ray jukeboxes with read-only media**
WORM tape jukeboxes*!
EMC Centerra*!
I would assume that United States SEC (Securities and Exchange Commission) guidelines have more detail on tamper-resistant auditing (i.e. auditing that's difficult to change after a bad actor commits financial malfeasance).
*In at least some modes, the OS can perform open a new file, write to it, close it, open it again and perform random write write operations even over previously recorded data - the platter's "journal" must be read to see whether this has happened, and if so, retrieve "prior versions", which both tend to be incredibly slow and cumbersome processes.
**I have to assume the above can also happen (see: Puppy Linux) but without the benefits of a good journal.
*! I suspect the above can happen, but I've never worked with these.
December 5, 2013 at 8:24 am
volker.osterlitz (12/5/2013)
Americans tend to microcontrol and log too much by default already.How much of data gathered is really used productively?
American logging is often not meant to be used productively, it's meant to be used in court.
December 5, 2013 at 9:11 am
Nadrek (12/5/2013)
patrickmcginnis59 10839 (12/5/2013)
"... Ideally, the audit information should be sent to a location that cannot be modified or tampered with, even by a sysadmin."http://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
Windows in general, and SQL Server in specific, are spectacularly unsuitable for any kind of permanent, tamper-resistant logging. Tamper-resistant audit trails can include a lot of protections, but the #1 protection must be that once written, the original audit data can be recovered despite anything that happens at a logical level.
So now I'm wondering which attributes of Microsoft software make it spactacularily unsuitable for this? Its not like I've never put a greenbar printer on a Windows server 🙂
December 5, 2013 at 9:53 am
A cautionary real-world tale:
Our company uses a mission critical system from a vendor. I'm not sure of the exact architecture, but in general there is a production database that is copied to and overwrites a "mirror" testing database every night. Effectively the mirror is a day-old copy of production intended for testing and training purposes. The two databases are accessed from similar but very distinct Java clients.
Anyway, we were informed the other day that somehow their server that handles the "mirror" transactions inadvertantly got pointed at the production data(!). Fortunately it was early in the morning and only a few people were using it, however one of our staff was doing a competency evaluation on the mirror involving data for a 29 CFR 11-compliant process.
To their credit, the vendor did inform us, but then proceeded to resolve the issue by rolling back the erroneous transactions that were made as if they never happened. BIG NO-NO. 29 CFR 11 regulations require that data entry errors be handled in a manner that shows the erroneous data AND the corrections. You don't simply "white-out" or overwrite/roll back errors.
For reasons I can't divulge, there was a remote but very real possibility people's health or even their lives could have been put at risk by this situation.
Not all applications and databases necessarily rise to this level of impact, but it is certainly the case that the time gap between when data is collected and when it is used has fallen to near zero, so the accuracy and integrity of data - which includes auditability - has become much more important because the real-world consequences can in many cases be substantial.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
December 5, 2013 at 5:31 pm
The only secure provable way we have been able to provide auditable information is to include the audit function as part of the development cycle. This has been driven by federal and state legal requirements of types of data and processes we must perform. We log, keep audit trails, and have write/update triggers that cut before and after images of parts of many transactions. This is the only provable way we are able to prove the transactions.
Windows logs and server logs help but in the end if the law requires you to keep the minutia then the minutia is what you collect.
M...
Not all gray hairs are Dinosaurs!
December 22, 2016 at 6:07 am
Haven't been able to make it work with any real value for us.
December 22, 2016 at 6:55 am
While I agree that sys admin actions on production machines should be tracked, as a developer I wish there was an easier solution to logging user behavior. It has been a few years, but shortly after Change Data Capture became available I was building an application where we wanted to track who changed what. The DBA thought this would be a great place to try out CDC. It all seemed great -- until I needed modify the schema. Then all that goodness silently broke leaving no tracking. This to me is the kind of story that just shouldn't happen.
December 22, 2016 at 7:01 am
Still amazes me that developers want to do all the auditing via the application.
Tend to get a cross-eyed response when I ask them how they want to work auditing out in a CQS applications. 😛
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply