Monday, 30 April 2012

Basic Database Security Checklist

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 and look at their development check lists, I bet they are 100 times longer than anything we could think up.

Friday, 27 April 2012

Conditional Roles in VSTS

We have databases that exist separately in our dev environment but are deployed into common databases. This is fine since we produce schemas and these are all added to the common databases and work fine. We had a problem though in that we share roles across the databases. If we include the role in multiple databases, they work fine in isolation but when the second schema is created on the common database, it fails to create the role since it already exists. It therefore bombs out which means nothing is created in the database. It would appear that it is not possible to include the conditional addition of the role, if you add IF NOT EXISTS... to the role sql in the VSTS project, it will not build. The only workaround I have found so far is to create the role conditionally in the pre-deploy file and then assign its privileges in the post-deploy file. Not great but it works.

Wednesday, 25 April 2012

Custom Configuration Files aaaaaaahh

If anyone has done this, you might have come across all sorts of pain. I was trying to do a basic custom configuration like this:
        <add connstring="blah" displayname="blah"></add>
I started with a simple configuration section with a single property like this and it all worked fine:
<sessioncomparersection environments="test">
I then added the ConfigurationElementCollection derived class for the list of environments and created a specialised ConfigurationElement class for the 'add' data in the collection exactly as per the examples I found on the web. Sadly, I then started getting ConfigurationErrorsException "unrecognized element 'environments'" when it tried to load the configuration. Sadly this is caused by many things, none of which helped me.
On a whim, I removed the ConfigurationPropertyCollection from the top level section (which was now empty but which was returned by an override method in my ConfigurationSection top level class) and this made it all work again!
I guess if you need the collection at the top level, you need to add a reference to the collection property (Environments in my case) into the property collection so it knows that it is present. This is despite the fact I had marked it up with ConfigurationProperty.

Thursday, 19 April 2012

Rhino difference between .Is.Anything and .Is.Typeof

Rhino mocks, when matching arguments, it is preferential, as usual, to be as specific as you can about expected arguments (including repeat information). For instance, if you will call a method with e.g. a username, then set the expectation to expect that specific username:

Mock.Expect(r => r.SetUsername(Arg.Is.Equal("expectedusername"))).Repeat.Once()

There is an "Is" constraint called Anything. You might expect the following to match any string:

Mock.Expect(r => r.SetUsername(Arg.Is.Anything)).Repeat.Once()

But it doesn't! It matches anything! This is slightly misleading since the Arg class is parameterised and would seem a bit redundant but it is still the way it is. What you should do to achieve this functionality is to use TypeOf instead:

Mock.Expect(r => r.SetUsername(Arg.Is.TypeOf)).Repeat.Once()

Callback arguments didn't match the method arguments

One of those, "it sounds obvious but I can't work it out" errors. This one in Rhino mocks, made more confusing by profuse use of generics, anonymous types and lambda stuff.
I had a mock expectation that was set up like this:

 _DataService.Mock.Expect(r => r.UpdateData(Arg.Is.Equal(Id), Arg
                .Callback((Guid c,Summary m) => // etc

which caused the above error during the mock setup. I tried lots of various implicit and explicit changes to infer arguments etc. They always built but always crashed. The reason in my case was simply that UpdateData is not a generic method (despite all the GetData methods being generic) but which takes an OBJECT as the second argument. This fact was masked by the way I was calling it with a Summary object and I couldn't understand why it fell over. The code should have been like this:

 _DataService.Mock.Expect(r => r.UpdateData(Arg.Is.Equal(Id), Arg
                .Callback((Guid c,object m) => // etc

Which I could then cast to the correct type inside my callback.

Sometimes you can have simple, free and effective

What am I talking about? I bought a new MacBook pro laptop. Without defending it too much, for music software, it is much more consistent than Windows (no random defrags or virus scans) and the battery life is fantastic.
Anyway, I bought it and went home and immediately had problems connecting to my wifi - it sometimes connected, sometimes didn't (timed out), it dropped out and ran slow as well but was fine on the ethernet cable. My best guess was that the MacBook reception was not great and the D-Link DIR-615 output possibly not very high. Other devices connected OK so the router wasn't completely busted and I doubted the MacBook was broken, albeit the aluminium wasn't helping.
I noticed online that loads of people had similar problems and I wasn't too keen to shell out £120 for an Apple router or risk buying another that may or may not work. I couldn't imagine Virgin Media agreeing to replace anything.
Anyway, the solution was dd-wrt an open source Linux project which is a replacement for your router software. Of course, this sounds like it is either difficult to do or perhaps there was some hidden charge (if you like it, buy it) but none of these was the case.
I simply looked up my router which was listed, downloaded the firmware and uploaded it to the router as if it was a D-Link update and it was all sorted, the output boosted and the MacBook now has no problems with wifi.
Things to note:

  1. Write down your host name from the existing router along with any specific dns settings etc, these will obviously all be lost when you update and you will need to enter them again. Most of the defaults were fine however, it would just be port-forwards, mac filtering etc. that you might have setup yourself.
  2. The default dns range was different between the DLink and wrt software so any static ip addresses on your home devices would need changing.
  3. You risk bricking your router if you use the wrong firmware etc so be warned!
  4. I had to fully reboot the cable modem and router (off for 30 seconds) which meant I obtained a new public IP address but it meant the modem mac address cloned properly into the router.

Thursday, 5 April 2012

Cannot step into stub project with VS Debugger

I had a really annoying problem where I was trying to debug a stub project (which was an asmx) and even though I was building the debug build and attached to w3wp.exe, every time I tried to step into the stub code or break-point it, I could never break in the code.
Often this is caused by trying to breakpoint in code that is not the code that was used to compile the service but in my case it definitely was. The project was pointing to the IIS web site which was pointing at the correct location on the disk and my build configuration was all set to debug.
Eventually, I noticed that the web.config for the stub had which apparently overrides all other settings and meant that the debug information was not compiled with the stub meaning it couldn't be debugged. Set this to true and we were all good again!