10

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?

  • 2
    Comments are not for extended discussion; this conversation has been moved to chat. – Paul White May 11 '17 at 21:59
  • Incidentally, the usual answer is wrong. You should salt the password, then hash it with a slow algorithm such as bcrypt or argon2. – Tgr May 13 '17 at 16:11
27

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

  • 2
    There is an existing 'holy war' in whether business logic goes in the application or in the database. If you have built an application where all business logic (in particular security) is in the database (using stored procedures, views etc.), then there might be an argument to utilise database security mechanisms because no one can connect directly and get around your security. I completely agree that you should avoid 'rebuilding' a security mechanism (i.e. login/pasword) in a custom table. Why do that when you already have active directory / O365 security. – Nick.McDermaid May 12 '17 at 0:26
  • 1
    @Nick.McDermaid I like to make sure that I mix business logic between DB and application fairly to help eliminate the potential for future developers to have any friggin clue what is going on, it also helps me maintain my job security as no one is crazy enough to want to manage it. – Der Kommissar May 12 '17 at 17:17
  • Throw a web service and a tomcat application server in there and you could work for IBM – Nick.McDermaid May 12 '17 at 21:15
8

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.

  • 1
    Ok, so the "application should not alter the schema" is in my opinion the first good point against the technique I suggest. However the anonymous account able to create new users only needs to be able to create new users with most basic access. Only administrators can promote users and drop users, which I guess is what is wanted in applications :-) The general data is visible to the anonymous user, so this connection can be used for content where authentication is not needed. Portability is also a good point, yet I think user commands are now part of the standard (not sure, it wasnt years ago) – Fabian Pijcke May 11 '17 at 15:02
  • By the way with this setting if a hacker gets access to the database as the anonymous user, he will be able to create as many users as he wants, but this is not a security issue, he could also surely bloat the database using one of the created accounts, that would be annoying but again no security issue (and he could do it using the standard interface too anyways, it would be way slower though :p) – Fabian Pijcke May 11 '17 at 15:16
  • 3
    I do agree that from a security point of view database level users would be better. However that is basically impossible to manage with e.g. online shops where you might have 50 million registered users. Each one would need to be a database user. Plus: this typically doesn't play well with web applications using a connection pool. – a_horse_with_no_name May 12 '17 at 12:15
6

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.

5

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
  • Something like CREATE POLICY deleted_posts_high_rep ON posts FOR SELECT TO baseuser USING ((SELECT rep FROM users WHERE name = current_user()) > 10000) answers your first question. I never said I did not need a users table anymore, and ensuring sync with the db user table is somewhat tricky but possible. The main point of the technique discussed is security. As for the connection pool, I work with OCaml and I have to say I don't get why I would need a pool of connections. I currently use a hash map associating cookie values to 0-ary functions returning connections :-) – Fabian Pijcke May 11 '17 at 22:19
  • 5
    Connection pool are implemented to enhance performance : with them, you do not need to establish a connection for each user request (so you save cpu/io/latency on creating tcp link + the all the connection and auth exchange with the database). As for the policy definition : your database will be constantly checking access right on every access to data, even when you already checked them once. Also 'access denied or forbidden' is not the same as 'OK, no result'. Not sure the security guys would prefer the OK version. – Thierry May 11 '17 at 22:32
  • 3
    Fabian, connection pooling is fundamental at any sort of scale, it's really a default that you wouldn't even consider not using. Here are some benchmarks just to give you an idea of how important it is: 600x performance increase with connection pooling (vladmihalcea.com/2014/04/17/the-anatomy-of-connection-pooling) and over 4,000x performance increase with connection pooling (progress.com/tutorials/jdbc/…). It's several orders of magnitude difference, it isn't a "nice to have", apps would grind to a halt without it. – Ivan McA May 12 '17 at 12:28
  • 1
    Interesting numbers. And indeed, i've never seen an application not using connection pools. But i did not know (and expect) them to make that much speedup. The other aspect you get with connection pool is resource control : thanks to them, a huge increase in client trying to connect all at once will make your application slow, but your database will not be impacted as much (if the pool is well configured). – Thierry May 12 '17 at 12:43
  • Ok, I just searched for more information about connection pools, and I agree that this is a very valid point as soon as the website has to handle more than one hundred connections per second, which is not that high. It seems that every connection in PostgreSQL eats about 10MiB of RAM! I never thought it was that much. – Fabian Pijcke May 12 '17 at 14:18
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.