Security auditing/logging. It’s not the same as the others

Error logging means something went wrong with the runtime code. Crimes could happen and not a single error be raised. Error logs are first for developer diagnostics. Just “logging” and trace are usually for developer diagnostics. When runtime errors aren’t happening, then developers have to move on to trace. System.Diagnostics, enterprise library and log4net are examples of that. Aspect oriented techniques that log all method calls in an assembly would fall in that category.

Server event logging is aimed at the uses cases of server administrators–the Sql Server Logs, the IIS Logs server to help the administrator troubleshoot problems.

The customer of a security log is not the developer or the system administrator. The customer is the court of law, the control and managemetn structures of the company that uses the computer system. When a security log picks up wrong doing, no one asks the developer or system administrator to fix it, instead the wrong doers get fired, arrested, sued, jailed, etc. The developers and administrators will be asked to prevent these in the future, but security logs are not kept to keep developers and sysadmins busy. Security logs are kept to find and punish wrongdoing.

Often security logging turns into an attempt to make databases temporal– meaning the state of data is recorderd for the life of the database. So not only are transactions logged to enable ACID transactions, but the state of each cell in each coloumn in each table is recorded in its before and after state. If the DB is all write with a tiny amount of update, this is okay. If you update your entire database each year, then the logging feature will cause database size doubling– even if you aren’t adding any new rows, customers or sales. While it’s an interesting database problem, data modification logging helps the DBA solve DBA oriented problems, like how to un-corrupt data after the user sends the COMMIT TRANSACTION command.

As exciting as temporal databases may be– its about as relevant as a court lawyer showing and saying he will present his evidence in the form of a real time movie of the suspect, starting when the suspect was born and continuing with out interruption until today. The temporal-database as a security log dodges the question of how to effectively do security auditing and fails to answer it. A planet sized heap of garbage data is still garbage data.

Although there is little support for the idea at the moment, I think security auditing begins with risk and threat assessment. What crimes can be committed with your data? What evidence will we wish we’d gathered for it prosecution? That is what we should audit.

The best SQL Stored Procedure Header EVAR

/******************************************************************************
** File:		my_stored_procedure
** Author: 		John Doe Programmer
** Copyright:		ABC Corp 2009
** Creation Date:	1/1/2009
** Description: 	Return data
** Version:		2.9
** Build:		291
** Number of bugs:	13
** Specification #:	13.123.123.12.312.3.123.234.34.5.2345.324.5.345.23.45.2345.23.45.3.45.3245.345.3.45.2345.3245.34.5.3245.2345
** 	Please refer to the requirements tracibility matrix to see how this beautifully links to the following requirements:
**      13.123.123.12.312.3.123.234.34.5.2345.324.5.345.23.45.2345.23.45.3.45.3245.344
**      13.123.123.12.312.3.123.234.34.5.2345.324.5
**      13.123.123.12.312.3.123.234.34.5.2345.324.5.345.23.45.2345.23.45.3.45.3245.342
**      13.123.123.12.312.3.128.234.34.5.2345.324.5.345.23.45.2345.23.45.3.45.3245.342
** Bug tracker ref:	Http://bugzilla/proc=12
** Temperature:		70
** Cubicle Size:		12x8
** Number of characters of text:
** Percent ratio of whitespace to letters 'e':
** List of people who think this is buggy:
** My favorite colors:
** My favorite pony:
** My boss's name:
** My boss's wife's name:
** Birthday of Dr. Codd:
** Description of what we think Dr. Codd would have to say about this stored procedure:
** Lawyers to call if you need to ask questions about the copyright notice:
** Number of colums referenced:
** Acronym list: WTF, STFU, TANSTAFL
** Abreviation list:  
** Misspellings we can't fix because there are too many dependencies on them:
** 10 ways to improve your code through comment headers:
** Phone number to call in case of emergency:
** Phone number of 911 service:
** Numer of time executed:
** Comment template:
**
** /***********
** ** File:		my_stored_procedure
** ** Author: 		John Doe Programmer
** ** Copyright:		ABC Corp 2009
** ** Creation Date:	1/1/2009
** ** Description: 	Return data
** ** --TODO: Finish copying template into all headers
**
**
** Db Objects affected list:  (this will be out of date on first edit)
** Called by: (This will be out of date, um tomorrow)             
** Return values: (This will be out of date, um today)
** Parameters: (because we should repeat source code in comments, its's good for you)
** Input					
** ----------					
** @ID  This is the ID. ID is short for identifier. Can you say 'aye dee'? I knew you could.
** @NEW_ID  This is the NEW_ID. This means "New Id".  Don't be confused by the underscores!
** Output
** -----------
** 0 or 1 or both.
** TODO: (INSERT ASCII ART HERE TO SUPPLEMENT BEAUTIFUL BOXES)
*******************************************************************************
** Change History
*******************************************************************************
** Date:		Author:				Description:
** --------		--------			-------------------------------------------
** 1-1-2009		Larry Smoe			Added header
** 1-2-2009		John Doe			Removed header
** 1-3-2009		Larry Smoe			Restored header
** 1-4-2009		John Doe			Removed !$!#$ header
** 1-5-2009		Larry Doe			Copied header to new stored procedure, 
will update someday. with new text 
** 1-6-2009		Larry Doe			Fix row of astrixes, there were 4 extra!
** 1-7-2009		John Doe			Removed all ***** ascii art
** 1-8-2009 8AM		Larry Doe			Started adding unicorn ascii art
** 1-8-2009 9AM		Larry Doe			Coffee break
** 1-8-2009 1PM		Larry Doe			Lunch break
** 1-8-2009 3PM		Larry Doe			Finished adding unicorn ascii art, golf time!
** 1-9-2009		John Doe			Removed all ***** ascii art
** 1-8-2009 8AM		Larry Doe			Added "--Get Rows" comment to all select statements
** 1-9-2009		John Doe			Removed all !#$#$ "--Get Rows" comments
*******************************************************************************/

