• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Spring Data: Is there a good way to filter the database based on ACL permissions

 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm trying to build some row level security in my Spring application. I am looking at Spring ACL and basically, when you insert a record, you have to insert into the corresponding ACL record to specify who can access that row. Then when you are retrieving records from database, you retreive the records and then throw out the rows that the current user doesn't have access to.

This is fine, except that it's horribly inefficient, and messes up paging. If I have a screen on the UI that shows the first 100 records, I want to show the first 100 records, and not retreive 100, throw out 10 and show 90.

What I really want to do is to every query against the database, I want to add a clause that joins the table with the ACL table. All my database access is through Spring Data. So, I can tightly control the kind of queries running against the database. I can implement my own Repository that adds the criteria to every call. I was wondering is someone has already built something like this?
 
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Unfortunately no.

What I ended up doing is creating an @Entity for each of those 4 tables and annotated them with @Immutable.

So for example:



Then I created a Predicate class to hold a bunch of Query DSL predicates (many of them returning BooleanExpressions's). That way they could be reused. This code has been obfiscated but here is an example



Now I just used it in a repository, passing in the predicate that I had built up.


I am sure given time (which I did not have) something more elegant could have been done, but it did what I needed at the time. Of course that query would not necessarily be efficient for your use case, but you get the general idea. So short story long you have to do it yourself
 
Jayesh A Lalwani
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah, That's what I was going to build. Thanks. It helps to know there's nothing out there

Basically, I was planning to either a) implement my own JPA repository that extends SimpleJpaRepository and "injects" predicates into each call b ) wrap an interceptor around all SimpleJpaRepositorys that will "inject" the predicate; ie; intercept findAll() and call findAll(Specification) instead

I'm having trouble figuring out methods annotated with @Query. As far as I can tell you cannot combine Predicates with NamedQueries*. I'm trying to figure out if it works with other kinds of Queries.

*This is another thing that irks me.. Really, JPA? You have one way of querying a persistent store, and another way of querying a persistent store, and you can't use both of them together?
 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I feel your pain. I had the extra added headache of mixing Hibernate search into the mix to query a Lucene index. That was on top of all the ACL and other regular complexities. It would be nice if they all integrated a little better but such is life.
 
Greenhorn
Posts: 2
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If only jpasecurity would be complete at the moment. Sigh ... Very cool project but it hasn't picked up for some reason. Perhaps if it were hosted in github?
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Unfortunately, even after ~6 years we are struggling on the same issue. Apology for posting on old thread. Can Bill himself or somebody else share Repository+Service example which uses QuerydslPredicateExecutor code Bill shared.
 
reply
    Bookmark Topic Watch Topic
  • New Topic