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?

migrated from stackoverflow.com Mar 17 '17 at 10:36

This question came from our site for professional and enthusiast programmers.

  • I'm sure you can do what you need to do with RBAC and a proper role design. When designing the roles, take a look to the Principle of the Least Privilege. Remember that Boolean algebra also allow you to do has(x) and !has(y) – Laiv May 21 '17 at 19:52

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.

  • 1
    It's worth mentioning that there are systems that do exactly this. Active Directory is probably the most widely used version that does. A user can be assigned a group which has permissions, or permissions can be directly applied or revoked for a given user. It's telling that EVERY best practice guide you'll find explicitly recommends only using roles and never doing direct assignment of permission to individual user. – Paul May 21 '17 at 11:36

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.