Java – efficient, database-aware instance-level authorization?

Question

In a JPA app I have a scenario in which the app is to

list all accounts the given user is authorized to withdraw from

I have the Account entity and a many-to-many table that lists what authorizations each user has on each account – to implement the above scenario, the app currently just inner-joins the two tables – which is quite quick.

Now, I was planning to add an explicit authorization layer (based on apache shiro / spring security / other) to insulate authorization-related logic from the rest of the code, but...

There are some 10k Accounts in the database and the "average" user is granted "deposit" on all of them, "view" on one half of them and "withraw" on just a few.

Does any security framework allow to implement this scenario efficiently?

Ie: is any of them able to "decorate" a JPA query of the type "select a from Account a" (or the equivalent SQL) and thus get the list of accounts without loading all user grants from the database, and by all means, without having to retrieve all accounts?)

...and you don't have a User entity that is associated to the authorizations and can just "SELECT * FROM Account WHERE Account.userAuths.user = user"? — Visionary Software Solutions
Yep - that's what the app does right now. I'm wondering if any auth framework allows me to remove that authorization-related join from the code. — giorgiga
Can a NamedNativeQuery query that return Account objects like 'select acc.* from account acc, user_auth uauth where uauth.user_id=:login_userid` help? — Ahamed Mustafa M
Thanks, but... no :( That's once again authorization-related code, and I'd rather not have it in the place where accounts are retrieved. Suppose at some point I have to add "root" user, who is allowed to withdraw from all accounts no matter what - with that join in place I'll have to update the query (or - arguably better - I'd have to make sure that USER_AUTH table has one entry for "root" and each account) — giorgiga

Jeff Jeff · Accepted Answer · 2012-09-18T15:25:34

Have a look at Apache Shiro.

It allows you to pull in the User authorization once and cache it for the duration of the session. In addition, if all users can VIEW all ACCOUNTS then you wouldn't need to explicitly define this which would significantly reduce the overhead.

If your solution requires realtime access handlers Shiro has a way to reset the Permissions dynamically during runtime too.

Shiro allows you to implement a typical RBAC and define permissions like this:

domain:action:instance

So in your case permissions might look like this for a user:

account:deposit:*  // deposit all accounts
account:view:1111
account:view:2222
account:view:3333 // view on these accounts
account:withdraw:5555
account:withdraw:6666  // withdraw on these accounts

In code you can then do something like this:

if (SecurityUtils.getSubject().isPermitted("account:withdraw:"+account.getAccountNumber() ) {
  // handle withdraw
}

Shiro also has annotation driven permissions for additional abstraction.

EDIT

The Shiro permissions is the end result, not where you start. I used a set of tables representing mappings of the user-to-role and role-to-permission along with other mappings to instance. After AuthN its usually a simple set of queries indexed by the User PK to build up the data structures needed to render the permissions.

Java – efficient, database-aware instance-level authorization?

3 Answers