How to implement row-level security in Java?

Question

I am currently evaluating authentication / authorization frameworks.

Apache Shiro seems to be very nice but I am missing row-level security features.

E.g. there might be special rows in a database which should only visible and accessible by users with special privileges. To avoid unnecessary round-trips, we currently modify the SQL queries to join with our authorization data to get only the visible rows for the current user.

But this concepts doesn't feel 'right' to me, because we mix business code with security related code which should be orthogonal and independent from each other.

• What solutions are available/possible?
• How do you implement row-level security (especially in combination with jpa)?

UPDATE:

Target database is mostly Oracle 10g/11g
- but a database independent solution would be preferred if there are no big drawbacks

Answer 1

Row level security is really best done in the database itself. The database has to be told what your user context is when you grab a connection. That user is associated with one or more security groups. The database then automatically appends filters to user supplied queries to filter out what can't be seen from the security groups. This of course means that this is a per database-type solution.

Oracle has pretty good Row Level Security support, see http://www.orafusion.com/art_fgac.htm as an example.

Answer 2

We implemented it as JDBC wrapper. This wrapper simply parses and transforms SQL. Hibernate filter is good idea too but we have many reports and ad-hoc queries, Hibernate is not the only tool to access data in our applications. jsqlparser is an excellent open source SQL parser but we have to fork it to fix some issues and to add support of some advanced SQL features e.g. ROLLUP for reporting purposes https://github.com/jbaliuka/sql-analytic This reporting tool is also available on github but there is no dependency on row level security infrastructure https://github.com/jbaliuka/x4j-analytic