10

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?

Amoeba
  • 1,573
  • 4
  • 19
  • 25
  • no i think it means that you put the application user credentials to log in from the application, where as you use the database user credentials to log in directly to phpMyAdmin or whatever you're using, not sure tho – Stephan Jul 04 '13 at 17:44
  • Do you want to *manage* "many" MySQL accounts individually? I wouldn't. Usually an application that controls all database access will act as the gate-guard itself - this differs from where individual users are given direct access! That being said, consider that there might be one or two different kinds of accounts with distinct limitations on what they can update/delete (i.e. normal users and "the *data* administrator") - this makes sense to differentiate and may even slightly improve security. – Paul Jul 04 '13 at 17:46
  • At the moment I just have two categories, normal and root. Root can create new app users, delete tables etc. Normal can only select, insert or update. – Amoeba Jul 04 '13 at 17:48
  • @Amoeba That sounds good to me! Having those as two different SQL users/roles/schemas coupled with a strong DAL sounds like a solid approach. However, *do not* expose an account that can run DDL - one can be a *data* administrator without needing to change the schema (which is a developer/SA job). – Paul Jul 04 '13 at 17:48
  • 1
    Possible duplicate of [Should application users be database users?](https://stackoverflow.com/questions/341271/should-application-users-be-database-users) – mach128x Jul 29 '19 at 14:17

1 Answers1

9

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.

Paul
  • 247
  • 1
  • 4