Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The Future of Auditing Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 8:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 33,266, Visits: 15,432
Comments posted to this topic are about the item The Future of Auditing






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519852
Posted Thursday, December 5, 2013 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 5,399, Visits: 3,124
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!!!
Post #1519909
Posted Thursday, December 5, 2013 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 53, Visits: 419
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.
Post #1519984
Posted Thursday, December 5, 2013 6:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 415, Visits: 2,439
"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.

Post #1520029
Posted Thursday, December 5, 2013 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:11 AM
Points: 2, Visits: 5
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?
Post #1520071
Posted Thursday, December 5, 2013 7:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:17 AM
Points: 73, Visits: 195
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!
Post #1520089
Posted Thursday, December 5, 2013 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:06 AM
Points: 125, Visits: 132
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.
Post #1520110
Posted Thursday, December 5, 2013 8:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 869, Visits: 2,399
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.
Post #1520111
Posted Thursday, December 5, 2013 8:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:09 AM
Points: 869, Visits: 2,399
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.
Post #1520119
Posted Thursday, December 5, 2013 9:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 415, Visits: 2,439
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

Post #1520169
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse