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:

    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 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.

  • 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, 2018 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 learn.microsoft.com/en-us/sql/relational-databases/security/… Jan 12, 2018 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, 2018 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. Jan 17, 2018 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, 2018 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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