1

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.

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.

  • Thank you David! This is another idea to explore. For us, this means that we would need to create some hundred tables as we have got hundreds of different business objects types. How would you query it and what about perf? Like this? select <fields> from order where exists(select orderid from order_accesscontrol where orderid = order.orderid and groupid in (<select groups for user>)) – Raul Jan 12 '18 at 9:28
  • Having a single Object_ACL table shared by all tables with compatible keys would work too. As for query form, it depends on your platform. For SQL Server you can use Row Level Security docs.microsoft.com/en-us/sql/relational-databases/security/… – David Browne - Microsoft Jan 12 '18 at 14:03
  • I need a design that could work under MSSQL and Oracle. The security should be at the application level as the users are logical entities and not database users. This is a multi tier application where the business server connects to the database with just a few users (so that the connections can be pooled) – Raul Jan 17 '18 at 13:39
  • Typically you would store the real user in a session-level store, and join that in views. In SQL Server this can be a temporary table, the SESSION_CONTEXT or a regular table with a row for each session. – David Browne - Microsoft Jan 17 '18 at 14:56
  • Thank you again David!! I will look at it, do some tests and investigate if Oracle can do something like this – Raul Jan 18 '18 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.