Need help designing an Access Control List (ACL) database structure

Question

I need help with a database design for an ACL (Access Control List) system.

Business rules

Let me explain the requirements:

• Any business object in the system will get an ACL that explicitelly defines who can access the object.
• Our system has groups of users and the ACL will specify which groups can access the object.
• Any user can be member in N groups.
• The user will not even see the objects if she is not included in the ACL.

Current considerations

There are tables that can hold hundred thousands of these business objects and thus I need a design that can quickly select the "visible" rows for them.

My idea is to add an ACL_ID to the tables with the business objects. Let's say I get a table for Orders and I need to control the accesibility:

CREATE TABLE Orders
(
    ID  int not null,

    ... lots of columns with relevant information

    ACL_ID  int not null
);

I add an ACL_ID column constrained as a foreign key into an AccessControlList table:

CREATE TABLE AccessControlList
(
    ACL_ID int not null,

    ... any needed information
);

Another table could hold the user groups that are included in the list:

CREATE TABLE AccessControlListItem
(
    ACL_ID int not null,
    USER_GROUP_ID not null
);

The AccessControlList defines which groups of users can access the elements. If I had 100 different groups I could define that users from 3 groups can access the row.

As there are potentially millions of business objects I thought that probably would be better to just create AccessControlLists rows for the different combinations. For example, if the user groups "A", "B" and "C" are included in a list with ID = 125 I could reuse this ACL for any business object with the same combination. If someone adds a group "D" to the ACL I would need to create a new list with a new ID. The ACLs would be then "lazy-created": everytime I need a new combination I create the new list. Of course I will need a "garbage collector" to eliminate lists that are not used anymore or I leave them as they could be created later on.

Ok, now I reduced the number of elements in the AccessControlList table from millions of objects to maybe some thousands. In any case I believe that the design will be very expensive for queries. A query for orders without any other filter than the ACL could be something like this:

select <columns> from Orders where ACL_ID IN (
      select ACL_ID from AccessControlList where USER_GROUP_ID IN (
           <select the groups for the current user>)
)

The user could potentially be a member in 100 different ACLs.

The question

Any experiences with a similar design? Any comments about some other way of implementing it? I would be very grateful for any ideas or comments. Thank you very much.

Answer 1

I favor computing and materializing granular object access entitlements so they are simple and cheap for the database to enforce at runtime. And to simplify the relational model.

Order(OrderId, ...)
Order_AccessControl(OrderId, GroupID, AccessType)

Every time you create a group or an order you would generate the AccessControl entries using (potentially complex) business logic and configuration data, but storing, auditing and enforcing the security is simple.