52

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?

JasonSmith
  • 72,674
  • 22
  • 123
  • 149
  • Use a framework that implements authorization for you. Look into CanCanCan or other Attribute Based Access Control (ABAC) models e.g. XACML – David Brossard Jun 15 '17 at 06:55

10 Answers10

82

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

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

Resources <- require -> (one or many) Permissions.

Roles <- are collections of -> (one or many) Permissions.

Users <- can have -> (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.

Amr Mostafa
  • 939
  • 6
  • 3
  • 1
    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! – Jeach Nov 27 '10 at 06:39
  • I understand this in concept but what might the table structure look like for the core values? – HPWD Jul 30 '15 at 20:34
  • How do I group similar permissions so that it helps me display the same in the frontend? – Jaseem Abbas Oct 18 '15 at 08:54
  • You might want to consider AERBAC https://www.researchgate.net/profile/D_Kuhn2/publication/260584013_Adding_Attributes_to_Role-Based_Access_Control/links/55c4b38008aeca747d617b22/Adding-Attributes-to-Role-Based-Access-Control.pdf "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." – geoyws Sep 03 '19 at 12:29
  • What exactly should be in the permissions table? Can you please give example? – Sanjay Prajapati Dec 11 '20 at 15:57
32

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

enter image description here

Community
  • 1
  • 1
Hanxue
  • 12,243
  • 18
  • 88
  • 130
  • 15
    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. – kipzes Dec 12 '15 at 19:35
  • nice diagram, yet you just happen to leave out the resources model ... – Yordan Georgiev Nov 30 '20 at 11:46
  • 6
    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. – Arash Mar 01 '21 at 07:18
3

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.

Yuval =8-)

Yuval
  • 7,987
  • 12
  • 40
  • 54
  • 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. – Jeach Apr 16 '10 at 22:07
  • 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. – Yuval Apr 17 '10 at 06:32
1

You can use Restful ACL Rails plugin.

IDBD
  • 298
  • 2
  • 6
1

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.

detay
  • 1,082
  • 11
  • 19
0

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

Yardboy
  • 2,777
  • 1
  • 23
  • 29
  • the web page says it is no longer maintained :-( – Peter Kriens Nov 27 '12 at 08:21
  • 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 (http://railscasts.com/episodes/192-authorization-with-cancan). I'm a fan of the binary-masking method of storing user roles. – Yardboy Nov 27 '12 at 16:25
0

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

Xiao Li
  • 11
-1

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

Keith Patton
  • 938
  • 5
  • 12
-1

I really like this blog post: https://content.pivotal.io/blog/access-control-permissions-in-rails

EDIT:

It seems that ryanb of railscasts thought along the same lines and created a gem called cancan https://github.com/ryanb/cancan using a basic technique similar to the pivotollabs post.

Dave Powers
  • 2,051
  • 2
  • 30
  • 34
bluekeys
  • 2,217
  • 1
  • 22
  • 30
-1

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.

Joe
  • 41,484
  • 20
  • 104
  • 125
Kunal Khatri
  • 451
  • 3
  • 12