Ruby on Rails
AccessControlListExample

Database schema (PostgreSQL)

CREATE TABLE users (
    id serial PRIMARY KEY,
    nick character varying NOT NULL,
    name character varying,
    "password" character varying NOT NULL,
    modified timestamp with time zone 
        DEFAULT now() NOT NULL,
    created timestamp with time zone 
        DEFAULT now() NOT NULL,
    "access" timestamp with time zone
);

CREATE TABLE roles (
    id serial PRIMARY KEY,
    name character varying NOT NULL,
    info character varying
);

CREATE TABLE permissions (
    id serial PRIMARY KEY,
    name character varying NOT NULL,
    info character varying
);

CREATE TABLE roles_users (
    user_id integer REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    role_id integer REFERENCES roles ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
);
CREATE INDEX ur_map_idx ON roles_users USING btree (user_id, role_id);

CREATE TABLE permissions_roles (
    role_id integer REFERENCES roles ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    permission_id integer REFERENCES permissions(id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
);
CREATE INDEX rp_map_idx ON permissions_roles USING btree (role_id, permission_id);

MySQL version

CREATE TABLE users (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nick VARCHAR(40) NOT NULL,
  name VARCHAR(40),
  password VARCHAR(40) NOT NULL,
  modified_at DATETIME,
  created_at DATETIME,
  access DATETIME,
  PRIMARY KEY(id)
);

CREATE TABLE roles (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  info VARCHAR(80),
  PRIMARY KEY(id)
);

CREATE TABLE permissions (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  info VARCHAR(80) NULL,
  PRIMARY KEY(id)
);

CREATE TABLE roles_users (
  user_id INTEGER UNSIGNED NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,
  INDEX roles_users_FKIndex1(role_id),
  INDEX roles_users_FKIndex2(user_id),
  FOREIGN KEY(role_id)
    REFERENCES roles(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(user_id)
    REFERENCES users(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

CREATE TABLE permissions_roles (
  role_id INTEGER UNSIGNED NOT NULL,
  permission_id INTEGER UNSIGNED NOT NULL,
  INDEX permissions_roles_FKIndex1(permission_id),
  INDEX permissions_roles_FKIndex2(role_id),
  FOREIGN KEY(permission_id)
    REFERENCES permissions(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(role_id)
    REFERENCES roles(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

NOTE: There is a funky MySQL error which you might see if you had your own “users” table before adding all the role stuff:

ERROR 1005 (HY000): Can't create table './<your_database>/roles_users.frm' (errno: 150)

This is most likely due to type mismatches between the roles_users.users_id column and the users.id column (thanks for the great error message MySQL). Just alter users.id to match exactly :

users_id INTEGER UNSIGNED NOT NULL

RubyOnRails migration version

Create a new migration in the command line in your project directory :

ruby script/generate migration create_acl_schema

and put the following content into db/migrate/00X_create_acl_schema.rb:


class CreateAclSchema < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
      t.column :nick, :string, :limit=>40, :null=>false
      t.column :name, :string, :limit=>40
      t.column :password, :string, :limit=>40, :null=>false
      t.column :modified_at, :datetime
      t.column :created_at, :datetime
      t.column :access, :datetime
      t.column :nick, :string
    end
    create_table :roles do |t|
      t.column :name, :string, :limit=>40, :null=>false
      t.column :info, :string, :limit=>80
    end
    create_table :permissions do |t|
      t.column :name, :string, :limit=>40, :null=>false
      t.column :info, :string, :limit=>80
    end
    create_table(:roles_users, :id => false) do |t|
      t.column :user_id, :integer, :null=>false
      t.column :role_id, :integer, :null=>false
    end
    create_table(:permissions_roles, :id => false) do |t|
      t.column :role_id, :integer, :null=>false
      t.column :permission_id, :integer, :null=>false
    end
  end

  def self.down
    drop_table :users
    drop_table :roles
    drop_table :permissions
    drop_table :roles_users
    drop_table :permissions_roles
  end
end

Model code

require 'active_record'
class Permission < ActiveRecord::Base
  has_and_belongs_to_many :roles
end
class Role < ActiveRecord::Base
  has_and_belongs_to_many :permissions
  has_and_belongs_to_many :users
end
class User < ActiveRecord::Base
  has_and_belongs_to_many :roles
end

Example Usage

Get the user associated with id #1:

user = User.find(1)

Get the roles associated with this user:

roles = user.roles

Get the permissions for the first role associated with the user:

perms = roles.first.permissions

Add role #2 to the user:

role = Role.find(2)
user.add_roles(role)
user.save

NOTE: Supposedly the above is old syntax, and you should do this instead:

role = Role.find(2)
user.roles << role
user.save

Add a few more permissions to role #2:

role.add_permissions(Permission.find(2), Permission.find(3))
role.save

Remove one of the permissions again:

role.remove_permissions(Permission.find(3))
role.save

category:Example