Application user == database user?

Question

I have an application whereby many users will have access to a MySQL database. Now what I'm confused about is how I manage users. As I see it, there are two different types of user - the APPLICATION user and the DATABASE user. Should these be the same thing, or different?

Let me illustrate. This is how I have it working now:

When users log into the application, a single database account logs in to MySQL and checks if the application username exists, and compares the password hashes. These are all stored in a App Users table in MySQL. All these users use the same MySQL account to access the database.

Should each user in the app be a distinct MySQL user also?

Answer 1

Where access to the database is only allowed through a controlled application (or web-service), a single database account for all application accounts is often used. This is especially true in environments without centralized user management; in SQL Server on AD (such as in the case with, say, SharePoint) it is sometimes practical to use Integrated Authentication.

The reason is simple:

It becomes a nightmare to try and synchronize database accounts with application accounts; and, because the application controls all SQL data-access and queries (i.e. there are no direct log-ins) then there is little need to separate user A from user B in terms of database access levels.

In this configuration, the application assumes responsibility for authenticating, authorizing, and identifying user access.

That being said, it's good to have different database accounts with different levels of access. These might be similar to:

1. app_user; can do everything that a normal application user needs to do. In an immutable design this might exclude delete/update access on most/all tables. I've yet to run into a case when I've created a different account for different types of "normal" users; again, the onus of access is on the application at this point.
2. app_admin; can do everything app_user can, and has [update] access to special tables that only a high-level administrator should have - this is the "root" account of the running application. This account should not allow schema modifications; that is not a "live" aspect of most applications.
3. database_admin; well, the person who can change the database. The important thing is: do not use this account to connect from the application. This is the developer/SA account - it can do everything, including making schema changes.

For multitenant applications there might be an "app_user" account (and possibly schema or database) per tenant.

Since it sounds like you're rolling yet-another authenticator, take time to correctly implement salt (large random) + hashing (bcrypt/scrypt/pbkdf2 - no sha!). Alternatively, consider external authenticators or existing vetted libraries. And, as always, use placeholders.