db level ACL filtering

Question

I am looking at Spring-security 3.0 for this, spring's ACL filtering happens as post(api call) operation. There are 2 issues with that :-

1. it will break paginated query
2. Even if i take pagination out on layer above the api fetching results( i am using spring-hibernate here) , the db query each time is wasteful as it fetches and populates all results even if most of them are destined to be filtered out at java level

I have seen solutions where each query is appended with the acl queries which does the filtering at the db level , but that looks ugly as it pollutes business logic with authorization concern, are there any ways/frameworks that does db-level acl filtering transparently ? I like spring-securities overall approach of enforcing security declaratively through config/annotations thus sparing the code from security related logic directly, but i think it loses out on this on performance concerns

Answer 1

For the issues that you mentioned , only the #1 one is the real issue to me .

For the #2 issue if I understand correctly , it is the query that return a result list without any pagination behaviour. Because of that , it is supposed to assume that size of the result is finite and will not grow to the point that it will become very slow to return the result . Otherwise you need to make this query to be pageable and go back to the #1 issue. Given the finite result list , I doubt that filtering at the application level using @PostFilter will become noticeably slower than filtering at the database level.

I have seen solutions where each query is appended with the acl queries which does the filtering at the db level , but that looks ugly as it pollutes business logic with authorization concern, are there any ways/frameworks that does db-level acl filtering transparently ? I like spring-securities overall approach of enforcing security declaratively through config/annotations thus sparing the code from security related logic directly,

So for the #1 issue , if you are using Hibernate , you can check out @Filter which allows you to declaratively define a where clause that will be appended to the select SQL when querying certain entity. The filter is by default turned off and required to be enabled per transaction .The where clause can also be parameterised .

That means you can simply use Spring AOP to define an annotation to annotate the query method that you want to enable the authorization .Then in the advice that backed by this annotation , turn on this filter and configure the parameters for the where clause based on the current user information if necessary. For the query method that is not annotated with this annotation , the filter is turned off and not aware of the authorization concern.

Basically it is the same as appending the authorization logic to the query , but with the help of AOP and the nature of the @Filter , the business logic is not aware of any authorization logic.

If Hibernate filter is not suitable for your requirements, you can then look into which data access technologies allow you to modify the query easily by adding the authorization logic to it. For example , using JPA Criteria API is also possible as it provides the object model to represent a query ,and hence adding the authorization logic to the query is just equivalent to tweaking the query object.

The idea is that you need to have a proper design of the data access layer such that you can use AOP to configure the underlying technology to apply the authorization concern easily and in a consistent way. And use AOP to separate the authorization logic from the business logic.