3

I have a system in development and part of the system is how permissions are handled, business logic rules for this are very complex so after lots of trials, we couldn't fit them directly in the database and I came up with the following model (the structure is very simplified of course):

Table [OperationType]
Id    | Name
--------------
1     | View
2     | Edit
3     | Delete
4     | Create

Table [Permission]
Id    | Name    | OperationType_Id    | Condition/Scope
--------------------------------------------------------

Table [Role]
Id    | Name
-------------

Table [Role_Permission]
Id    | Role_Id    | Permission_Id
----------------------------------

Table [User]
Id    | Name
-------------

Table [Entity]

Id    | Name
-------------

Permissions are basically View/Edit/Delete/Create entities with specific criteria The problem is that the permissions are usually very complex and cannot be translated directly to where conditions for select statements, so evaluating them for every request in case of millions of records in the database (especially for View/Edit/Delete permissions) is very expensive operation and takes a lot of times, so I came up with the following table to translate the business rules into:

Table [UserEntityOperationType]
Id    | User_Id    | Entity_Id    | OperationType_Id
-----------------------------------------------------

and this table is updated automatically by the back end with certain triggers (ex, new entity is created, user has been assigned a new role and such triggers)

I was told that this design is a bad design, so my questions are:

  1. Is this design model a known model? If yes, What is the name of it? and any readings about it are much appreciated.
  2. What are the pros and cons of this design mode?
  3. If this design is really bad and have lots of cons vs pros, what are the alternatives of designing a system with such complicated business rules?

Edit:

To make things more clear, below are some examples of the system authorization requirements:

  • We have three object types (line [Entity] table above), let them be [Entity], [Project], [Object3]
  • Each of those object types is linked to the other two using link tables.
  • For each of those object types, we have a manager, and creator.
  • For each of those object types, we have parent-child relationship using [Parent_Id] column.

If there's a page that lists projects that the user can see, those are some conditions that projects must match any of them:

  • Projects where the user is creator.
  • Projects where an entity he is the manager of, is linked to.
  • Projects where an Object3 he is the manager of, is linked to.
  • Projects where any of the employees in the entities he manages, is the creator or manager.
  • Child projects of all projects he can see (Recursively).
  • 4
    If someone tells you it's a bad design, I would hope they also tell you why it is a bad design (or what part of it is bad design). If not, then their feedback isn't particularly useful. – Flater Jul 31 at 11:19
  • 4
    Also, this seems to be an inevitable logical consequence. If you want complex permission configuration on a per-action-type and per-entity basis, then you're obviously going to need to perform complex permission validation for every operation on every entity. That's cause and effect. If you want to avoid that, then first reevaluate if you can't simplify the permission logic itself. I can't judge that, I don't know the business requirements. – Flater Jul 31 at 11:27
5

Is there anything that forces you to handle complex business logic for authorization rules at the database level?

I would rather recommend to handle such business logic rules in the application code. Your database can contain information that tells you what roles a specific user has or what groups they belong to but fine-grained rules that reflect what a specific user is allowed to do in specific uses cases that have a business context (rather than only technical constraints) should be implement in your application from my point-of-view.

Despite of being harder to implement (as you can see in your case) it is often very difficult to understand the business logic from database constraints. This is much easier to understand and change from clean application code which also allows for easier testing e.g. via unit tests which can not be done that easily if the rules are manifested in the database.

You can have a look at a similar discussion here: https://stackoverflow.com/q/1473624/7730554

In addition I would recommend to look into Clean Architecture and Domain-driven Design.

Update

After additional discussions (see comments) I'd like to add that the data model seems to be okay to fit the query (or read) requirements in order to receive some kind of list of entities a user is allowed to see along with the information what they are currently allowed to do with those entities.

Data structures of your code that is responsible for really applying the business operations should be structured to best support the business logic and should be more or less independent from the data model. The data model on the other hand should be structured in a way that persisting the information as well as querying or changing it can be done most efficiently.

Considering this I think your UserEntityOperationType table could be good fit for the storing and querying requirements.

After elaboarting on this, let's come back to your questions:

1. Is this design model a known model? If yes, What is the name of it? and any readings about it are much appreciated.

I think your current design comes closest to the (ACL) Access Control List approach which is used to apply access control on a per-object and per-user (or referring to your data model per-entity) basis.

See:

2. What are the pros and cons of this design mode?

I think the pros and cons of this approach are described quite well in the [documentation of the Ory Keto Permission server][1]:

Benefits:

  • Fine-grained control that can be fine-tuned per identity and permission.
  • Works really well in systems where each identity has a different set of permissions.

Shortcomings:

  • Works really well in systems where each identity has a different set of permissions.
  • As the number of identities and resources grows over time, the matrix becomes large and hard to maintain.
  • If many identities have the same permissions, choose a system like RBAC.

The mentioned term RBAC (Role-based access control is another approach which is widely used by most Identity and Access Management Platforms/Services.

See:

3. If this design is really bad and have lots of cons vs pros, what are the alternatives of designing a system with such complicated business rules?

In general, like in many other cases, you cannot say that ACL is good or bad per-se as it really depends on the use case. But it can be a valid approach for solving the problem where fine-grained per-object, per-user access control is required.

To decide if this is the best design for your project would go beyond the itention of a Q/A portal so it will be up to you to do some more research with these information and then choose an approach that you think will suit you best.

If I would have to choose I would at least first determine if something like RBAC would also fulfill my requirements to see how far the fine-grained access control on per-object (or per-entity) per-user basis has to go. I suggest to evaluate the pros and cons of RBAC as well (see https://www.ory.sh/keto/docs/engines/rbac).

You can also look into other alternatives to ACL and RBAC to evaluate what will suite you (see https://www.imperva.com/learn/data-security/role-based-access-control-rbac/).

But whatever approach you finally choose make sure that you carefully think about what data structures you need for performing the business operations vs. what you need for persisting and querying the information about who has what rights for accessing the business entities. [1]: https://www.ory.sh/keto/docs/engines/acl

Note: Getting access control and authorization right can become a demanding task and you might also want to look into existing third party solutions to make sure there is nothing that will already help you with solving your requirements.

| improve this answer | |
  • I completely agree with you, however in my case, the business rules are very complex to a level that makes a select statement takes about 1 minute to evaluate which records the user is allowed to see, that's why I searched for another approach to be able to immediately tell which records the user can see by linking the user to those records and joining in the select statement. – user2987021 Aug 2 at 10:11
  • Is it really about specific records or more about what kinds of data the a user should be able to see? Because if the latter is the case can't you apply those rules in the application code? For instance, first determine the role(s) or group(s) the user belongs to and based on that allow them to read/write specific types of data or more specifically from a business perspective allow them to perform the corresponding actions... – afh Aug 2 at 12:23
  • Or is more about querying all kinds of records a user shall be able to see (or display)? Or are there really requirements that demand that for each record (i.e. data object) you can define what users will be allowed to access it? I mean rather than based on types of data... – afh Aug 2 at 12:29
  • Yes, this statement is indeed the case "there really requirements that demand that for each record (i.e. data object) you can define what users will be allowed to access it", and I already made the implementation of evaluating those rules on the application (API) level and it worked fine, but in the case of millions of records, the "evaluation" hence telling which records match the conditions and thus the use is allowed to see takes minutes, so it doesn't scale very well @afh – user2987021 Aug 2 at 17:09
  • 1
    I tried to focus on your initial questions as well and updated the answer correspondingly. There is no yes or no to each question but I think it will point you in the right direction... – afh Aug 3 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.