Security – Why Storing Users Passwords at All?

Securityusers

I occasionally see questions asking how to safely store user passwords for a web application (using an RDBMS, I'm not talking of Facebook or Twitter). The usual answer is "salt the password, then hash it with a strong algorithm such as TDES or SHA512".

My question is : As an RDBMS user, why should I bother at all with the password storing problematic at all since most engines have a built-in authentication mechanism.

For example, if some user X wants to create an account user password Y on my web application, how is issuing the following query wrong:

CREATE USER X WITH ENCRYPTED PASSWORD Y IN GROUP baseuser;

Then within my application, the user can open a connection to the database using his credentials and I don't have to bother at all of password management.

I see multiple advantages to this method:

  • If the RDBMS decides that the encryption algorithm needs to be changed, I don't need to touch anything, just to apply the security updates;
  • It is easy for me to manage the users authorizations. If a user is promoted to an administrator's role, I just have to add the user to the corresponding group;
  • SQL injections are now meaningless, for I manage permissions to allow exactly what I want to allow to each user in the database (for example, in a forum like SO, adding new posts, answering to posts, commenting and editing/deleting his own questions/answers/comments);
  • An user account "anonymous" can be used for unauthenticated connections to my application;
  • Each user is the owner of the data he provided.

But on virtually every question I see on this topic, there seems to be a general consensus that this is not the way things have to be done. My question is : why?

Note : The third point is allowed by policies in PostgreSQL, and security policies in Microsoft SQL Server. I realize that these concepts are newcomers, but anyway, now that they are here, why doesn't the technique I describe become the standard way to handle users accounts?

Best Answer

Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single dedicated service account is used to connect to the database back-end.

As a DBA, I don't want to have to manage, at the database level, the 10,000 active users of some medium-sized public-facing web application, or maybe the 2+ million users of some suddenly popular app.

In a very real sense, this is a difference in philosophy between application developers and database developers/DBAs.

Many/most application developers are not going to want to pass responsibility for major aspects of app functionality and/or business rules down to the database layer. Instead, they view the database as a tool to simply store and retrieve data.

In some cases, this might be short-sighted; many RDBMSes do have awesome features that could make app dev lives much easier (row-level security, columnar indexes, filestream storage, etc.).

But some of these cooler features are only available in newer versions, and organizations aren't always quick to upgrade existing environments (see this chart from 2014).

And in other cases, handling those things at the application layer is preferred (and not just for database platform portability, I frankly think that claim is overblown).

Related Question