How to simply secure entities on the backend?

Question

I wonder if there are some good practices how to secure entities on the backend without fetching all the time the root entities.

Let's say you have an app (e.g. Spring Boot + MySQL) with the following entities:

• User: Each one could have many project or no one at all.
• Project: each one belongs to a User and could have many Item or no one at all.
• Item: each one belongs to a Project and could have many Comment or no one at all.
• Comment: each one belongs to an Item or no one at all.

Something classic like

User --> Project --> Item --> Comment

I wonder how to check efficiently if a user has the right e.g. to delete a Comment.

My first idea would be to join back up to Project and then check if the User has the right to modify this Project.

I also think about a big mapping table with each entities in order to prevent doing the 2 joins. But it seems a mess to keep in sync.

My third think is that a Document-oriented database could be a better bet than a classic MySQL/MariaDB.

So my question is, is there any concept or maybe concrete implementation/library/example how to solve this problem?

Answer 1

As usual, "it depends".

It seems you've simplified your requirement statement. Just to be clear:

• A comment belongs to exactly one item.
• An item belongs to exactly one project.
• A project belongs to exactly one user.
• A user has zero or more projects.
• A project has zero or more items.
• An item has zero or more comments.

The question you want to answer is: given an item, does a specific user have access to it based on the data structure above?

You don't specify the number of records, or the precise definition of "efficient", but I'll assume you're not talking about Facebook-levels of data, and "efficient" means "fast".

If that's the case, your first option - "join comment to item, item to project, project to user" is likely to be very fast - relational databases are really good at joins. It doesn't duplicate data, so maintenance is simple.

Option 2 - denormalizing into a mapping table - is almost certainly going to be unnecessary, and may well be slower as you have to maintain the mapping table which isn't free.

Option 3 would make sense; you could also look at a graph database.

However, there are also some standard models for managing permissions which may be more useful. As you mention Spring, Spring Security supports a rich permissions system. There are some alternatives too.