Applying least privilege to database connections

I've noticed that most FOSS applications (Wordpress, for example) only uses a single set of database credentials that have been granted all permissions. This seems like it violates the principle of least privilege.

In writing such an application, would it be better to use several accounts, for example, an account only for SELECT queries, another for UPD开发者_StackOverflowATE, etc?

This is definitely a violation of the principle of least privilege. Let's go back to the definition:

In information security, computer science, and other fields, the principle of least privilege, also known as the principle of minimal privilege or just least privilege, requires that in a particular abstraction layer of a computing environment, every module (such as a process, a user or a program on the basis of the layer we are considering) must be able to access only such information and resources that are necessary to its legitimate purpose.

In your Wordpress example, a public user is retrieving data from the database with a SQL account which also has the ability to change or delete that data. The "least privilege" for this user would not include access to change that data whether it be directly on the table of via a stored procedure. This is definitely not compliant with "access only such information and resources that are necessary to its legitimate purpose".

The risk in a SQL environment is primarily SQL injection. One little flaw and if that public account has the rights to do damage then you end up with all sorts of problems. Yes, input should be validated, yes queries should be parameterised but this is one additional layer of defence that gives you some extra insurance.

I talk about this specifically in OWASP Top 10 for .NET developers part 1: Injection.

I'd imagine it would be worse, if only for maintenance issues. One user means credentials are one place and they can be updated for each server in exactly one place. Further, most frameworks work with the assumption of one credentials set to rule them all, and while it isn't too difficult to allow for two+, it is more annoying.

There is some benefit in that if you have one user with select only privileges, you don't have to worry about SQL injection quite as much (certainly not on a Bobby Tables level), but even that is no guarantee, so you'd have to be sanitizing your data input anyway (they could still do injection attacks based on select...).

The best practice is to grant privileges to stored procedures rather than at the table level.