Why storing users passwords at all?

Question

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?

Answer 1

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).

Answer 2

The dividing line is getting a little bit fluffy at that point, but I would consider users and permissions at the database level to be part of the schema not part of the data, and in general applications have no place modifying the schema (there are, as always, exceptions to this rule).

I'd rather not give the application(s) the permissions they need to manage schema objects (logins, users and permissions) as new users are needed and old ones leave, because if that application gets hacked the attacker then has access to those permissions making it easier to crack the database open further. In MS SQL Server unless you are using fully contained databases logins are server level objects so you are needing to hand out rights beyond the single application database making it even more risky.

Furthermore even if you do have per-application-user accounts at your database level you still need application level user accounts to deal with non-authenticated requests - i.e. if the application needs information from the database before the application user has successfully authenticated (perhaps to display status information on the welcome/login screen?).

Also if portability between database engines is a goal (perhaps your app wants to be able to run on both mysql and postgres?) then your applications will need to abstract out the user/login management functions for each engine as they are not standard between them - if you are going to that effort then you might as well implement passwords yourself and get the management options you desire instead of accepting the lowest common feature-set the engines offer.

Answer 3

Restating the question

Why storing users passwords at all?

The simplest answer is that you have to do so. You still store passwords in your alternative method. It's just that you use the database's built-in system to manage the storage. So your method is only as good as your database's. That may still be better than whatever you might do otherwise, but it's not avoiding storage. It's really just avoiding coding storage.

It also might not be better. If I compromise a database and get copies of the files, I can access the tables directly. So there's little point to using a brilliant password management system. Because if someone can access the system level table containing the passwords or hashed passwords, they can also access the files directly. Why bother with cracking passwords when you already have the data? It's not like you can encrypt the data easily either. Each user needs to be able to access it. So user-level encryption won't work so well.

But what you really seem to be asking is

Why use an application level authentication scheme when the database already has one?

Security

Ignoring the possibility that you could write a more secure version, others have suggested a number of reasons. I generally agree. But there's another that no one has mentioned yet. You are compromising the security of your database.

In this system, every user of your application has a database user and password. Sure, it's a limited user, but it's still a user who can connect to the database. Even if you use row level security, you are still allowing end users to know database connection info. If there is ever an exploit where a user can get even table level access, you've opened up your database to attack. And some exploits have gone beyond that to administrator access.

In the layers of the security onion, the normal system is:

• Database, which only allows connections from certain machines.
• Database, only allows connections as certain user/password combinations.
• Servers with database permissions that allow connections from applications.
• Applications are run on the server.
• Applications have database connection info with limited privileges.
• Applications authenticate users before allowing them to modify the database (except possibly to make new accounts).

In this system:

• End users know user/password combinations that will work on the database, admittedly with very low privileges.
• Only need to comprise a server or convincingly pretend to be an authorized server.

We've lost the entire application level of security. And we've voluntarily given up part of the database layer. So we only need two exploits:

1. Spoof or compromise an authorized server.
2. Increase the limited privilege account's access.

If we can do those two things, we have access to the database. So we go from three layers down to two. (The third layer in the normal system is that you need a valid user to connect to the database.)

You are going to be doing a lot of management of these accounts. What if you make a mistake? Instead of limiting user X to just certain rows, you give them all access to a critical table. That kind of stuff is normally done manually and there are only a few of them, so they are easy to audit. But in your system, you could be doing thousands or millions or even billions of user accounts, each with their own idiosyncratic access.

For that matter, does the database's system scale to millions or billions of users? If it does, what about the number of rules? If each user takes up a hundred or more rules about what they can and can't access, does that scale up to a billion users? You're taking a normally small scale system and making it large scale. That won't necessarily work. You may find system limitations as you grow.

Answer 4

What you describe will handle the authentication, but not the authorization (what data the user can access) or only in the most simple of the use case.

To continue on your example with stackexechange : How would you make deleted post only visible to high rep user ? So for access rules, you'll still need logic in code. Instead of sharing the access logic between database rules, and applicative access rules, most developpers prefer to have them all in the same place.

You'll also need a table to store the user info : a user is not just a username + password. He has an email, a reputation, and so on. So you still need the user table, that you have to ensure is in sync with the database user table, provided you can access it.

With your solution, you can just omit a password column, which is not so hard to fill-in : there are good libraries and documentation on how to handle/hash passwords.

An other point: how would you create a connection pool ? I know only jdbc (java <-> db), and you need to supply a username+password to get a connection, that you can then pool.

I thought of some other points that will be harder / more convoluted to implement than with the established way :

• handling password recovery
• 2 years after the initial implementation, your product manager ask you to add support for oauth scheme, or allow double factor auth

Answer 5

One other point: many[1] web applications allow you to register and create a user account on-line via the app itself. Which means your anonymous user (who should have least privileges one would assume) has to have rights to create users and grant privileges!

OK, it may be possible to limit what privileges it could grant, and give higher-level users more grant options (I'm not aware it is though - isn't "grant privileges" a single privilege often). But still, it means putting something with grant privileges on-line for everyone to hack at. I'm pretty sure my DBA wouldn't like me if I did that :)

I know at one level this problem exists anyway, but you have layers of defence, especially to deleting data, if you don't allow the web app to manage DB users.

Also, this would prevent any use of pooled or reused DB connections.

[1] Most I would say, but not figures to back it up