Design an extensible permissions model in database

Question

I’m trying to model a system with Users, Permissions and Products. The main goal is to have a way of checking if a User has a specific Permission in order to allow or deny other system operations.

So Users will have a list of Permissions and this Permissions will be given to the User when the User buys a specific Product.

This way the Users will have Permissions but the Products must have something like a “template” of the Permission that the User that buys the product will get.

This structure could be simplified to users having products and products having permissions but one of the requirements is to have the possibility of assigning a specific permission to a specific user without needing a product. And also each permission has its own context so, each permission has its own relation with a user and some other variables that mutate over time and can change the permission validity.

I’m trying to map this domain to a database structure but I’m stuck with the relation between permissions and products.

Thanks

Answer 1

I'm under 50 points so I cannot comment, but I can answer. If I understood alright, you have:

A Product has permissions associated to it. A Person that buys a product will have all the permissions associated to that product. A Person may have another permissions besides the permissions "automatically" given by buying a product.

So lets say: ProductA has permissions PerA1 and PerA2. ProductB has permissions PerB1 and PerB2.

Now lets say a UserA buys the product ProductA. Then this person will have permissions: PerA1 and PerA2. And because of some reason,the system wants to assign UserA also de permission PerB1 (with no need of buying ProductB). So at the end:

UserA has PerA1, PerA2 and PerB1.

Is this right? If this is the case, You will have 3 tables for users, products and permissions. And you also need two more tables:

*) One for associating Users with Permissions i.e UserPermission with fields: ID, UserID, PermissionID *) Another one for associating Products with Permissions i.e ProductPermission with fields: ID, ProductID, PermissionID

Then, when you say: And also each permission has its own context so, each permission has its own relation with a user and some other variables that mutate over time and can change the permission validity.

It is not very clear to me. But I suppose you may add this "context" (whatever it is) in the relationship between Users and Permissions i.e in the Table UserPermission