Why storing users passwords at all?
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).
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.
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:
- Spoof or compromise an authorized server.
- 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.