title: Performant ACL schema for complex application (RDBMS, Graph Database?)
body: <p>I'm building a rather complex web application with Java / Spring and at least 2 different databases:</p>
  
  <ul>
  <li>RDBMS for main data</li>
  <li>MongoDB for files (via GridFS) and other data CLOBs/JSON/etc.</li>
  </ul>
  
  <p>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.</p>
  
  <p>The most common simple ACL table probably looks like:</p>
  
  <pre><code>TABLE  | FIELDS
  -------+--------------
  class  | id, className
  object | id, class_id, objectId
  acl    | id, object_id, user_id, permissionBitMask (crud)
  </code></pre>
  
  <p>But unfortunately that's not enough for my needs <code>:(</code>.</p>
  
  <p>I also need:</p>
  
  <ul>
  <li><strong>Roles</strong>:
  <ul>
  <li>each User can have several roles, and</li>
  <li>an ACL entry can also belong to a role</li>
  </ul></li>
  <li><strong>More Permissions</strong>:
  <ul>
  <li>For example: <em>each Project can have multiple tasks</em>, <strong>but</strong> 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.</li>
  </ul></li>
  <li><strong>ObjectId of different types</strong>:
  <ul>
  <li>The RDBMS tables will use UUID surrogate keys (so at least I never have to deal with composite keys here)</li>
  <li>But MongoDB of course uses its own ObjectId</li>
  <li>Additionally I will have some static resources inside the code which must be access restricted as well.</li>
  </ul></li>
  <li><strong>Parent Objects</strong> to inherit permissions</li>
  </ul>
  
  <p>If I combine all these aspects, I get the following table structure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Of course I could split the <code>acl</code> table into 2 tables (1. object + user, 2. object + role), but I don't think that really matters.</p>
  
  <p>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...</p>
  
  <p>Now the basic lookup query would be something like this:</p>
  
  <pre><code>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 (?)
        )
  </code></pre>
  
  <p>(Permissions are cached, Roles for current user are also cached via session context. <code>granted</code> just indicates, if the user has the permission or not.)</p>
  
  <p>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.</p>
  
  <p>This can't be really performant. So what are the alternatives? My ideas:</p>
  
  <ul>
  <li>Different DB schema (any ideas!?)</li>
  <li>Graph Database like Neo4j.</li>
  </ul>
  
  <p>Neo4j advantages:</p>
  
  <ul>
  <li>Finding the first parent with a permission entry is a simple task for this DB</li>
  <li>Storing an array of permissions within the ACL entry is possible <code>-&gt;</code> no JOIN</li>
  <li>Basically I could store <strong>all</strong> information in a single Node:</li>
  </ul>
  
  <p>.</p>
  
  <pre><code>{
    class: ClassName,
    object: ObjectId,
    parent: RelationToParentNode,
    user: UserId,
    role: RoleId,
    grantedPermissions: [Permission1, Permission2, ...]
  }
  </code></pre>
  
  <p>(Every permission, that is not listed inside the array, is automatically <em>not granted</em>. It's not possible to store complex types in a Neo4j array, so there's no way to store something like <code>permissions: [{Permission1: true}, {Permission2: false}]</code>)</p>
  
  <p>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.</p>
  
  <hr>
  
  <p>Any ideas on this? Is there any out-of-the-box solution? Maybe there's a reason to use MongoDB for ACL?</p>
  
  <p>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?</p>