Multiple Datasource - Security Series #1

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.


Matt Turner's Gravatar

Your idea seems planned with good intensions. However I might point out some down sides beyond just the extra complexity your introducing, for what I see as only a marginal gain.

Taking this idea to an enterprise level and I think you will want to seriously rethink this. Consider that each unique DSN has its own connection pool at the java level to your database. So a totally fictitious but not impossible example, you have a request that runs 1 selects, 1 update, 1 create, and 1 delete (unlikely but stick with me). Now you have have 4 connections to your database from cf and you are taking up 4x as many slots in the pool for a single user. Bear in mind that by default CF keeps those connections active for the duration of the request, which is normally a great benefit but is in your example now hurting you.

Beyond just the connections you have now added a level of network negation that needs to occur for each of your four data sources which could add some (maybe not noticeable without load) additional time to your run times. There is also some increased potential for contention within your database as you have separate users making request that could potentially lock records up on on each other.

I suppose my concerns are born in a world that does not really exist in most applications. An enterprise implementation would almost certainly use stored procedures to avoid the security concerns you are attempting to address so perhaps your solution does add some value for inline SQL but i'm not sure how much. An interesting discussion point at the very least.

# Posted By Matt Turner | 5/6/08 4:32 PM
Daniel Garcia's Gravatar

