I'm building a rather complex web application with Java / Spring and at least 2 different databases:
- RDBMS for main data
- MongoDB for files (via GridFS) and other data CLOBs/JSON/etc.
The next step is authorization. Simple role based authorization isn't enough, because users should be allowed/disallowed to view/modify different resources. Though ACL came to my mind.
The most common simple ACL table probably looks like:
TABLE | FIELDS
-------+--------------
class | id, className
object | id, class_id, objectId
acl | id, object_id, user_id, permissionBitMask (crud)
But unfortunately that's not enough for my needs :(.
I also need:
- Roles:
- each User can have several roles, and
- an ACL entry can also belong to a role
- More Permissions:
- For example: each Project can have multiple tasks, but a User who can modify the project details isn't allowed to create new tasks for this project. So there must be a separate permission for that.
- ObjectId of different types:
- The RDBMS tables will use UUID surrogate keys (so at least I never have to deal with composite keys here)
- But MongoDB of course uses its own ObjectId
- Additionally I will have some static resources inside the code which must be access restricted as well.
- Parent Objects to inherit permissions
If I combine all these aspects, I get the following table structure:
TABLE | FIELDS
---------------+--------------
class | id, className
object | id, class_id, objectId, parent_object_id
acl | id, object_id, user_id, role_id
permission | id, permissionName
acl_permission | id, acl_id, permission_id, granted
Of course I could split the acl table into 2 tables (1. object + user, 2. object + role), but I don't think that really matters.
The "objectId" will be a simple VARCHAR and my application has to convert it from/to String. Else I'd have 5 additional tables for my different ObjectId types. And this would result in 5 additional JOIN operations...
Now the basic lookup query would be something like this:
SELECT p.granted
FROM acl a
JOIN acl_permission p
WHERE p.permission_id = ?
AND (
a.object_id = ? AND a.user_id = ?
OR a.object_id = ? AND a.role_id IN (?)
)
(Permissions are cached, Roles for current user are also cached via session context. granted just indicates, if the user has the permission or not.)
Then I would also have to apply an recursive SELECT, in order to get the parent object's ACL, if there's no ACL entry for the current object.
This can't be really performant. So what are the alternatives? My ideas:
- Different DB schema (any ideas!?)
- Graph Database like Neo4j.
Neo4j advantages:
- Finding the first parent with a permission entry is a simple task for this DB
- Storing an array of permissions within the ACL entry is possible
->no JOIN - Basically I could store all information in a single Node:
.
{
class: ClassName,
object: ObjectId,
parent: RelationToParentNode,
user: UserId,
role: RoleId,
grantedPermissions: [Permission1, Permission2, ...]
}
(Every permission, that is not listed inside the array, is automatically not granted. It's not possible to store complex types in a Neo4j array, so there's no way to store something like permissions: [{Permission1: true}, {Permission2: false}])
Of course it's also possible to store Permissions and Classes as separate Nodes and just link them all together. But I don't know what's the better approach with Neo4j.
Any ideas on this? Is there any out-of-the-box solution? Maybe there's a reason to use MongoDB for ACL?
I read about XACML and OAuth(2), but both seem to need an additional ACL schema to do what I need. Or am I wrong?