I couldn't find a better title for this...
I am trying to create an Access Control for my application and I am having serious trouble with building not an insane system, mainly for performance as this system will reside in an SQL database and will be used excessively throughout the app (every query will be using it for fetching data)
So here is my scenario and my current design:
My system has 3 modules and each has the general CRUD actions - list, view, update, create, delete; and sometimes has more specific actions like - user.view.creator, user.update.photo, user.update.metadata, storage.upload, storage.download etc.
I want my system to have "tasks" or "operations" and they will be hierarchical, for example storage.view will have a child storage.download.
This is for the operations. This part I have no problem implementing and designing. My biggest problem is designing the connection between a user and the tasks. Both RBAC and ACL doesn't fit my needs in this case, so I am trying to build my own. I cannot implement RBAC because in RBAC the roles are static but in my case the roles themselves are dependent of the resource and the group that this resource belongs to. For example, Group A's role photographer can only upload a photo from the storage, while the Group B's photographer can also delete the photo from the storage. Therefore, to keep things somewhat simple, I decided to add a new resource GROUP and every all the files, roles etc must BELONG to some group. On the other hand, the user just gets mapped to a group; they never BELONG to a group, they just access resources in groups. This makes perfect sense and works seamlessly and is not very complex - the queries are simpler, so I have space to cluster the databases etc.
However, now here comes the problem that just doesn't fit in my current design. I have another resource, CLIENT. The problem is, in the one organization, a user can have different access to different clients. So for example, lets say I have Group A, that has Clients 1 to 25. A specific user that belongs to that group, can have something like:
Client 1 - Manager
Client 2 - Assistant
Client 3 - SomeOtherRole
And when he fetches a list of clients, the user will only see 3 clients. I really don't know what I should do to achieve this in the most performant way possible. My question is, how should I proceed with designing this part of my system? And a better question, how can I simplify this system?