Technology, training, learning and development blogs

Keys to the door

SQL Security features, pros and cons

Johnny Security is a difficult character to put your finger on. When I first entered IT he was shy and retiring.  Computers were expensive and rare.  Very few people ever got to see one, let alone knew how to use one, so security consisted of a coded lock on the mainframe room.  Gradually, as computers grew ever more ubiquitous, so his profile rose.

 

When I first started training others in SQL databases I would illustrate the importance of security by saying, ‘a few months ago there was a story about a laptop left in a pub’, or ‘last year a web site allowed people to see these credit cards.’  Nowadays ‘a few months ago’ is more likely to be last week or even yesterday.  EPOS systems, on-line banking, E-commerce sites, and the public sector’s drive to treat patients and taxpayers as ‘customers’ all mean that there are a thousand pieces of private information about me on a million different systems.  The opportunity for serious security breaches have proliferated to a scale undreamt of even by Aldous Huxley.

 

As security requirements have changed, so too have people’s attitude.  Microsoft used to say, ‘here are all our wonderful features.  Switch them off if you don’t want them.’  In SQL 2005 and 2008 they are off by default; you have to enable many of them before you can use them.  More security features are introduced with each new release.  SQL 2005 has password policies applied to SQL logins; new permission sets; encryption, code signing and validation by symmetric and asymmetric keys; horizontal filtering of management views; DDL triggers that allow auditing of schema changes, etc. 

 

SQL 2008 adds to these.  The PKI infrastructure is extensible, so you can integrate third-party tools like hardware encryption dongles and separate key management from data management.  Transparent Data Encryption (TDE) allows you to encrypt an entire database on disk without any code changes to your applications, and because it is applied at the page level, it means your backups are encrypted too.  Policy-based management means you can apply a security policy across groups of servers that can report on, or even prevent, poor security practices by your administrators and developers.  All actions can be audited in a very efficient way, including SELECT statements.

 

But the problems with security evolve too.  Fail to backup your encryption keys separately, and the backup of your encrypted database is worthless.  Make your policies too draconian and people will not be able to do their jobs properly.

 

The biggest problem with database security has not changed since data was first chipped into stone tablets.  You can never make an uncrackable safe, you just make it as hard as possible.  All your hard work is wasted, though, if some fool leaves it unlocked.

 

Some Microsoft marketing slides sell TDE as a solution to the laptop left in the pub problem, but as it’s an Enterprise-only feature, how many people are going to have SQL Server 2008 Enterprise edition installed on their notebook?  Backups of TDE-enabled databases need the key to restore them, so how many people are going to send the key in the same envelope in which the backup is being couriered? 

 

As security becomes more rigorous and complicated, so does the knowledge and skills to properly administer and apply it.  Thus the opportunities to foul it up, or the temptation to skip it all together, become more numerous.  The post-it notes on monitors, detailing passwords, bear witness to many people’s lack of basic security knowledge.

 

So, what inspired this rant?

 

Last year I wrote off my motorcycle, and by the time I left hospital it was mutually agreed I was too old to bounce down the A2 anymore.  Not agreed by me, you understand, but mutually by those in higher moral authority.  If you’re married, you’ll understand.  So now I join the many hundreds of commuters on the rail network.  One morning I was lucky enough to get to sit down in an actual seat in the carriage (I now carry a camping stool for the majority of occasions I can’t get a seat, but that’s another rant).  As we neared London two passengers who obviously worked for an IT-related company were discussing security at various sites.  One of their customers in particular was very hot on security.  I won’t say who, but it is a government department at the pointy end of anti-terrorist activity.  On the crowded train they started talking about what they needed to encrypt when bringing data in and out of the site, what encryption algorithms they used, and details about what storage media they were allowed and not allowed to carry through the checkpoints.  All this on a crowded public train.

 

Tell me.

 

What’s the point in having the most powerful locks known to man on your house, then leaving a note on the front door stating that the key is under the doormat?