Layer-Design：在哪里检查数据库读取/更新的权限？

In most scenarios, you want a user only to be able to access entities in a database that was created by the user themselves. For example if there is a calendar that was created by User1, then only User1 should be able to read, update or delete this particular calendar and its contents in the database. This is not about authorization in general - in my project, there already is a role-based authorization component which checks if users belong to the "calendar-editor" role, but it doesn't check if a specific user is allowed to access a specific calendar.

在大多数情况下，您希望用户只能访问用户自己创建的数据库中的实体。例如，如果存在由User1创建的日历，则只有User1应该能够在数据库中读取，更新或删除此特定日历及其内容。这与一般的授权无关 - 在我的项目中，已经有一个基于角色的授权组件，用于检查用户是否属于“日历编辑器”角色，但它不会检查是否允许特定用户访问具体日历。

So eventually there has to be a comparison between the user-id of the current request and the user-id that represents the owner of the calendar that is requested. But I'm wondering where to do this. My thoughts:

因此，最终必须在当前请求的user-id和表示所请求日历的所有者的user-id之间进行比较。但我想知道在哪里这样做。我的想法：

• I could do it on the the DAO-level. But then every DAO-method needs an additional parameter that represents the user-id which makes these methods more verbose and decreases re-usability.

  我可以在DAO级别上完成。但是，每个DAO方法都需要一个表示user-id的附加参数，这使得这些方法更加冗长并降低了可重用性。

  E.g.

  例如。

  def findCalById(id: Int): Future[Option[Calendar]]

  def findCalById（id：Int）：Future [Option [Calendar]]

  becomes

  变

  def findCalById(id: Int, ownerId: Int ): Future[Option[Calendar]]

  def findCalById（id：Int，ownerId：Int）：Future [Option [Calendar]]

  An advantage would be that the permission check is basically done on the query-level which means that if a user has no access to a calendar, no calendar is returned from the database. But then again: if no calendar is returned in some cases, how do you distinguish between a calendar that does not exist and an existing calendar that cannot be accessed by the current user? Two different scenarios, producing the same outcome.

  优点是权限检查基本上在查询级别完成，这意味着如果用户无法访问日历，则不会从数据库返回日历。但话说回来：如果在某些情况下没有返回日历，那么如何区分不存在的日历和当前用户无法访问的现有日历？两种不同的情景，产生相同的结果。

• Another option could be to keep the DAO out of this and to do the check in a service layer or something similar. This would mean that the check is performed AFTER the requested calendar is returned by the DAO. This approach sounds more flexible than the other one, but it also means that in case a user has no access to the requested calendar, the requested calendar still consumes bandwidth and memory since it is fetched from the Database in either case.

  另一个选择可能是将DAO保持在此范围之外，并在服务层或类似的东西中进行检查。这意味着在DAO返回请求的日历之后执行检查。这种方法听起来比另一种方法更灵活，但它也意味着如果用户无法访问所请求的日历，则所请求的日历仍然消耗带宽和内存，因为在任何一种情况下都从数据库中提取。

Maybe there are additional options I didn't even consider. Are there any best practices?

也许我还没有考虑其他选项。有没有最佳做法？

Btw: there are no calendars in my web application, this was just an example to illustrate the issue.

顺便说一句：我的网络应用程序中没有日历，这只是一个说明问题的例子。

6 个解决方案

#1

I think, the key is to think about what exactly you mean when you say that the DAO approach "decreases reusability". If your requirement to enforce user access rights is universal to all applications of your DAO, then doing this at the DAO level actually increases reusability rather than reducing it: everybody using the DAO would be able to benefit from these checks rather than having to implement them on their own.

我认为，关键是当你说DAO方法“降低可重用性”时要考虑你的意思。如果您强制实施用户访问权限的要求对DAO的所有应用程序都是通用的，那么在DAO级别执行此操作实际上会提高可重用性而不是减少它：使用DAO的每个人都可以从这些检查中受益，而不是必须实现它们他们自己。

