What's the minimum footprint for a ColdFusion SQL Server datasource?

Question

I think I'm going to have separate datasources for various functions. Almost all my cfqueries are select statements, so my default datasource will have everything turned off in the administrator (except for select).

But for the "Insert/Update/Delete" datasource, I'd like to see if I can minimize it's rights as well.

Whenever I'm setting up a user in Microsoft SQL Server for my ColdFusion datasource, I always go under Security, Users, New User. Under "Owned Schemas", I've gotten into the habit of checking db_owner and under "Role Members", I check db_owner.

I got it to work that way and haven't looked back. So now I'm looking back and wonder, does this give my ColdFusion datasource more rights than it needs to have? Is there another option under "Owned Schemas" and/or "Role Members" that could minimize my exposure?

Heck, I'd be ok with reducing the number of features as long as I knew what features I would be turning off. For instance, I never alter a table from ColdFusion.

Some people advise to only allow stored procedures and never allow updating tables directly from the application, but I'm not following that advice.

Answer 1

It looks like what you are trying to do is have your datasource / connection have the minimal amount of access to your SQL Server.

While it is true that ColdFusion datasources can restrict what kind of statements can be run, you really want your SQL connection to do the restriction. Say you have a datasource called Employee_Info but it is being used for a ColdFusion app that really should only be reading the Employee_Info data.

What you should do is on SQL Server create a read only login. You may even want to call it Employee_InfoRO

Also See

How do I limit the permissions of a C# program to read only on an SQLServer Database?

This is an image from one of the answers on that question

This link may also be useful

When you create a new login in SQL Server it selects db_owner by default

Why not have ColdFusion restrict to Select?

ColdFusion is NOT a native SQL Server engine. It does not really know the meaning of the SQL statements, that is the job of the SQL Server engine. ColdFusion only takes its best guess at what is a select, insert, update, and delete. It does so by string comparison and is very heavy. SQL Server engine knows more about DB security than ColdFusion ever will.