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

问题描述

我有一个应用程序，许多用户将可以访问MySQL数据库。现在，我很困惑的是如何管理用户。如我所见，有两种不同类型的用户-APPLICATION用户和DATABASE用户。这些应该是相同还是不同？

让我举例说明。这就是我现在的工作方式：

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

应用程序中的每个用户也应该是不同的MySQL用户吗？

解决方案

仅允许通过受控应用程序（或Web服务）访问数据库的地方，通常使用所有应用程序帐户的单个数据库帐户。在没有集中用户管理的环境中尤其如此。在AD上的SQL Server中（例如在使用SharePoint的情况下），有时使用集成身份验证是可行的。

原因很简单：

尝试将数据库帐户与应用程序帐户同步已成为噩梦；并且，由于应用程序控制着所有SQL数据访问和查询（即没有直接登录），因此就数据库访问级别而言，几乎不需要将用户A与用户B分开。 / p>

在此配置中，应用程序承担验证，授权和标识用户访问权限的职责。

话虽这么说，最好让具有不同访问级别的不同数据库帐户。这些可能类似于：

1. app_user；可以完成普通应用程序用户需要做的所有事情。在不可变设计中，这可能会排除大多数/所有表的删除/更新访问权限。我还没有遇到过为不同类型的正常用户创建其他帐户的情况；


2. app_admin;此时，访问权限在应用程序上。可以做app_user可以做的所有事情，并且可以[更新]访问只有高级管理员才应该拥有的特殊表-这是正在运行的应用程序的 root帐户。此帐户不应允许架构修改；


3. database_admin;并不是大多数应用程序的实时方面。好吧，可以更改数据库的人。重要的是：请勿使用此帐户从应用程序连接。这是开发人员/ SA帐户-它可以做所有事情，包括进行架构更改。

对于多租户应用程序，可能会有一个 app_user （每个租户的帐户（可能还有架构或数据库）。

由于听起来您正在滚动-另一个身份验证器，请花些时间正确地 实现盐（大随机数）+哈希（bcrypt / scrypt / pbkdf2-不行！）。或者，考虑使用外部身份验证器或现有的经过审核的库。而且，像往常一样，使用占位符。

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?

解决方案

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.

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