Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
role based access control schemas in mysql, postgres and sqlite
/*
* 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());
/*
* 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);
/*
* 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'));
@sharkyze

This comment has been minimized.

Copy link

@sharkyze sharkyze commented Jan 10, 2019

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

@anahids

This comment has been minimized.

Copy link

@anahids anahids commented Jul 22, 2020

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

I have the same question

@morenoh149

This comment has been minimized.

Copy link
Owner Author

@morenoh149 morenoh149 commented Jul 22, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment