If you are reading this, you are probably not an expert in database security! If you are, please give feedback, if not, here is a simple cribsheet of things to consider when setting up database and web app security to avoid both unauthorised access and also damage to your data or database. These range from database specific to process issues.

  1. Never, ever, ever, use the system/db admin account for your web application! This is one of the most common causes of people being able to do damage. Bypass the web app security and you can do anything at all!
  2. Don't use the sys admin account during development. This is often seen as an easy way to get moving, "we'll worry about security later". The problem is, this rarely works. Even if you do find time later to try adding security, it becomes not just awkward (and you might forget the idea and leave the web site using the sys admin login) or otherwise you might think you have set it up correctly but forgotten parts of the site and end up with problems on the live site. It's much easier to setup and test while you are going along.
  3. If you have just a few groups of user (e.g. admin, user, readonly) then setup a database role for each of these and map the users to the roles, this means that someone wanting to do damage would already need to be an admin AND hack the site, whereas the many more standard users cannot do much, even if they can hack the system. It is a game of statistical risk.
  4. Deny permissions to all web application roles that are not needed. Very few web apps need to create or drop tables so deny the permissions.
  5. Use stored procedures for all DB access if possible, certainly for tables that contain sensitive data. This makes it much easier to control what database users can and can't do. For instance, an audit table might only have a single proc (e.g. procAddItemToAudit) and therefore this restricts the ability to select, update, delete or drop the audit table. Likewise procCheckPassword(user,password) enables you to prevent anyone having to SELECT from the user table.
  6. Test the security that you have setup, in a methodical way. You might find that what you expect is not quite what is happening. SQL Server for instance, has some funny little gotchas that will either grant too much or too little security to a user.
  7. Do not attempt to do too finely grained security management on the database if you are using a web application. It will quickly become very hard to manage and again tempt people to take the easy way out.
  8. Use roles and schemas where possible to make it easier to manage groups of people. It is easier to maintain a role having a permission on a schema than it is to be messing around with individual people. All you have to do then is to add users to a role(s) and the permissions are unlikely to need modifying.
  9. Maintain regular backups, even if they are noddy and going to an external hard disk. Something is better than nothing although storing it off-site or in the cloud is a way of making it more resilient. More importantly, test that you know how to restore the backup. Don't leave it until you actually need to restore it before you realise it doesn't work! Repeat this every time you upgrade your database server. Regardless of the theory of what will work, you need to ensure it does work!
  10. Decide on what needs to be encrypted before you start building databases. Understand how to set it up and more importantly, how to manage it. A chain is as strong as its weakest link and having keys that are guessable or discoverable removes most of the advantage of encryption.
  11. Beware of using online forums to inform you of security. Prefer formal training or at least getting multiple viewpoints. There are very wide views on security from "don't bother, you should trust people" through to, "create a department of 100 people to manage it". Most likely, you are somewhere in between.
  12. Security costs money, despite the fact that lots of information is free. If you are creating enterprise-grade web applications, it will cost money to implement and test the security measures. You may have to pay contractors or specialist security companies which might well not be very cheap. If you get it wrong, you can lose respect, reputation and more importantly can cause massive damage to the client who is trusting your work.
  13. Consider using security agencies who can vet your development process rather than just your product. There are many easy ways to create a security policy that will cover most of the common pitfalls.
  14. Don't invent your own security. There are so many instances of this, often, I would guess, because we are afraid of exposing our work to the outside and are not keen to pay for experts to do it properly for us. A false economy. Check out owasp.org and look at their development check lists, I bet they are 100 times longer than anything we could think up.