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

Spitter

I am still with the same dilemma, so for the project at hand i am doing #2 for most scenarios other than a few where the number of entities will be very large for those i have no other option than to use hand written query like i mentioned. – Spitter

Cool ... I'm working on the something similar so I'll keep you posted if I found a magic solution – Mccary

Any update on this ? as I have the same issue with protected by ACL entities and pagination.... – Audiovisual

Give Oracle $10M and use VPD. Or wait for PostgreSQL 9.5 which will have Row Security – Chilpancingo

1.See if tweaking echcache integration in org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(List<ObjectIdentity>, List<Sid>) helps you there, you can implement your own lookup strategy and inject it for acl lookup. – Marleen

2.I experienced it while implementing acl authorization using 3.2. what we noticed that call goes to org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(List<ObjectIdentity>, List<Sid>) for most of the records which are passed to filters (pre/post) for authorization and thought of it as a unnecessary performance overhead. – Marleen

3. I customized it so that call to lookupObjectIdentities from readAclsById are kept at minimum and implementation of echcache is utilized for maximum fetch calls arising from org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(List<ObjectIdentity>, List<Sid>) or whatever will be applicable in your case. Though I won’t be able to provide you exact stat with respect to performance but seems it is improved a lot. – Marleen

Do you have any news about this? @Spitter - Did you finish the project? Did you decided to take the pagination out and use the PostFilter? When you say that for some cases you were forced to do manipulate the query, did you find a way to use the inheritance feature? – Argillite

see my answer on #19621076 – Siloam

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.

Tutankhamen