Effective database-level instance authorization?

Question

In a JPA application, I have a script in which the application is located

list all the accounts that this user has the right to log out

I have an Account object and a many-to-many table that lists the permissions that each user has for each account - to implement the scenario described above, the application currently only internally joins the two tables - this is pretty fast.

Now I planned to add an explicit authorization level (based on apache shiro / spring security / other) to isolate the authentication logic from the rest of the code, but ...

There are several 10 thousand accounts in the database, and the “average” user receives a “deposit” for all of them, “viewing” one half of them and “finding” only a few.

Does any security infrastructure provide an efficient implementation of this scenario?

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

Answer 1

Take a look at Apache Shiro .

It allows you to pull out user authorization once and cache it for the entire session. In addition, if all users can VIEW all ACCOUNTS, you do not need to explicitly define this, which will significantly reduce overhead.

If your solution requires real-time access handlers, Shiro has a way to reset Permissions dynamically at runtime.

Shiro allows you to implement a typical RBAC and define permissions as follows:

domain:action:instance 

So, in your case, user permissions might look like this:

 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 do something like this:

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

Ciro also has annotation-driven permissions for added abstraction.

EDIT

Shiro permissions are the end result, not the one where you start. I used a set of tables representing user-to-role mappings and roles to resolve along with other mappings for the instance. Following AuthN is usually a simple set of queries indexed by a user PK to create the data structures needed to grant permissions.

Answer 2

I have a hope that this is one of the possibilities to implement your requirement with Spring-Security.

• Write custom org.springframework.security.acls.Permission as ViewAccount , DepositToAccount , WithDrawFromAccount

• Write custom org.springframework.security.access.PermissionEvaluator Override hasPermission(Authentication userAuthentication,Object accountObject,Object oneOfThePermission) to check if the user has a specific permission on accountObject

• Get a link to the JPA EntityManager in your user evaluator and cross-check / check in the database with user_id, permission_id, account_id

• If the user is "root", you can staight away return true for hasPermission without checking with the database.

• Annotate your utility calls with @PreAuthorize("isAuthenticated() and hasPermission(#accountArgument, 'respectivePermission')")

Refer link for custom implementations of Permission and PermissionEvaluator

Answer 3

If you use EclipseLink, there are several functions for this,

one is the @AdditionalCriteria annotation, which allows you to apply a filter to all requests for a class,

http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_additionalcriteria.htm#additionalcriteria

another EclipseLink support for Oracle VPD (row-level security in the database),

http://wiki.eclipse.org/EclipseLink/Examples/JPA/Auditing

and finally, EclipseLink supports SessionEvents, which allows you to add a filter to the execution of any request,

http://www.eclipse.org/eclipselink/api/2.4/org/eclipse/persistence/sessions/SessionEventAdapter.html#preExecuteQuery%28org.eclipse.persistence.sessions.SessionEvent%29