Not using SqlRedgate is industrial sabotage

SQL and related databases have some peculiar characterists that encourage it’s users to become opinionated.  Namely, it is very easy to screw stuff up.  The relational database is a unparallelled tool attached at the hip to a COBOL era programming langauge.  The recent rise of compensating tools is a revolution in IT and probably will add an extra %0.1 to the developed worlds GDP.  If only adoption rate was higher.

SqlCompare.  This is one of many schema diff tools on the market.  It does source control (or scripts folder) to database schema comparison.  And it works.  Microsoft’s product that enables this work flow costs twice as much (ie. data dude)

SqlData Generator. Fire those monkeys you call testers.  A typical testor of a database application hits random keys until the validator allows saving a row. If the row gets rejected, but otherwise the data looks okay, a bug is filed.  Sql Data Generator can do that 100,000 times a second.  If you have foreign keys, SqlRedGate can generate sample data better than most domain experts.

SqlPrompt.  Writing code without intellisense is slow.  Working without intellisense, expect your developers to need and extra six monts to a year to commit the schema to memory.  Trust me, I’ve never really been good with a schema until I had most of it committed to memory.  With typcial schemas of 500+ tables, not giving your developers intellisense is adding an extra half man year for each developer on your team.  

Code formatting affects maintenance times and right now to debug a TSQL stored procedure it takes hours if the proc is long enough.  Reformatting pays for itself in the first debug session.

Webservices and WCF- eatting one’s own dogfood

The official line for WCF and webservices is that they are for interop especially between organizations and between different technology stacks, e.g. Java and .NET or COBOL and .NET.

Who wants to build an open API when no one has asked for it? Who will ask for an open API if one doesn’t already exist?  For WCF/webservices to happen at all you have to image a use case for these that would be useful now.

Three reason why you’d wan’t to consume your own webservices:

Javascript to .NET interop.  This allows for Aptana driven development against a C# application.  This becomes especially compelling if you have webservices returning JSON,  and RSS, because the client will be simpler to write.

Testability.  The webservices API is more testable than the webforms that do much the same thing.

Data access.  XML is a datatype too.  It can be handy to have one more dataformat in the data monkey’s toolbox.

Compatibility with future versions. WCF especially- The web service as a programming model might be low performance, but it is remarkably resiliant to changes in implementation.  So much thought has gone into defining an interface that works with everyone, it will even work with that foreign application called “Your Application, version next”

When to and when not to use #Region in C# and VB.NET

Regions are not classes. Regions are not shelves in your closet. Regions are not folders on your obsessively compulsively clean desk. Regions are not to be alphabetized like the cans of food in your cupboards. Regions are not the rug for sweeping your buggy code under.

Remember, regions by default are collapsed and invisible without special action.

Regions are a code smell. Regions are obfuscation. Regions are evil. Except in the following 3 cases.

DO. License headers. You got to have them on every file for certain licenses. If these collapse by default, that is okay.

DO. Stupid ass headers your boss makes you put in your code that add no value. #Region will make them invisible most of the time.

DO. Commented out dead code you can’t delete because you don’t expect your colleagues to find it in the source control.

And to clarify:

DON’T. Groups of related methods. This is a code smell. If you have more than 100 lines of code and have the urge to use a region, you probably have discovered the need for a new class.

DON’T. Arbitrary categories of members, e.g. Properties, private methods, methods that return strings, methods starting with the letter “r”

DON’T. Code Generated regions. Use partial classes.