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?
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!