Skip to content

Instantly share code, notes, and snippets.

@morenoh149
Last active November 29, 2023 11:44
Show Gist options
  • Save morenoh149/dfc41d36dad840e108d7 to your computer and use it in GitHub Desktop.
Save morenoh149/dfc41d36dad840e108d7 to your computer and use it in GitHub Desktop.
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'));
@datnguyen293
Copy link

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.

@Jarajesh
Copy link

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.

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