CODEWITHSUNDEEP
sqlruby-on-railsrubyoracle-databaseruby-on-rails-4
newbie
newbie

Reputation: 1033

flexible roles and permissions database schema

I have a question about roles and permissions schema. I understand there are existing based schemas to do manage and define this.

But I wanted to understand if there are potential issues or bad practices with the folllowing approach.

Here is how I envision the role table to be like:

Role:
id(pk)
name(varchar)
description(varchar)

This is my permissions table:

Permissions
id(pk)
role_ids(array of role ids)
Name
Description

Each permission can belong to many roles.

To identify the above, I am storing an array of role_ids in the role_ids field.

Is the above pratice considered bad?

Would it cause issues when adding new roles or assigning permissions to different roles. Will it be programatically inefficient to insert and delete from the above array?

I looked up existing approaches but some of them seem too complicated for my simple use case.

Upvotes: 0

Views: 929

Answers (2)

phlegx
phlegx

Reputation: 2732

There is a gem on Rubygems called rolify. Here the gem: https://github.com/RolifyCommunity/rolify

And here the tables used by rolify:

class RolifyCreateRoles < ActiveRecord::Migration

  def change
    create_table :roles do |t|
      t.string :name, null: false
      # With resource, a role can be assigned to a complete model class or to instances of it
      t.references :resource, polymorphic: true
      t.timestamps
    end


    create_table :users_roles do |t|
      t.references :user, null: false
      t.references :role, null: false
      t.timestamps
    end

    add_index(:roles, :name)
    add_index(:roles, [ :name, :resource_type, :resource_id ])
    add_index(:users_roles, [ :user_id, :role_id ])
  end

end

Add a role to a user

To define a global role:

user = User.find(1)
user.add_role :admin

To define a role scoped to a resource instance

user = User.find(2)
user.add_role :moderator, Forum.first

To define a role scoped to a resource class

user = User.find(3)
user.add_role :moderator, Forum

Upvotes: 0

victorkt
victorkt

Reputation: 14552

It is considered a bad practice in a relational database since this data is not normalised. As @spickermann pointed out, if you wanted all permissions for a given role it you be very difficult to query.

Also, usually a Role has many Permissions and a Permission might belong to many roles, but it is weird to design just a Permission having many Roles.

You should create a third table with the associations, like this:

Role
id(pk)
name(varchar)
description(varchar)

Permissions
id(pk)
Name
Description

RolePermissions
role_id(fk)
permission_id(fk)

Check the guide Active Record Associations, it has many examples on the matter and how they are implemented in Ruby on Rails.

Upvotes: 2

Related Questions