You can make the user id an implicit parameter to make these methods more friendly to upstream user. You could also make it return a Try (or, perhaps, an Either) to address your concern about distinguishing between a missing and an inaccessible object cases:

您可以使用户标识为隐式参数，以使这些方法对上游用户更友好。你也可以让它返回一个Try（或者，也许是一个Either）来解决你关于区分缺失和不可访问的对象案例的问题：

case class UserId(id: Int)
def findCalById(id: Int)(implicit user: UserId): Future[Try[Option[Calendar]]] = ???

Then, the caller could do something like this:

然后，调用者可以这样做：

implicit val currentUser = UserId(request.getUserId)
dao.findCalById(request.getCalendarId).map { 
    case Failure(IllegalAccessException()) => "You are not allowed to use this calendar"
    case Return(None) => "Calendar not found"
    case Return(Some(cal)) => calendarToString(cal)
}

On the other hand, if there are possible cases where the DAO would be used without a user context (an "admin" application perhaps), then you might consider either subclassing it to provide the access control to your "regular applications", or, perhaps, simply making an additional role that would allow a user access to all calendars regarding of ownership, and then using that "superuser" in your admin application.

另一方面，如果有可能在没有用户上下文的情况下使用DAO（可能是“管理员”应用程序），那么您可以考虑将其子类化为“常规应用程序”提供访问控制，或者，或许，只需创建一个额外的角色，允许用户访问有关所有权的所有日历，然后在管理应用程序中使用该“超级用户”。

I would not worry about the cost of having to load the object before checking the access (even if the object is really expensive to load, it should be a rare enough occurrence that someone tries to access an object he does not own). I think, a stronger argument against the service layer approach is exactly reusability and modularity of the code: the very existence of the DAO class with a public interface suggests that it can, at least potentially, be reused by more than one component. It seems silly having to require all such components implement their own access checks, especially, considering that such contract would not be enforceable: there is no way to make sure that somebody, who decides to use your DAO class a couple of years from now, will remember about this requirement (or care to read the comment). If you are producing a layer for accessing the database anyway, you might as well make it useful for something.

我不担心在检查访问权限之前必须加载对象的成本（即使加载对象非常昂贵，也应该是有人试图访问他不拥有的对象的罕见情况）。我认为，针对服务层方法的更强有力的论据正是代码的可重用性和模块性：具有公共接口的DAO类的存在表明它至少可能被多个组件重用。考虑到这样的合同是不可执行的，特别是要求所有这些组件都执行自己的访问检查似乎很愚蠢：没有办法确保有人在几年后决定使用你的DAO类，将记住这个要求（或阅读评论）。无论如何，如果要生成一个用于访问数据库的层，您也可以使它对某些内容有用。

#2

I use the second approach. I will go only from an architectural point of view which I feel is more important than a usually small cost of query.

我使用第二种方法。我只会从架构的角度来看，我认为这比通常很小的查询成本更重要。

Some reasons include:

一些原因包括：

1. It is cleaner to have all validations/verifications in a single place. The code has a structure. There will be cases when some validations can not be performed in the DAO layer. And then it becomes ad-hoc, what are the validations go into the service layer and what in the DAO layer.

   在一个地方进行所有验证/验证更清晰。代码有一个结构。有些情况下，无法在DAO层中执行某些验证。然后它变得临时，进入服务层的验证和DAO层中的内容是什么。

2. The method findCalById should only return Calendar by id using an id. It is more reusable. What if tomorrow you need a functionality that an admin can see all the calendars irrespective of the owner. You will end up writing another query for this feature. It will be more easy to add this check in a service layer.

   方法findCalById应该只使用id通过id返回Calendar。它更可重复使用。如果明天你需要一个管理员可以看到所有日历而不管所有者的功能。您将最终为此功能编写另一个查询。在服务层中添加此检查会更容易。

