how to implement row level security in spring data jpa using hibernate filter or other ways?

Question

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).

Venation

Imagine organization node count is 100,000 and want access all organization to admin user ? how can implement with ACL? when new node create in organization i will add access to all users. it dose not have good performance , imagine we have 10,000 user and how many record will save in access table ? – Venation

Answer 1

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?

On 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.

Hypoglycemia

Based on the fact that a single lookup produces a join over 4 tables I doubt that the default implementation will scale well in that particular case (though I might also be wrong). If keeping data in a 3rd normalized form is not required, you can basically also use a MongoDB appoach instead which creates one entry per domain object only and should therefore scale linearly. Sadly, I haven't got any real response from the Spring Security community if they are interested in integrating a MongoDB based ACL into their codebase – Blende

Hey @RomanVottner - quick note about MongoDB and ACL. In general, what I've seen works best in the Spring ecosystem is a community solution that, eventually gets integrated and adopted officially. If you're interested in working on that, the best way to go would be rolling that out. There's a chance of it eventually becoming official but even if it doesn't, it's still something the community can use. – Hypoglycemia

thanks , yes i want to authorize user for save and load . in save i can not handle that without acl or expression . but in load object , hibernate filter is ok .but when using ACl in this scenario my problem is in changing user_organization access and will add and remove ACL_ENTRY – Venation

when count Of organization are 100,000 and i have 200,000 user what is count of ACL_ENTRY ? 100,000*200,000? how can i handle that? – Venation

@aliakbarazizkhani depends on how many actual rules you have defined per ACL (~= organization). The users will be the SIDs in your ACL setups, while organizations will be the ObjectIdentity's the ACL is pointing to and the AccessControlEntry will define the actual permission for a specific user on the given domain object. So, unless you define rules for each user separately, the count should be way less. Though the join-table would probably accumulate still a lot of data before the filtering kicks in (but I'm not an SQL expert). Note also that permissions are inheritable – Blende

@Hypoglycemia i see ACL Code(JdbcMutableAclService.java) , when acl update, all entry remove and recreate . when i have 200,000 entry what happen when i want add new entry? is it ok? – Venation

Answer 2

With Spring you can use the following things:

1) You can use SpEL EvaluationContext extension that makes security properties and expressions available in SpEL expressions in the @Query annotations. This allows you to get only those business objects that relate to the current user:

interface SecureBusinessObjectRepository extends Repository<BusinessObject, Long> {

    @Query("select o from BusinessObject o where o.owner.emailAddress like ?#{hasRole('ROLE_ADMIN') ? '%' : principal.emailAddress}")
    List<BusinessObject> findBusinessObjectsForCurrentUser();
}

2) You can refer to Beans and use path variables in Web Security Expressions. This allows you to grant access only those objects which is allowed to the current user:

@Service
public class UserService {
    public boolean checkAccess(Authentication authentication, int id) {
        // ...
    }
}

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    // ...

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.authorizeRequests()
            .antMatchers("/businessObjects/{id}/**").access("@userService.checkAccess(authentication, #id)")
            // ...
    }
}

Check my demo project for more details. In this example Users can access Rooms if they belongs to categories related to these Users. Admin has access to all rooms.

Osmen

when your category count is 1,000,000 is your solution work ? – Venation