I want to set up basic "permissions" for a website. It has a basic "roles" system where users are part of certain groups and get all the permissions allowed to that group. However, I need more than just a basic
Role_Permission setup because I want to allow for specific exceptions - certain users may have access to extra permissions (even if not granted to any of their "roles") and certain users may be denied specific permissions even if their "role" has access to them. Basically, I want it to be completely flexible and customizable but still abstracted and reusable.
This is the basic design of the tables:
Now I need a way to allow "override" so a specific user can be allowed/denied a specific permission. Should I create 2 separate tables, one for denied permissions and one for allowed? Or should I create a single User_Permission table? If so, should it have 2 separate flags for allow/deny or a single field? I'm thinking of having something like this:
(or should it be called PermissionException? PermissionOverride?)
Allow (bit flag)
Deny (bit flag)
(I then plan to write a SQL stored procedure
hasPermission(UserID, PermissionID) that would be called to determine whether this user has permission to perform an action.)
Is this a good design? Is there any way it can be improved upon? What is the general standard used for implementing this common design pattern?