Database Design 101

Over the years I have seen some very well designed databases, unfortunately I have also encountered some very badly designed databases (and I use the word designed loosely).

Does This Sound Familiar

Picture this. You have just taken ownership of an application and you decide to familiarize yourself with the database. You login to the database and start poking around only to find something like this.

• Primary Keys are not explicitly defined, although there is a column that looks like the primary key
• Foreign Keys are not defined as no primary keys are defined.
• The Primary Key is an integer that is created by the application instead of declaring it as an 'identity' or 'auto_increment'
• No other types of contraint exist
• On databases that include diagramming functions no attempt has been made to use them.
• Database backups are not setup or are setup to the same drive as the live database files.
• Normalization is either non existent or gone way too far.
• Stored procs are not used
• etc etc etc

I personally have encountered every single one of these scenarios in various combinations on both SQL Server and MySQL databases. I have also encountered the most redundant queries ever. e.g. A query that gets every record in the DB just to calculate a record count.

Result: A Slow Performing Database

The results of these types of designs flaws were applications that ran slowly, were unreliable and error prone. These were databases designed (and again I use that term loosely) by consultants(non IT) using access and excel and should never have seen the light of day. In fact if you are trying to develop a database and you think you have any of these problems stop, call me instead. Trust me the cost of hiring a good designer now will be far less than the cost of trying to fix problems later.

Even though performance problems can become very serious very quickly, they are not the worst issues I have seen.

Security in Database Design

The worst issues I have seen are in the realm of security. Of course the most obvious is SQL Injection vulnerabilities (if you don’t know what that is, again call me) but there are more. I’m talking about the great big whopper such as:

• Applications AND users connect using the SA/root login
• The SA/root login is still the default or not set
• user roles are nonexistent.
• (This is my favourite) The database login and connection details are stored in a text file config.txt in the webroot or even worse in a JavaScript file.

These security holes are just asking to be hacked. Oh and let’s not forget trying to restore a database when backups where never created in the first place.

If you even suspect you might have these issues but have not had any problems so far then don’t think you have been fine up till now. These are show stoppers that must be addressed so listen up, luck does not last forever.

Now before I go on let me state that no system is foolproof or 100% secure once it connects to the network, there are too many technical and human vulnerabilities to ever be completely locked down, but you can make it very difficult for a would be hacker.

Common Sense Guidelines to Database Security

Below are the guidelines we follow at ColdSQL for database security. These guidelines are constantly reviewed and updated. Let me emphasise that again, our security guidelines are continually reviewed. It is not enough to set out guidelines; they must be followed and reviewed on a regular basis.

1. SA/root remote login is disabled
2. Different roles must be defined for different levels of access
3. Data can only be added modified through stored procedures
4. Adhoc queries are only be allowed on views which belong to a limited user role which cannot modify data or structure.
5. All adhoc queries must use parameters
6. All sensitive data must be encrypted
7. All passwords are stored as hash values
8. Database login details must be of minimum length and contain numbers, letters (upper and lower case) and symbols.
9. The database login credentials are never stored with the application in any publically accessable area.

Of course database security must be backed up by application security and user training but the above guidelines are the very minimum that should be followed for your database and you absolutely must have a good back and restore plan in place in the event that something does happen to your database.