So, I have the following database design for Users, Roles & Permissions.
Users * ---- * Roles
Roles * ---- * Permissions
As you see User may be in many roles. Role has many permissions. It has one downside though: What if I want to give a particular special Usersome specific permission, which is not in user's existing roles? Imagine I have role "manager". John Doe is in manager. Now, I want to give John a permission "See Activity". "See Activity" is in "Observer" role but not in the "manager". Although I want to let John "See Activity" I don't want to give him "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.
Any other database design/ideas? Suggestions? Thanks
2 Answers
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!
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.
