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
, and 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:
Permission
PermissionID
PermissionName
Role
RoleID
RoleName
Role_Permission
PermissionID
RoleID
User_Role
UserID
RoleID
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:
User_Permission
(or should it be called PermissionException? PermissionOverride?)
UserID
PermissionID
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?