ColdFusion Securing Databases (part 2)

In ColdFusion Securing Databases (part 1) we looked at restricting what sql statements can be run with a datasource and partitioning applications to use multiple datasources and multiple users to improve security. In this article we'll look at setting the permissions on the database tables.

Let look further at the simple poll application. Here is the SQL to create it's tables:

The question tables stores the questions, each question has several possible answers that a user can select and the users answers are stored in the results table.

We'll create two datasources/database users to manage the system, one for public access and one for administrators. We'll call the users "public" and "admin".

To give users access to SQL tables you use a combination of grant and deny SQL statements.

First off let deny access to both users to all tables.

This denies select, insert, update and delete access to both users on all three tables.

Let look at select access, the public user needs to be able to view questions and answers and the admin user needs to be able to view the all tables. This is done by granting select permission to the tables.

Administrators can add new questions and edit existing questions. We grant these permissions like so:

Public users need to be able to add new results.

Notice that no user has update permission on the result table so that results can never be modified only created by the public user. The admin user cannot modify the results in any way. While it may be tempting to give the admin user permission to do anything you'll can find they actually need less access that public users.

Also notice that no user has delete permissions, so once data is inserted into the database it can't be deleted via SQL statements by these two users. You may want to allow administrators to delete questions or answers but it may be better to do this via a delete flag as they already have update permission on the question and answer tables.

With the permissions in place above if someone got access to the database via SQL injection (or any other method) they wouldn't have permission to actually much at all reducing the amount of harm that could be caused.