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

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.

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.