Help with database strategy for giving roles to users in specific model instances

Question

I have a ruby app running with declarative authorization and I have made the roles:

admin ( app admin ) org_admin ( organization administratr ) org_colab ( organization colaborator ) org_visitor ( organization visitor )

a User can has_many Organizations and he can be an admin or a colaborator.

I link them using a Affiliation table

What is the best strategy to solve this many to many roles?

To put a extra attribute on the Affiliation table ? like : Affiliation(:user_id:integer, :organization_id:integer, :affiliation_type:integer)

and the affiliation type can be 0 for org_admin and 1 for org_colab and 2 for org_visitor?

I imagine there must be a better way to assign roles to a particular organization...

Answer 1

It's the classic "User-Group-Role" model. It's a ternary relationship. User-Group is many-to-many; so is Group-Role. You'll need five tables to capture all of it.

You'll start with a User table. It'll have a primary key (of course). Same with Group and Role.

You'll have a UserGroup table sitting in-between the User and Group tables in your E/R diagram. UserGroup will have two columns: user_id and group_id. The primary key will be the combination (user_id, group_id). The user_id column will have a foreign key relationship with the primary key of the User table. Ditto for the group_id column and the Group table.

There will be a similar arrangement with Group and Role. Each will have a primary key. The GroupRole table will sit between the two in your E/R diagram. Repeat the verbiage above and you'll have it.

I'd create an E/R diagram to show you, but I'm busy doing some other things. I hope this is sufficient for you. If not, perhaps I'll add it later.

So Users don't have Roles; Groups do. You add a User to a Group, and that User gets all the authorizations that the Group has. That way you can add a particular Role (e.g., admin permissions for the Admin group) one for a particular Group. Then everyone who is added to that Group will get those permissions.

Answer 2

Hi Victor,

i actually implement something similar to my project. To clarify if i understand you correct - your user might have a role in the whole application-context, as well as a specific role in the organizational-context which may not depend on each other.

A pragmatic solution might be to implement two different rolesets. Let's think of the following construct: You have a user-model (having application-wide informations), a organization-model (which defines an organization and might possibly be owned by a user) and a collaborative-model which defines a relationship between a user and a organization.

In this case it would be the best to store roles in the user-model (which do the application-wide stuff) and to store organization-specific roles in the collaborative-model.

I used a string based roles-store in the models in combination with ryan bates cancan-gem - its easy & fast to use and puts roles-logic in a defined place.

This might not work for thousands of different roles and groups and stuff, in that case you should implement the role-storage in an extra model (with a polymorphic relation to users and collaboratives).

Best wishes - Florian

edit: To make that implementation searchable by ActiveRecord you might want to use the 'serialize'-feature for the :roles-field, as described in ActiveRecord::Base.