Performant ACL schema for complex application (RDBMS, Graph Database?)

Question

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?

Answer 1

First of all, the complex Permission System you're looking for has a standard spec called RBAC (Role-Based Access Control). I've implemented various RBAC models in SQL both simple and complex. Work fine, SQL implementation is not fast on commodity hardware when the number of relationships grow above million. Reads are instant, but writes are slow due to the heavy work to duplicate records in order to provide fast reads.

Originally, when I designed the Permission System I literally "drew" it on a paper based on the RBAC spec. The output was, indeed, a graph. So, after two years of production usage, I'm thinking of switching to a native graph database.

Neof4j is a popular solution, but some important customers seem to be dissatisfied with it due to its weak clustering and replications system. So have a look at OrientDB (see OrientDB vs Neo4j).

You have mentioned above that "it's not possible to store complex types in a Neo4j array". OrientDB boasts having addressed this issue with custom data types. I haven't personally tried it yet, but planning to test after migrating our production data.