如何使用Hibernate过滤器或其他方式在spring数据jpa中实现行级安全性？ [英] how to implement row level security in spring data jpa using hibernate filter or other ways?
问题描述
信息软件中的一个非常重要的问题是具有不同角色，不同职责和访问级别的用户的存在。例如，想象一个具有如下结构（层次结构）的组织：
[组织角色] [组织ID]
首席执行官org01
财务助理org0101
人员1
软件助理org0102
人员2
商业助理org0103
人员3
想象一下，这个组织有一个管理人员信息的系统。在该系统中显示人员信息的规则是每个用户可以看到他有权访问的组织的人员信息;例如，'user1'可以访问'财务助理'和'商业助理'级别，因此他只能看到'人事1'和'人事3'的信息。同样，'user2'只能访问'Commercial Assistant'级别，所以他只能看到'personnel 3'的信息。因此，该系统中的每个用户都有一个特定的访问级别。
现在考虑一下，在这个系统中，每个用户只能看到登录后他可以访问的人员信息。因此，该系统的表结构如下所示：
[Organization]
id
code
名称
[员工]
id
first_name
last_name
organization_id
[User]
id
user_name
密码
[UserOrganization]
user_id
organization_id
以下查询足以获得适当的人员每位用户的信息结果：
选择*
来自员工e
其中e.organization_id在
中（从user_organization中选择uo.organization_id
uo
其中uo.user_id =：authenticatedUserId）
我们可以看到，下面的条件定义了显示正确数据的访问逻辑：
e.organization_id in
（sel ect uo.organization_id
$ b $ from user_organization uo
其中uo.user_id =：authenticatedUserId）
这种访问级别也被称为行级安全性（RLS）。
另一方面，相应的存储库类可能有一些负责读取数据的方法，所有这些方法都必须满足适当的访问级别条件。在这种情况下，访问级别条件将在某些地方（方法）重复。看来，使用'休眠过滤器'将是这个问题的一个合适的解决方案。唯一需要的是获取经过身份验证的用户的ID并在每个读取方法之前执行'enablefilter'命令的过滤器。
@Filter（{
@Filter（name =EmployeeAuthorize，condition =（organization_id in（select uo.organization_id from user_organization uo where uo.user_id =：authenticatedUserId）））
}）
$ / code> 现在问题是，所提出的解决方案是否正确？如果是，该方法如何在spring数据？
PS：鉴于我们不想依赖于数据库，数据库方面的实现不能成为候选解决方案，因此我们有义务在应用程序方面实现它（级别） 。
阿里，这是一个有趣的场景。
>两个问题您需要在这里回答。
第一个问题 - 暴露数据时，只是要过滤或者你会超越？
例如，如果您公开像 users / {id} 这样的操作 - 那么您需要检查授权 - 并确保用户有权访问该操作。如果您只是简单地公开像 / users 这样的操作，那么您只需要进行过滤，因为您只需公开当前用户有权查看的用户。
这种区分将决定很多实施。
第二个问题 - 您做了多少手动工作？或者，另一方面，您可以根据数据结构调整代码 - 并且手动执行更多操作。
这些是我首先关注的两个因素，在任何其他之前 - 因为根据这4个决定，实现看起来完全不同。
最后，回答你的can ACL scale问题 - 两个快速注释。
一个 - 你需要测试。是的，ACL可以扩展，但是可以扩展到10K或者100K，这不是一个可以在没有测试的情况下具体回答的问题。第二，当你测试的时候，考虑一下真实的场景。理解解决方案的局限性当然很重要。但是，除此之外，如果您认为您的系统将拥有1M个实体 - 非常棒。但是，如果它不会 - 那就不要制定这个目标。
希望有所帮助。
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
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)
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) ) ")
} )
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.
There are two questions you need to answer here.
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?
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.
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.
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.
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.
Hope that helps.
这篇关于如何使用Hibernate过滤器或其他方式在spring数据jpa中实现行级安全性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
