The Right SQL User, For The Right Job
Just about every (web)application being built uses just one SQL user to do all its queries. This SQL user usually has all privileges to read data from the database, as well as perform UPDATE, INSERT & DELETE queries.
Wouldn’t it be a good idea to use a separate SQL user with only SELECT privileges to perform all your data-retrieval (= most common) queries? This read-only user could greatly increase the security in your application, too.
Say you forget to sanitize your input (shame on you!), and someone manages to perform a SQL Injection. If you performed the query with only SELECT privileges, the SQL Injection Attack suddenly seems a lot less harmful. No DROP TABLE statements, UPDATE or DELETE – it’s only possible to append another SELECT statement to your query … So it’s still possible to read data that wasn’t ment to be read, but there’s no permanent damage to your database as a result.
You could stretch this pretty far too, using an UPDATE-only user for every UPDATE query, DELETE queries to be handled by a DELETE-only user, … This isn’t what I mean, of course :-)
But just think about it, use the privileges/right management that your database-system provides you, and use them. This isn’t even that hard to implement, if you’re using any form of database-class/database-handler – but it will make your application a lot more secure.
Yup, nice stuff. Obviously, this method cannot forbid SQL Injection at all, but can surely prevent a lot of problems. Great idea.
Good idea, except for the performance implications – it requires you to open AT LEAST twice as many database connections – if you have a very busy website, it may bring the server down.
For the security reasons you cited, that’s why I normally go with stored procedures. All you need to do is pass in parameters (no parsing of SQL statements since they’re already compiled). It offers speed advantages.
Also, you can just set the user to ONLY be able to execute stored procedures. And since the procedures are already compiled and do only what you made them do, it’s even more secure. Makes it harder for SQL injection.
@Jacek: that’s not entirely true. On an average site, nearly 80% of all queries are only SELECT’s, and the occasional INSERT/UPDATE. I agree it would be more demanding on the server, but it probably won’t kill it.
@John; indeed, it’s safer to use stored procedures – but that often requires more rewriting of the code. Creating 2 users for SELECT & everything else can be done easily if you’re already using a database-class.
PHP is the problem — there’s no foolproof way to isolate the sql users, so PHP code could be using either user.
I suggested pretty much exactly this approach to an Automattic employee, and that’s what he pointed out to me.
@Jacek: It only requires one additional connection for each user (minimum). Connections are usually pooled and you will probably be mostly querying data.
Nice article. Easy to implement and a no-brainer now that I think about it. Im adding users right now!