应用程序用户==数据库用户? [英] Application user == database user?

问题描述

我有一个应用程序，许多用户可以通过该应用程序访问 MySQL 数据库.现在让我感到困惑的是我如何管理用户.在我看来，有两种不同类型的用户 - APPLICATION 用户和 DATABASE 用户.这些应该是同一件事还是不同?

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:

当用户登录应用程序时，单个数据库帐户登录到 MySQL 并检查应用程序用户名是否存在，并比较密码哈希值.这些都存储在 MySQL 的 App Users 表中.所有这些用户都使用相同的 MySQL 帐户来访问数据库.

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.

应用中的每个用户是否也应该是不同的 MySQL 用户?

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

推荐答案

只有通过受控应用程序(或网络服务)才允许访问数据库的情况，单个数据库帐户所有应用程序帐户经常使用.在没有集中用户管理的环境中尤其如此；在 AD 上的 SQL Server 中(例如在使用 SharePoint 的情况下)，有时使用集成身份验证是可行的.

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.

原因很简单:

尝试将数据库帐户与应用程序帐户同步成为一场噩梦；而且，由于应用程序控制所有 SQL 数据访问和查询(即没有直接登录)，因此在数据库访问级别方面几乎不需要将用户 A 与用户 B 分开.

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;可以做普通应用程序用户需要做的一切.在不可变设计中，这可能会排除对大多数/所有表的删除/更新访问.我还没有遇到过为不同类型的普通"用户创建不同帐户的情况；同样，此时访问的责任在于应用.
2. app_admin;可以做 app_user 能做的所有事情，并且对只有高级管理员才应该拥有的特殊表具有 [更新] 访问权限 - 这是正在运行的应用程序的root"帐户.此帐户不应允许架构修改；这不是大多数应用程序的实时"方面.
3. database_admin;好吧，可以更改数据库的人.重要的是:不要使用此帐户从应用程序连接.这是开发者/SA 帐户 - 它可以执行所有操作，包括更改架构.

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.

对于多租户应用程序，每个租户可能有一个app_user"帐户(可能还有架构或数据库).

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

因为听起来您正在滚动另一个身份验证器，请花时间正确实现盐(大随机)+散列(bcrypt/scrypt/pbkdf2 - 没有 sha！).或者，考虑外部验证器或现有的审查库.而且，一如既往，使用占位符.

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.

这篇关于应用程序用户==数据库用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！