Spring Data JPA and spring-security: filter on database level (especially for paging)

Question

I'm trying to add method level security to my open source project using annotations and spring-security. The problem I'm now facing are findAll methods especially the ones for Paging (eg. returning a page).

Using @PostFilter works on Lists (but I personally believe its not a good idea to filter in application and not database) but completely fails on paging queries.

This is problematic because I have an Entity containing List<Compound>. There are different implementations of compound and a user might only have the privilege to read one of the Compounds. Compound uses TABLE_PER_CLASS inheritance. Repositories implement QueryDslPredicateExecutor.

My thinking is to add a predicate to each query that limits the return results based on current user. However I'm kind of lost on a) how the data model for user and roles should look and b) how to then create the predicate (this is probably easy once the model is defined). Or does querydsl already offer type based filtering (on elements contained in the queried class)?

Answer 1

For the time being a came up with following solution. Since my project is rather simple this might not work for a more complex project.

1. a user can either read all or none of the entities of a certain class

hence any query method can be annotated with @PreAuthorize containing hasRole.

The exception to this is the Container entity in my project. It can contain any subclass of Compound and a user might not have the privilege to view all of them. They must be filter.

For that I created a User and Role entity. Compound has a OneToOne relation to Role and the that role is the "read_role" for that Compound. User and Role have a ManyToMany relationship.

@Entity
public abstract class Compound {    
    //...
    @OneToOne    
    private Role readRole;
    //...   
}

All my repositories implement QueryDSLPredicateExecutor and that becomes very hand here. Instead of creating custom findBy-methods in the repository we create them in the service layer only and use repositry.findAll(predicate) and repository.findOne(predicate). The predicate holds the actual user input + the "security filter".

@PreAuthorize("hasRole('read_Container'")
public T getById(Long id) {        
    Predicate predicate = QCompoundContainer.compoundContainer.id.eq(id);
    predicate = addSecurityFilter(predicate);
    T container = getRepository().findOne(predicate);        
    return container;
}

private Predicate addSecurityFilter(Predicate predicate){        
    String userName = SecurityContextHolder.getContext().getAuthentication().getName();            
    predicate = QCompoundContainer.compoundContainer.compound.readRole
        .users.any().username.eq(userName).and(predicate);        
    return predicate;
}

Note: QCompoundContainer is the "meta-model" class generated by QueryDSL.

At last you probably need to initialize the QueryDSL path from Container to User:

@Entity
public abstract class CompoundContainer<T extends Compound> 
    //...
    @QueryInit("readRole.users") // INITIALIZE QUERY PATH
    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL,
            targetEntity=Compound.class)
    private T compound;
    //...
}

Omitting this last step can lead to a NullPointerException.

Further hint: CompoundService automatically sets role on save:

if (compound.getReadRole() == null) {
    Role role = roleRepository.findByRoleName("read_" + getCompoundClassSimpleName());
    if (role == null) {
        role = new Role("read_" + getCompoundClassSimpleName());
        role = roleRepository.save(role);
    }
    compound.setReadRole(role);
}
compound = getRepository().save(compound)

This works. The downside is a bit obvious. The same Role is associated with every single instance of the same Compound class implementation.