Best Role-Based Access Control (RBAC) database model [closed]

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

What is the best database schema to track role-based access controls for a web application? I am using Rails, but the RBAC plugin linked by Google looks unmaintained (only 300 commits to SVN; latest was almost a year ago). The concept is simple enough to implement from scratch, yet complex and important enough that it's worth getting right. So how do others architect and implement their RBAC model?

Use a framework that implements authorization for you. Look into CanCanCan or other Attribute Based Access Control (ABAC) models e.g. XACML

10 Answers 10

To my rather basic knowledge in that area, the basic actors of an RBAC are:

• Resources.
• Permissions.
• Users.
• Roles (i.e. Groups).

Resources (one or many) Permissions.

Roles (one or many) Permissions.

Users (one or many) Roles.

The tables for such a model would be:

• permission
• role
• user
• role_permission
• user_role

Now you might want to include resources here as well if you want users of your application to be able to configure which permissions a resource need. But I never needed that. Hope that helps.

When you say that 'Resources require (one or many) permissions', I'm assuming that this is for the UI of that resource, which allows a user to set allow/deny values to each permission for each user. correct? If so, then in your DB schema, you may have 10, 20 (or more) possible resources associated with a single permission. For example, a 'Project Manager' has a 'Create' permission. Then you would link such permission with a Project, Schedule, Task, Document, Timesheet resources (just to name a few), because a Project Manager may create all those resources? Thanks!

You might want to consider AERBAC researchgate.net/profile/D_Kuhn2/publication/… "RBAC and ABAC have their particular advantages and disadvantages. RBAC trades up-front role structuring effort for ease of administration and user permission review, while ABAC makes the reverse trade-off: it is easy to set up, but analyzing or changing user permissions can be problematic."

Here is a simple diagram to illustrate Amr Mostafa's excellent answer

You don't have to use the UserRoleID and the RolePermissionID. Instead of that in User_Role, the combination of UserID and RoleID should be unique and the primary key. This is the same for the Role_Permission table with RoleID and PermissionID.

This diagram is wrong. User one-to-many User_Role many-to-one Role one-to-many Role_Permission many-to-one Permission . and Resource entity is omitted.

I happen to be working on the RBAC sub-system here at work at them moment. what a coincidence.

My model is based on the building blocks of the different entities in the system that require permissions, be they attributes to view/update or actions to perform. There are also, of course, different roles in the system (which can be given to users), and the glue that holds the whole thing together is the access rule, which connects a specific role, a specific permission-needing entity and the permission granted. An access rule might look like these:

rule 14: guest role + page name + read permission rule 46: approver role + add column + execute permission 

and so on. I'll leave the ERD as an exercise to the reader ;-) if you have questions, leave a comment.

When you create a resource, how do you decide what roles and permissions to assign to it? Do you inherit them from its parents? That's the part which I'm mystified with. If you leave it empty for 'someone' to assign roles and permissions to it, this would become a huge management overhead on the system.

This is indeed an overhead, and must be taken care of, either by the developer writing the resource, or someone who is responsible for this cross-application feature. It's kind of like synchronization code. either it appears everywhere, or it's no good.

RESTful_ACL is nice, but has no built-in concept of role-based control, so this isn't actually an RBAC solution.

I think the answer to your question goes as deep as you wish to go. If you happen to think about putting roles into groups and then associating groups with users wouldn't be enough. Eventually you'll need to give specific permissions to a user on a specific object (a forum, a video etc).

I'm more close to Yuval's answer, all we need is to associate project-wide objects + actions + users. To provide this; a base object (Entity) makes perfect sense. Any object inheriting from Entity can be easily associated with a user + action this way.

As you also wish to keep things simple; my suggestion would be;

• Any object due to rbac restrictions should derive from a base Entity.
• There should be a list of roles, which are one-to-one related with an Entity.
• There should be a list of relations between users and roles.

To take things one step further, I would also reccomend the following (for an automated rbac)

• I use service-based access to my objects. That is; I create respositories of objects (which do the db-access for me) and I access repositories via service functions.
• I use a custom attribute at the beginning of every service function. This defines the required role to access that function.
• I use the User parameter to access to all my service functions, and each service function does a role check before executing itself. Reflection helps me to understand which function I call, and what kind of role it has (via custom attributes)
• I also run an initializer on my application startup, and it checks for all the functions (and their attributes) and sees if I added a new required role. If there's a role I just added and doesn't appear to be on the db, it creates it on db.

But alas, that's just available for .NET, as far as I know Java doesn't have custom attributes so that's not yet likely to be available for Java.

I'd like to come up with some code examples but I'm too lazy to do that. Still if you have questions about my way of rbac; you can ask here and I'll surely reply.

Role Requirement works with Restful Authentication very well to provide role-based auth functions and is well-maintained.

Yeah, what a difference a few years makes. :) Personally, I have taken to using Devise for authentication and a combination of roll-your-own role assignment and CanCan for most of my authorization needs. I got started down this road after watching Railscasts #192 (railscasts.com/episodes/192-authorization-with-cancan). I'm a fan of the binary-masking method of storing user roles.

Try https://github.com/ThoughtWorksStudios/piece, it is a rule engine for you to manage user role based access control:

1. Define access control rules
2. Combine rules to construct new rules

You can find full Rails application example here: https://github.com/xli/piece-blog

For .net applications you should look at something like Visual Guard http://www.visual-guard.com/ to avoid having to handle permissions and roles from scratch.

Also for .net, you have the membership and role providers and authorisation handled with configuration. http://www.odetocode.com/Articles/427.aspx

Introduction to RBAC -

Role based access control system is a method of restricting access to 'some sources or applications or some features of applications' based on the roles of users of organization.

Here, restrictions can be by means of multiple permissions, those are created by administrator to restrict access, and these permissions collectively represents a role, which will be assigned to user.

And if we go slight deeper in RBAC, it basically contains 3 features.

1) Authentication - It confirms the user's identity. Usually it is done via user accounts and passwords or credentials.

2) Authorization - It defines what user can do and cannot do in an application. Ex. ‘Modifying order’ is allowed but ‘creating new order’ is not allowed.

3) Auditing of user actions on applications. - It keeps track of user's actions on applications, as well as who has granted which access to which users?

This was very basic top view picture of RBAC system.

Basic Structure of RBAC system can contain following components: Users, Roles, Permissions or restrictions, resources.

• Permissions or restrictions – permissions represents an access to application’s resource.
• Role – It contains collection of permissions
• User – Single or multiple roles assigned to user, so eventually user contains permissions via means of role.

In addition to this, you can also have collection of users – called – groups, and role can be assigned to groups, if you want to support complex scenarios. So, This was very basic information about RBAC structure.