role based access control schemas in mysql, postgres and sqlite

mysql.sql

/*
 * Create Tables
 */

CREATE TABLE IF NOT EXISTS `PREFIX_permissions` (
  `ID` int(11) NOT NULL auto_increment,
  `Lft` int(11) NOT NULL,
  `Rght` int(11) NOT NULL,
  `Title` char(64) NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `Title` (`Title`),
  KEY `Lft` (`Lft`),
  KEY `Rght` (`Rght`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `PREFIX_rolepermissions` (
  `RoleID` int(11) NOT NULL,
  `PermissionID` int(11) NOT NULL,
  `AssignmentDate` int(11) NOT NULL,
  PRIMARY KEY  (`RoleID`,`PermissionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `PREFIX_roles` (
  `ID` int(11) NOT NULL auto_increment,
  `Lft` int(11) NOT NULL,
  `Rght` int(11) NOT NULL,
  `Title` varchar(128) NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `Title` (`Title`),
  KEY `Lft` (`Lft`),
  KEY `Rght` (`Rght`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `PREFIX_userroles` (
  `UserID` int(11) NOT NULL,
  `RoleID` int(11) NOT NULL,
  `AssignmentDate` int(11) NOT NULL,
  PRIMARY KEY  (`UserID`,`RoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*
 * Insert Initial Table Data
 */

INSERT INTO `PREFIX_permissions` (`ID`, `Lft`, `Rght`, `Title`, `Description`)
VALUES (1, 0, 1, 'root', 'root');

INSERT INTO `PREFIX_rolepermissions` (`RoleID`, `PermissionID`, `AssignmentDate`)
VALUES (1, 1, UNIX_TIMESTAMP());

INSERT INTO `PREFIX_roles` (`ID`, `Lft`, `Rght`, `Title`, `Description`)
VALUES (1, 0, 1, 'root', 'root');

INSERT INTO `PREFIX_userroles` (`UserID`, `RoleID`, `AssignmentDate`)
VALUES (1, 1, UNIX_TIMESTAMP());

postgres.sql

/*
 * Create Tables
 */
drop table rbac_permissions, rbac_rolepermissions, rbac_roles, rbac_userroles;

create table if not exists rbac_permissions (
  id    serial primary key,
  lft   integer not null,
  rght  integer not null,
  title text not null,
  description text not null
);
create index on rbac_permissions (lft);
create index on rbac_permissions (rght);
create index on rbac_permissions (title);

create table if not exists rbac_rolepermissions (
  role_id        integer not null,
  permission_id  integer not null,
  assignment_date timestamptz not null,
  primary key  (role_id, permission_id)
);

create table if not exists rbac_roles (
  id    serial primary key,
  lft   integer not null,
  rght  integer not null,
  title varchar not null,
  description text not null
);
create index on rbac_roles (lft);
create index on rbac_roles (rght);
create index on rbac_roles (title);

create table if not exists rbac_userroles (
  user_id integer not null,
  role_id integer not null,
  assignment_date timestamptz not null,
  primary key  (user_id, role_id)
);

/*
 * Insert Initial Table Data
 */
insert into rbac_permissions (id, lft, rght, title, description)
values (1, 0, 1, 'root', 'root');

insert into rbac_rolepermissions (role_id, permission_id, assignment_date)
values (1, 1, current_timestamp);

insert into rbac_roles (id, lft, rght, title, description)
values (1, 0, 1, 'root', 'root');

insert into rbac_userroles (user_id, Role_id, assignment_date)
values (1, 1, current_timestamp);

sqlite.sql

/*
 * Create Tables
 */

CREATE TABLE `PREFIX_permissions` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Lft` INTEGER NOT NULL,
  `Rght` INTEGER NOT NULL,
  `Title` char(64) NOT NULL,
  `Description` text NOT NULL
);

CREATE TABLE `PREFIX_rolepermissions` (
  `RoleID` INTEGER NOT NULL,
  `PermissionID` INTEGER NOT NULL,
  `AssignmentDate` INTEGER NOT NULL,
  PRIMARY KEY  (`RoleID`,`PermissionID`)
);

CREATE TABLE `PREFIX_roles` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Lft` INTEGER NOT NULL,
  `Rght` INTEGER NOT NULL,
  `Title` varchar(128) NOT NULL,
  `Description` text NOT NULL
);

CREATE TABLE `PREFIX_userroles` (
  `UserID` INTEGER NOT NULL,
  `RoleID` INTEGER NOT NULL,
  `AssignmentDate` INTEGER NOT NULL,
  PRIMARY KEY  (`UserID`,`RoleID`)
);

/*
 * Insert Initial Table Data
 */

INSERT INTO `PREFIX_permissions` (`ID`, `Lft`, `Rght`, `Title`, `Description`)
VALUES (1, 0, 1, 'root', 'root');

INSERT INTO `PREFIX_rolepermissions` (`RoleID`, `PermissionID`, `AssignmentDate`)
VALUES (1, 1, strftime('%s', 'now'));

INSERT INTO `PREFIX_roles` (`ID`, `Lft`, `Rght`, `Title`, `Description`)
VALUES (1, 0, 1, 'root', 'root');

INSERT INTO `PREFIX_userroles` (`UserID`, `RoleID`, `AssignmentDate`)
VALUES (1, 1, strftime('%s', 'now'));

@morenoh149 thank you so much for this. What is the purpose of lft and rght ?

I have the same question

not sure. Its been so long ago. I would load these into a db and diagram it in dbeaver gui. Share the diagram here to refresh my memory :)

my guess would be that table is an associative entity, so it would have a foreignkey to two other tables. Thats how you typically model a many to many relationship.

The lft and rght to decorate the hierarchy of the permissions. For example, account -> account settings -> create / update / delete account. So when someone has permission with the account module, then he has permission to access everything else within the account module.

hi thanks for the script,
I think it would be nice If the sql join query to get

1. roles associated with a user
2. permissions associated with a role
   is also provided.