3. Assuming someday you have another data store which returns the record, then you end up having validations in multiple places. It will not happen if there is a service layer to do the validations. The service layer will not change as it will not care from where the record comes.

   假设有一天你有另一个数据存储区返回记录，那么你最终会在多个地方进行验证。如果有服务层来进行验证，则不会发生这种情况。服务层不会改变，因为它不关心记录的来源。

4. Onboarding a new colleague becomes easier. Assuming a new guy who specializes in the DB domain starts to work with you. He will be more productive if he is only concerned with the records that the DB should return forgetting about how the application uses this data. (separation of concern applies in real life too :)).

   入职新同事变得更容易。假设一个专门研究数据库域的新人开始与你合作。如果他只关心数据库应该返回忘记应用程序如何使用这些数据的记录，那么他的工作效率会更高。 （关注点的分离也适用于现实生活:)）。

#3

You have raised a very interesting question here. As others have already emphasized, correct approach depends on the meaning of your question.

你在这里提出了一个非常有趣的问题。正如其他人已经强调的那样，正确的方法取决于你问题的含义。

how to distinguish between a calendar that does not exist and an existing calendar that cannot be accessed by the current user?

如何区分不存在的日历和当前用户无法访问的现有日历？

When you start thinking about implementing the filtering logic in DAO vs in Services, you effectively start solving the multi-tenancy problem, which probably is already solved. This sounds like multi-tenancy because you think how to isolate the data between different users that use the same application. The problem is narrowed down since you want to have the shared database - that's a given (cannot change). You also mention that data is not always isolated (admin can see what everybody else see in their isolation). If so, then the implementation place is actually irrelevant, and for each use case you can have a different choice - whichever makes sense for that specific use case. It would matter if you didn't have mixed use cases though, but you do have (admin vs regular user). So, your multi-tenancy is not that complex - it's just use-case-based.

当您开始考虑在DAO和服务中实现过滤逻辑时，您有效地开始解决可能已经解决的多租户问题。这听起来像多租户，因为您认为如何在使用相同应用程序的不同用户之间隔离数据。由于您希望拥有共享数据库（这是给定的（无法更改）），因此问题会缩小。您还提到数据并不总是孤立的（管理员可以看到其他人在隔离中看到的内容）。如果是这样，那么实现位置实际上是无关紧要的，并且对于每个用例，您可以有不同的选择 - 对于该特定用例是有意义的。如果你没有混合用例，那就很重要了，但你确实有（管理员与常规用户）。因此，您的多租户并不复杂 - 它只是基于用例。

One thing that I see going awkward in this conversation is - you consider your database in separation from your application, which actually defeats the purpose of the database. Your database is an application's database. I also saw signs of considering your data access logic in separation from other layers, which actually makes your data access a different application, but it's not - all layers together form your application. It's this holistic view of the application that makes the implementation place irrelevant [in this very specific case].

我认为在这次谈话中尴尬的一件事是 - 你认为你的数据库与你的应用程序分离，这实际上违背了数据库的目的。您的数据库是应用程序的数据库。我还看到了将数据访问逻辑与其他层分离的迹象，这实际上使您的数据访问不同的应用程序，但事实并非如此 - 所有层都组成了您的应用程序。正是这种应用程序的整体视图使得实现与[在这个非常具体的情况下]无关。

Here is how I see this for several use cases that can exist in your application:

以下是我在您的应用程序中可能存在的几个用例的看法：

• User is authenticated, which decides access rights for calendars per authenticated user (it can be either "View only user's calendars", or it can be "View all calendars").
• 用户经过身份验证，决定每个经过身份验证的用户的日历访问权限（可以是“仅查看用户的日历”，也可以是“查看所有日历”）。
• User accesses screen for viewing only user's calendars - user's calendars are displayed. If you think Admin should also use this same screen, then you can either map different service for admin, or call different DAO method for admin, or pass different parameters to DAO for admin.
• 用户访问屏幕仅查看用户的日历 - 显示用户的日历。如果您认为Admin也应该使用相同的屏幕，那么您可以为admin映射不同的服务，或者为admin调用不同的DAO方法，或者将不同的参数传递给DAO for admin。
• User accesses screen for viewing all calendars (well, this page probably is protected for only Admin's access). Then display all calendars. If you think regular user should also use this same screen, then you can either map different service for user, or call different DAO method for user, or pass different parameters to DAO for user.
• 用户访问屏幕以查看所有日历（嗯，此页面可能仅受管理员访问的保护）。然后显示所有日历。如果你认为普通用户也应该使用同一个屏幕，那么你既可以为用户映射不同的服务，也可以为用户调用不同的DAO方法，或者为用户传递不同的DAO参数。

#4

Filtering result on DAO-level is good approach for a few reasons:

在DAO级别上过滤结果是一种很好的方法，原因如下：

1. you save resources on application server
2. 您在应用程序服务器上节省资源
3. filtering on database level is faster
4. 在数据库级别上过滤更快
5. filtering as soon as possible reduce bug risk on higher app layers
6. 尽快过滤可以降低较高app层的bug风险

how to distinguish between a calendar that does not exist and an existing calendar that cannot be accessed by the current user?

如何区分不存在的日历和当前用户无法访问的现有日历？

For security reasons you should't show unaccessible object at all, but sometimes usability is more imortant. Distinguish possibility should depend on your app specific.

出于安全原因，您根本不应该显示无法访问的对象，但有时可用性更重要。区分可能性应取决于您的应用程序特定。

#5

Filtering on the Data Access Layer would be my choice. Normaly i separate my access to the database in a separate class Library called DAL. Inside that Library, i define an Interface with the methods that return the data. When you create an instance of that Interface, there will be a constructor that will have a user parameter. So the interface will filter data for you without having to pass user info on every method.

我可以选择过滤数据访问层。 Normaly我在一个名为DAL的单独的类库中分离了对数据库的访问。在该库中，我使用返回数据的方法定义了一个接口。当您创建该接口的实例时，将会有一个具有用户参数的构造函数。因此，界面将为您过滤数据，而无需在每个方法上传递用户信息。

public class DatabaseInterface {
    private UserIdentity UserInfo;
    private Database Data;

    public DatabaseInterface(UserIdentity user) {
        UserInfo = user;
        Data = new Database();
    }

    public List<cal> findCalById(int id) {
        return Data.cal.Where(x => x.user == this.UserInfo && x.id == id).ToList();
    }
}

Usage of the interface

使用界面

var dal = new DatabaseInterface(user);
var myData = dal.findCalById(1);

#6

Filtering the result in DAO layer is preferred to me.

在DAO层中过滤结果对我来说是首选。

As a way to reduce parameter list, since the calendar is retrieved from owner point of view, there is no need to pass in calendar ID. Instead of doing: def findCalById(id: Int, ownerId: Int ): Future[Option[Calendar]], I will do:def findCal(ownerId: Int): Future[Option[Calendar]].

作为减少参数列表的一种方法，由于从所有者的角度检索日历，因此不需要传递日历ID。而不是：def findCalById（id：Int，ownerId：Int）：Future [Option [Calendar]]，我将这样做：def findCal（ownerId：Int）：Future [Option [Calendar]]。

Regarding:

关于：

How to distinguish between a calendar that does not exist and an existing calendar that cannot be accessed by the current user?

如何区分不存在的日历和当前用户无法访问的现有日历？

With the def findCal(ownerId: Int): Future[Option[Calendar]] method, you don't even need to differentiate the two cases. Because from user/owner point of view, DAO just need to return the calendar if it is present.

使用def findCal（ownerId：Int）：Future [Option [Calendar]]方法，您甚至不需要区分这两种情况。因为从用户/所有者的角度来看，DAO只需返回日历即可。