Guide To Design Database For RBAC In MySQL

By bhagwatchouhan
Guide To Design Database For RBAC In MySQL

This tutorial provides complete steps to design a database schema of a Role-Based Access Control (RBAC) system to manage the users, roles, and permissions. It can be further used to decide access to specific resources based on specific permissions. Using an RBAC system should be considered as an integral part of any application sharing the resources among multiple users. E.g. the employees of an organization can access or manage the products based on the permissions assigned to them. Ideally, the permissions can be assigned via roles.

The Entity Relationship Diagram or visual database design is shown below.

RBAC Database Design

Fig 1

Notes: The role and permission tables discussed in this tutorial can be added to the application databases discussed in the Blog and Poll & Survey tutorials. This tutorial assumes that the permissions are hard-coded at the code level to check the access.

You can also visit the popular tutorials including How To Install MySQL 8 on Ubuntu, How To Install MySQL 8 on Windows, Blog Database in MySql, Poll and Survey Database in MySql, and Learn Basic SQL Queries In MySQL.

 

RBAC Database

The very first step is to create the RBAC Database. It can be created using the query as shown below.

CREATE SCHEMA `rbac` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have used the character set utf8mb4 to support a wide range of characters.

 

User Table

In this section, we will design the User Table to store user information. Below mentioned is the description of all the columns of the User Table.

IdThe unique id to identify the user.
First NameThe first name of the user.
Middle NameThe middle name of the user.
Last NameThe last name of the user.
MobileThe mobile number of the user. It can be used for login and registration purposes.
EmailThe email of the user. It can be used for login and registration purposes.
Password HashThe password hash generated by the appropriate algorithm. We must avoid storing plain passwords.
Registered AtThis column can be used to calculate the life of the user with the application.
Last LoginIt can be used to identify the last login of the user.
IntroThe brief introduction of the User.
ProfileThe user details.

The User Table with the appropriate constraints is as shown below.

CREATE TABLE `rbac`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `passwordHash` VARCHAR(32) NOT NULL,
  `registeredAt` DATETIME NOT NULL,
  `lastLogin` DATETIME NULL DEFAULT NULL,
  `intro` TINYTEXT NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC) );

 

Role Table

In this section, we will design the Role Table to store the system roles. Below mentioned is the description of all the columns of the Role Table.

IdThe unique id to identify the role.
TitleThe role title.
SlugThe unique slug to search the role.
DescriptionThe description to mention the role.
ActiveThe flag to check whether the role is currently active.
Created AtIt stores the date and time at which the role is created.
Updated AtIt stores the date and time at which the role is updated.
ContentThe complete details about the role.

The Role Table with the appropriate constraints is as shown below.

CREATE TABLE `rbac`.`role` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(75) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TINYTEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC) );

 

Permission Table

In this section, we will design the Permission Table to store the system permissions. Below mentioned is the description of all the columns of the Permission Table.

IdThe unique id to identify the permission.
TitleThe permission title.
SlugThe unique slug to search the permission.
DescriptionThe description to mention the permission.
ActiveThe flag to check whether the permission is currently active.
Created AtIt stores the date and time at which the permission is created.
Updated AtIt stores the date and time at which the permission is updated.
ContentThe complete details about the permission.

The Permission Table with the appropriate constraints is as shown below.

CREATE TABLE `rbac`.`permission` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(75) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TINYTEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC) );

 

Role Permission Table

The Role Permission Table can be used to store the mappings of the permissions to the roles. Below mentioned is the description of all the columns of the Role Permission Table.

Role IdThe role id to identify the role.
Permission IdThe permission id to identify the permission.
Created AtIt stores the date and time at which the mapping is created.
Updated AtIt stores the date and time at which the mapping is updated.

The Role Permission Table with the appropriate constraints is as shown below.

CREATE TABLE `rbac`.`role_permission` (
  `roleId` BIGINT NOT NULL,
  `permissionId` BIGINT NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL,
  PRIMARY KEY (`roleId`, `permissionId`),
  INDEX `idx_rp_role` (`roleId` ASC),
  INDEX `idx_rp_permission` (`permissionId` ASC),
  CONSTRAINT `fk_rp_role`
    FOREIGN KEY (`roleId`)
    REFERENCES `rbac`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rp_permission`
    FOREIGN KEY (`permissionId`)
    REFERENCES `rbac`.`permission` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

 

User Role

We can keep the system simple by assigning a single role to the user. The assigned role can be used to pull the permissions mapped to the role. The access to the specific resource or permission can be checked by comparing the hard-coded permission the list of permissions mapped to the role assigned to the user.

It can be done using the query as shown below.

ALTER TABLE `rbac`.`user` 
ADD COLUMN `roleId` BIGINT NOT NULL AFTER `id`,
ADD INDEX `idx_user_role` (`roleId` ASC);
ALTER TABLE `rbac`.`user` ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`roleId`) REFERENCES `rbac`.`role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

 

Advanced Options

One can think of assigning multiple roles to the user using the User Role Table. The more advanced options include the hierarchy system to group the permissions or roles. These options further complicate the query to pull the permissions list, hence need optimization by having an appropriate cache mechanism.

 

Summary

In this tutorial, we have discussed the database design of an RBAC system to secure specific requests and resources by allowing access only if the user is having appropriate permission.

You may submit your comments to join the discussion. You may also be interested in designing the database of the Blog and Poll & Survey applications.

The complete database schema is also available on GitHub.

Share this blog:

Profile picture for user bhagwatchouhan
bhagwatchouhan