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

Lenticular

What is your target database? – Cholecystitis

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.

Rootless

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

Berners

did you open sourced your jdbc wrapper? It looks to me like a "row-level-security jdbc wrapper" would be the best solution here. I have tried jpasecurity which looks like a great effort but its compliance with JPA spec is questionable. – Krak

I improved this library to support policies similar to postgres, it is a new code instead of my internal stuff for reporting but this new code should be more useful because it also supports DML. I am not sure I will have time to maintain it because all of major databases already support RLS internally (Postgres,Oracle,MS SQL Server) – Berners

Answer 3

There is a helpful article: http://mattfleming.com/node/243

The idea is that you can implement row level functionality in two ways: directly setting restrictions in your repository or binding the restrictions via AOP. The latter is preferred because security layer should be separated from business logic (orthogonal concerns).

In Hibernate you can use the concept of filters which are applied transparently and repository doesn't know about them. You can add such filters via AOP. The other way is intercepting session.createCriteria() and adding Restrictions to the Criteria transparently using AOP.

Hershel