We are actually going through this same exercise at work. We had come up with a similar scenario, but discounted it due to administrative overhead (didn't really think about the connections issue). One idea that we came up with is to create 2 user accounts in the database (1 with db_datareader/ db_denydatawriter access, and 1 with db_datareader, db_datawriter access) that would be used for a read only user and a read/write/delete user. Obviously we are using MS SQL for our database.

We then create 1 datasource in the CF Admin and specify the read only account as its user. We would use that for all selects. In the code, we will then add the username/password to CFQUERY for the read/write/delete account to allow inserts, updates, and deletes. The majority of our code would use the read-only user, but we can specify the other as needed.

We could add more user accounts in the database for different access levels, but we decided to keep it at 2 for ease of use.

Does anyone see any problems with this method? Thanks.

# Posted By Daniel Garcia | 5/6/08 5:09 PM
Dan Rouw's Gravatar

I work with Daniel and we were just discussing this here. My personal preference is to set up a dsn with a default username and password that only has SELECT rights. That way, if someone discovers your dsn name they can only read your data unless they figure out the credentials of an account with elevated permissions.

Also, I believe that ColdFusion dsns are registered as system dsns on the server (at least on Windows) which would allow any application on the server (web or desktop) to access that dsn if it knows the dsn's name.

# Posted By Dan Rouw | 5/6/08 5:27 PM
Jason Dean's Gravatar


Thanks for that feedback. I see what you are saying and I had not considered any performance issues when I thought of that. I think you are right in that the in some situations this idea would not be ideal, and it definitely does come with some overhead, but in some situations, security trumps performance, and if you want to have the best of both world then you have to purchase the hardware to support it. In an enterprise environment I, personally, would tell the decision makers that they need to pony up the dough for more DB servers rather than reducing the security of the app to save a few bucks on hardware. No granted that also depends on the sensitivity of the data. I would not suggest spending $150,000 or more on servers to protect my recipes. But if I was protecting thousands of credit cards, or medical records or something like that...

# Posted By Jason Dean | 5/6/08 5:49 PM
Jason Dean's Gravatar

@Dan - I'd be interested to see anything that discuss that DSNs are set up as system DSNs for Desktop and Web Apps. Becasue everything that I have ever experienced tells me that they are set up for ColdFusion Apps only and also that if you use Sandbox Security in the Enterprise Server that you can specify which web apps they are allowed for. Sandbox Security and DSN access are on my list of issues to research and blog about, so if there is anything that shows that DSNs are registered that way I would LOVE to see it.

# Posted By Jason Dean | 5/6/08 5:55 PM
Dan Rouw's Gravatar

@Jason - I double checked and you are correct, ColdFusion dsn's are not system dsn's. I must have been thinking about ColdFusion 5. Back then the ColdFusion administrator showed all system dsn's on the datasources page whether you set them up in CF or on the OS.

Also, we are running CF Standard Edition so we cannot use Sandbox Security.

# Posted By Dan Rouw | 5/6/08 6:16 PM
Jason Dean's Gravatar

@Dan - Thanks for the update on that. And just as a clarification, I was mostly suggesting the use of multiple DSNs to protect you from your outside users who might try SQL injection attacks or remote service layer exploits. You are correct that if you are not implementing sandbox security then you are not very well protected from other applications on the server that might exploit your app. At that point it is more "Security through Obscurity"

# Posted By Jason Dean | 5/6/08 6:36 PM
Matt Turner's Gravatar

@ Dan

Just as a point of trivia: version 6.1 is when the CF team took CF from a C++ app to the java platform and thus the move to JDBC to make connections as apposed to using system datasources, thought not out of necessity just based on performance.


You should look into the adminAPI, there is almost limitless amounts of information about your coldfusion installation available through it, including the ability to manage and view datasources available in CF.

As per your comment about spending 150k on servers, yeah your probably right but the truth of the matter is that you could find yourself in the same enterprise situation at a far lower price point then you might expect. You could easily find yourself doing 100-150 requests per second given an idea that with even minimal success and still managing to handle that load on a single instance of mySQL or postgres (both free) and two or three (redundancy) CF servers all while just barley eclipsing $100/mo worth of server cost. With the way a good idea can catch fire these days it's fairly easy to get to a point where the idea can be ruined by bad code architecture. Yes throwing hardware at a problem can make it go away for awhile but you are marrying yourself to that solution up front without thinking through the "what happens if this thing goes big?" problem up front, at least a little.

@ Daniel

Your approach certainly seems more logical to me and has no apparent drawbacks, though I am still not really convinced it's any more secure. Perhaps it does add some obscurity and a bit of complexity for a hacker as he now has to determine where a query exists that is being run as the right user in order to attempt an attack. Ultimately its a problem that has no catch all solution, each query is going to need to be protected on a one by one basis using cfqueryparam or stored procedures. One thing I often do when I am worried about hijacked values getting into my queries is to hash my call signatures appropriately in order to prevent manipulation.

# Posted By Matt Turner | 5/6/08 6:48 PM
Daniel Garcia's Gravatar

@Matt - Thanks for the feedback. BTW, we are using cfqueryparam or stored procedures. I didn't mention it as I thought that was a cardinal rule by now. :)

What is your methodology for securing this?

@Jason - To clarify Dan's comment about using CF Standard, we have our own dedicated servers where we are the only ones with access. We do have a hardware firewall and only have ports 80 and 443 open to the world.

I don't know if you already meant this, but one thing with our approach is that we don't rely on the CF DSN security limits (ie disabling functionality) but rather set permissions at the DB user level, which is presumably much more secure. Doing both is probably the best idea if using your approach, but for us wanting to just have one DSN, I think we'll be ok with doing at the DB.

Good posts and comments.

# Posted By Daniel Garcia | 5/6/08 9:08 PM
Jason Dean's Gravatar

@Dan - Yes, I was referring to setting the permissions at the DB level, thank you for clarifying. It certainly would not hurt to also specify them at the DSN level, but you are right, I would not rely on doing it at the DSN level. Maybe it is perfectly safe to do it that way, I have never heard one way or another, but to me having the ColdFusion Server handle your database permissions is akin to locking the front door of the bank so that you can leave the vault open.

# Posted By Jason Dean | 5/6/08 9:37 PM
Steve Bryant's Gravatar

Another thing to consider is that cftransaction requires that all queries in a transaction block use the same datasource.

So, this approach would prevent you from doing multiple actions (of different types) in one cftransaction block which could certainly present a major obstacle for some scenarios.

# Posted By Steve Bryant | 5/20/08 8:13 AM
Jason Dean's Gravatar

@Steve - That is a good point to remember. In most cases a cftransaction would probably only involve INSERTS, UPDATES, AND the occisional DELETE so the UPDATE DSN or DELETE DSN could be used for all of the datasources within the cftransaction block. And if there were a few SELECTS in there, they could certainly use those DSNs as well too. That would be an exception to the rule, but I don't think it would degrade the security of the app in anyway since the appropriate type of user would be authenticated ot use the overall block at that point anyway. YOu would just need to make sure your SELECT statement was locked down with appropriate CFQUERYPARAM statements.

Also that reinforces the idea that the appropriate RDBMS user with appropriate permissions is still in use in the DSNs of all levels.

Great reminder though, I would not have thought of that until I came across it in practice.

# Posted By Jason Dean | 5/20/08 11:47 AM
jenny's Gravatar We all are looking to get the roblox hack at and this could be so easy to get the roblox hack online.
# Posted By jenny | 3/18/18 3:24 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner