I need help with a database design for an ACL (Access Control List) system.
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.
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
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 );
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.
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.