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:

view plain print about
1create table questions (
2    id int not null,
3    question varchar(1000) not null
6create table answers (
7    id int not null,
8    questionid int not null,
9    answer varchar(200) not null
12create table results (
13    questionid int not null,
14    answerid int not null

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.

view plain print about
1deny all on questions to public,admin;
2deny all on answers to public,admin;
3deny all on results to public,admin;

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.

view plain print about
1grant select on questions to public,admin;
2grant select on answers to public,admin;
3grant select on results to admin;

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

view plain print about
1grant insert on questions to admin;
2grant insert on answers to admin;
3grant update on questions to admin;
4grant update on answers to admin;

Public users need to be able to add new results.

view plain print about
1grant insert on results to public;

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.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)