User Roles Permissions Database Design

Question

I have the following database design for Users, Roles & Permissions:
Users * ---- * Roles
Roles * ---- * Permissions

A user may have many roles. A role has many permissions. There is a downside though: What if I want to give a particular special user some specific permission that is not in that user's existing roles?

Imagine I have role "Manager". John Doe is a manager. I want to give John a permission "See Activity". "See Activity" is in the "Observer" role but not in "Manager". Although I want to let John "See Activity" I don't want to give him the "Observer" role, as it has other permissions too.

One way to solve it would be to have roles corresponding to each permission together with existing roles. And whenever John needs permission "See Activity", I will grant him role "See Activity Role" which contains only one permission.

What are other designs?

Answer 1

I believe you are thinking on the right track and it's a good design.

One recommendation is that since the db is nicely organized, try to have users get permissions only through roles. If it ever is the case that someone wants permission directly, just create a specialized role.

Like the example you mentioned in which John Manager doesn't have See Activity: This See Activity permission may be related to Observers but Observers has much more power to it than just See Activity. In that case, your design gives you the liberty to create a new role called ActivityObservers that has permission to See Activity. That way John Manager can be in both Manager and ActivityObservers role.

I am sure you are on top of indexing. Run some scenarios to answer questions like: What happens when John Manager leaves? How can you easily say which permissions John Manager had? Is there an Active flag in any table? Would it be useful to have one? Such active flag may help disable an account, role or permission quickly for every user.

Do you need an audit table to tell when and what permissions were given through roles to which user? If so, trigger based auditing might be helpful. Scenarios like - Jim Manager wants the exact same kinds of permission John Manager has - how quickly can this be done?

Overall, you have a good concept going!

Answer 2

It is a good idea to make a less role a subset of the bigger role. This can be easily determined by the SQL below

select count(permissions) from tblPermission where role='observer'

This will give the total number of privileges/permission that the role member has. Same query also for the manager's role

select count(permissions) from tblPermission where role='manager'

Logically if the manager role is a super-class to the observer class then the manager should be able to see activity as same with the observer.

Answer 3

What if I want to give a particular special Usersome specific permission, which is not in user's existing roles?

Your current design is RBAC (Role Based Access Control). You need ReBAC (Relationship Based Access Control), which allows for fine-grained permissions (e.g. user:x has a relation:y to object:z). See my other answer here: https://stackoverflow.com/a/71189433/1623249

Answer 4

It is easier and hassle free to work with bridge tables in relational databases.

So, here I make UserRol to store users with their specific role, that way you can have one user with just one rol or with many more. I have created a specific Permission table just for anything related to that, name, descriptions or whatever you can think of.
The table which stores all relationships, in which could be called a Permission Matrix Table, is the one I called Clearance, where it holds info about the Rol and its Permission. A rol could have many permissions. This way you could add easily new permissions, rols, and assign them to your convenience.