Designing an extra ring of security

While there’s a lot of effort devoted to protecting a site from SQL Injection-style attacks through input sanitization and paramaterized queries, why does nobody approach it from the perspective of sandboxing the database user account?

Now, I’m assuming here that the injections you’re concerned about are the form of “unrequested TRUNCATE TABLE” or “bogus INSERT” SQL calls. Walking through the database to, say, log in as another user, or selecting a few extra rows, will not be solved by this technology.

How to approach this?

Simple: When you set up the database, add the typical user– with all privileges, but also a second user, with only “select” priveleges, perhaps even restricted to specific tables.

Then you use the second user for the vast majority of the site– anywhere you’re preparing customer-facing reports, dumping user data, etc. Only even connect as the “typical” user for those few tasks which actually involve altering the data.

If your secondary user tries an “update Account set is_admin=1” call, it bounces right off with an error. It even isolates out code if the underlying script is compromised– if someone adds a dumb mysql_query() call to the end of your WordPress template, it will run as the restricted user and fail.

While this is by no means a cure-all, it means that you can focus your efforts to tightening security on only a handful of scripts, rather than worrying about every login form or search field potentially leading to database damage.

There’s also a secondary angle– by dividing up “select” database usage from “modify” usage, you can potentially divide things better for load-balancing or extreme-load situations– for example, pointing “selects” to a fast but dumb data store which is synched… eventually… from a copy on the “modify” database server,