如何使用休眠过滤器或其他方式在 Spring Data jpa 中实现行级安全性? [英] how to implement row level security in spring data jpa using hibernate filter or other ways?
问题描述
信息软件中非常重要的问题之一是存在具有不同职责和访问级别的不同角色的用户.例如，考虑一个具有如下结构(层次结构)的组织:
One of the very important problems in information softwares is the existence of users with different roles with different duties and access levels. For instance, think of an organization with the structure (hierarchy) like below:
[Organization Role ] [Organization ID]
CEO org01
Financial Assistant org0101
personnel 1
Software Assistant org0102
personnel 2
Commercial Assistant org0103
personnel 3
想象一下，这个组织有一个管理人员信息的系统.本系统显示人员信息的规则是每个用户都可以看到他所访问的组织的人员信息；例如，‘user1’可以访问‘财务助理’和‘商务助理’级别，所以他只能看到‘人员1’和‘人员3’的信息.同样，‘user2’只有‘商务助理’级别，所以只能看到‘人员3’的信息.因此，该系统中的每个用户都有特定的访问级别.现在考虑在这个系统中，每个用户在登录后只能看到他可以访问的人员信息.那么这个系统的表结构是这样的:
Imagine that this organization has a system that manages personnel’s information. The rule of showing personnel’s information in this system is that each user can see personnel’s information of the organizations that he has access to; For example, ‘user1’ has access to ‘Financial Assistant’ and ‘Commercial Assistant’ levels, so he can only see information of ‘personnel 1’ and ‘personnel 3’. Likewise, ‘user2’ only has access to ‘Commercial Assistant’ level, so he can only see information of ‘personnel 3’. Therefore, each of the users in this system has a specific access level. Now consider that in this system, each user only sees the personnel information that he has access to after he logs in. Having that the table structure of this system is like this:
[Organization]
id
code
name
[Employee]
id
first_name
last_name
organization_id
[User]
id
user_name
password
[UserOrganization]
user_id
organization_id
以下查询足以为每个用户获取正确的人员信息结果:
the below query would be enough to get the proper personnel information results for each user:
select *
from employee e
where e.organization_id in
(select uo.organization_id
from user_organization uo
where uo.user_id=:authenticatedUserId)
如我们所见，以下条件定义了显示正确数据的访问逻辑:
as we can see, the below condition defines the access logic for showing the right data:
e.organization_id in
(select uo.organization_id
from user_organization uo
where uo.user_id=:authenticatedUserId)
这种访问级别也称为行级别安全性"(RLS).另一方面，相应的存储库类可能有几个负责读取数据的方法，所有这些方法都必须满足适当的访问级别条件.在这种情况下，访问级别条件将在某些地方(方法)重复.似乎使用休眠过滤器"将是解决此问题的正确方法.唯一需要的是一个过滤器，它获取经过身份验证的用户的 id，并在每个读取方法之前执行enablefilter"命令.
This kind of access level is also known as ‘Row Level Security’ (RLS). On the other hand, the corresponding repository class, probably has a couple of methods responsible for reading the data, all of which has to fulfill the proper access level condition. In this case the access level condition will be repeated in some places (methods). It seems that using a ‘hibernate filter’ would be a proper solution for this problem. The only thing needed is a filter that gets the id of the authenticated user and executes the ‘enablefilter’ command before every read method.
@Filters( {
@Filter(name="EmployeeAuthorize", condition="(organization_id in (select uo.organization_id from user_organization uo where uo.user_id=:authenticatedUserId) ) ")
} )
现在的问题是，建议的解决方案是否正确?如果是，如何在spring数据中使用这种方法?PS:鉴于我们不想依赖数据库，所以在数据库端的实现不可能是一个候选的解决方案，因此我们有义务在应用端(级别)实现它.
Now the question is that, is the proposed solution right? If yes, how can this method be utilized in spring data? PS: Given that we don’t want to be dependent on databases, implementation on the database side cannot be a candidate solution, for this reason we’re obliged to implement it on the application side (level).
推荐答案
阿里，这是一个有趣的场景.
Ali, That's an interesting scenario.
您需要在此处回答两个问题.
第一个问题 - 公开数据时，系统是只进行过滤还是您会超出此范围?例如，如果您公开像 users/{id} 这样的操作 - 那么您需要检查授权 - 并确保用户有权访问该操作.如果您只是公开像 /users 这样的操作 - 那么您所需要的就是过滤，因为您只需公开当前用户有权查看的用户.这种区别将决定很多实施.
The first question - when exposing the data, is the system just going to do filtering or will you go beyond that? For example, if you expose an operation like users/{id} - then you need to check authorization - and make sure the user has access to that operation. If you simply expose an operation like /users - then all you need is filtering, because you'll simply expose the users that the current user is authorized to see. That distinction will determine a lot of the implementation.
第二个问题是 - 你可以做多少体力活?
The second question is - how much manual work are you OK with doing?
一方面，您可以根据框架的需要调整数据 - 并尝试尽可能多地依赖内置功能(安全表达式、ACL).或者，另一方面，您可以使代码适应您的数据结构 - 并且更多地手动执行操作.
One the one hand, you could adapt the data to what the framework needs - and try to rely as much as possible on the built-in functionality (security expressions, ACL). Or, on the other hand, you could adapt the code to the structure of your data - and do things more manually.
这是我首先要关注的两个因素——因为根据这 4 个决定，实施看起来会完全不同.
Those are the two factors I'd focus on first, before anything else - because the implementation will look entirely different based on those 4 decisions.
最后，回答您的ACL 可以扩展"问题 - 两个简短的说明.一 - 你需要测试.是的，ACL 可以扩展，但它是否可以扩展到 10K 或 100K，这不是一个无需测试就可以具体回答的问题.
Finally, to answer your "can ACL scale" question - two quick notes. One - you need to test. Yes, ACL can scale, but can it scale to 10K or to 100K isn't a question that can be answered concretely, without testing.
其次，当您进行测试时，请考虑现实场景.了解解决方案的局限性当然很重要.但是，除此之外，如果您认为您的系统将拥有 100 万个实体，那就太好了.但如果它不会——那么就不要把它作为目标.
And second, when you do test, think through realistic scenarios. It's certainly important to understand the limits of your solution. But, beyond that, if you think your system will have 1M entities - great. But if it won't - then don't make that a goal.
希望对您有所帮助.
这篇关于如何使用休眠过滤器或其他方式在 Spring Data jpa 中实现行级安全性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
