UPDATE: So after reading Ray's Post here and after reading the comments on that post, I think I have to agree with my commenters as well as Ray's that this is not the best idea. The level of complexity that this adds does not make it worth the extra security (perceived or otherwise) that the technique may offer.
For my first security series post, I am going to keep things simple. This one is more of a tip/best practice than an article. At least, that is how I am planning it in my head, we'll see if that changes by the bottom of the page.
Today I want to discuss something that most developers and administrators will find to be very annoying. I know I do.
Everyone knows that you should never set up your ColdFusion datasource(DSN) to use the root, SA, SYSTEM, or any other administrator log on(and if you didn't know that, now you do). I am not going to beat this one any further into the ground, most "in-the-know" developers are on this. So I will say it one more time and then were are done with that "NEVER set up a datasource with a DBMS system admin account"
So what should I do then, Jason? Is what I suspect you are going to ask next. Well, I guess I can take the time to tell you what I think you should. ;)
Most developers and admins have figured out that you should have a datasource for your application that only has the necessary permissions for the tasks of the app. In most cases this will be SELECT, INSERT, UPDATE, and (sometimes) DELETE. Removing GRANT, REVOKE, ALTER, CREATE and some of the lesser known permissions is pretty much a no-brainer for most applications. But it seems to me that INSERT, UPDATE, and DELETE are still some pretty powerful statements. In fact, the last time I checked:
DELETE FROM customers;
does almost as much damage as:
DROP TABLE customers;
So should your standard anonymous users who will only be accessing pages that use SELECT queries be using a DSN that has UPDATE, INSERT and DELETE permissions? I'd say "NO!". Especially since SELECT queries are usually the easiest to exploit(we'll cover that another day).
Here is what I am driving at: Your application needs more than one DSN. In fact, it probably needs 3 or 4 DSNs. Here is an example of what I would set up for an application that followed this methodology.
We'll call my Application MachCookBook (It is a cookbook/recipe storage app developed in Mach-II that I am working on to help me learn OO frameworks). The db users and DSNs I would set up for this application are:
- MachCookBook_SELECT - This DSN would have only the SELECT priviledge in the DBMS and would be used on all SELECT queries for all user types.
- MachCookBook_INSERT - If anonymous users were allowed to do inserts, like submitting a new recipe anonymously, then I would create this DSN for that INSERT query. This DSN would have only the SELECT and INSERT permissions. If I was taking the approach of only allowing authenticated users to add new recipes, I would not include this DSN because the following DSN would handle those user.
- MachCookBook_UPDATE - This DSN is for authenticated users who have the ability to add and edit recipes in the application. I would still use the MachCookBook_SELECT for any SELECT queries to which this user had access(authenticated users can try to hack you too!). This user would ONLY have DELETE permission if it was absolutely needed for the application. In most cases I would encourage the developer to only mark the record as deleted using an UPDATE statement and a flag column in the db table. For example, in the RECIPES table there would be a column named DELETED and it would be a Boolean or bit field.
- MachCookBook_DELETE - This DSN would have SELECT, INSERT, UPDATE, and DELETE (And possibly CREATE/DROP if temporary tables were being used). This DSN would only be used by site admins who needed to be able to delete recipes instead of just marking them as deleted.
Now, this may seem like a bear to manage, and in some cases, it may be. But when it comes to protecting your data, "An ounce of prevention..."
One of the first things I thought of when I was fleshing out this idea was how I would handle my DAO objects in my model(for those not yet doing OO, ignore this section).
After a few seconds thought it occurred to me that I can still pass in a single datasource name to the DAO when it is instantiated and create my queries inside with dynamic names
as I normally would but with the extensions above tacked on.
<cfquery name="getRecipe" datasource="#variables.dsn#_SELECT">
SELECT recipeName ...
<cfquery name="setRecipe" datasource="#variables.dsn#_UPDATE">
UPDATE recipes SET ...
I am working now on my first application that will implement this idea. I'd like to know what people think about it. It is a work in progress. I have not yet decided if this is overkill or if more separation is needed. This can be taken even further inside of the database by getting into table-level privileges. But I think I would need to be working on a very high-security level application before I would go that far.