What is the best way to structure my web permissions tables?

Question

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?

Answer 1

You can think of security from 2 perspectives: Functional security and Data Security.

Functional Security: This will provide access control over various functionalities of your web-site. Simplest form of this will be as you mentioned, privileges which are mapped to single functionality such as user management or product management etc, aggregated to create application roles such as Product Manager, or HR Manager.

Data Security:

This will control what data(basically logical rows) should be available for each functional grants. For example, if you want to restrict Manage User functionality per department, then you may setup a per-dept-data-security role. This role, together with manage-user role provide the user with access to Manage user functionality within his department.

If you want to directly grant the privileges to users, it will definitely be messy, and kills the aggregation possibilities. But you can implement this, as roles will always be converted to privileges at run time. It will just create another layer unnecessarily, and also from usability perspective, more confusing to manage.