Introduction – https://youtu.be/JVr56YgMm1I

Role-based database design for access control – https://youtu.be/IZsSKpITIEU

Authorization Server – https://youtu.be/ZIAi8sGHPII

Resource Server – https://youtu.be/lxsUrtcxrog

CORS support – https://youtu.be/7Yqb275FKmY

Web Application Integration – https://youtu.be/9l_Hn8qzsYw

SQL script

Delete database if user service exists;

Create database user service;

use user service;

Permission to create a table (ID int, primary key auto_increment, permission name varchar(50), unique key non-null);

Create table role (ID int primary key auto_increment, role name varchar(50) unique key non-null);

Create table role_permission(id int primary key auto_increment, role_id int, foreign key(role_id)
refers to role(ID), authorizationID int, foreign key(authorizationID) refers to authorization(ID));

Create table User (ID int primary key auto_increment, first name varchar(50) not null, last name varchar(50),
email_id varchar(50) not null, password varchar(1000), mobile varchar(20), country varchar(50),
UserType varchar(20));

Create table role_users (id int primary key auto_increment, role_id int, foreign key(role_id) refers to role(id),
user_id int, foreign key (user_id) refers to user (id));

insert into permission values (permission name) ('view permission');

insert into permission values (permission name) ('create_role');
insert into authorization values (authorization name) ('Edit role');
insert into permission values (permission name) ('view role');
insert into authorization values (authorization name) ('Delete role');

insert values into the permission (permission name) ('Assign permissions to the role');
insert into permission values (permission name) ('view_permissions_by_role');

insert into permission values (permission name) ('Create User');
insert into permission values (permission name) ('Edit User');
insert into permission values (permission name) ('view_users');
insert into permission values (permission name) ('delete_users');

insert values into the permission (permission name) ('Assign user to a role');
insert into permission values (permission name) ('view_users_by_role');

insert values into the role (role name) ('Administrator');

insert values (1,1) into role_permission (role_id, permission_id);
insert values (1,2) into role_permission (role_id, permission_id);
insert values (1,3) into role_permission (role_id, permission_id);
insert values (1,4) into role_permission (role_id, permission_id);

insert values (1,5) into role_permission (role_id, permission_id);
insert values (1,6) into role_permission (role_id, permission_id);
insert values (1,7) into role_permission (role_id, permission_id);
insert values (1,8) into role_permission (role_id, permission_id);

insert values (1,9) into role_permission (role_id, permission_id);
insert values (1,10) into role_permission (role_id, permission_id);
insert values (1,11) into role_permission (role_id, permission_id);
insert values (1,12) into role_permission (role_id, permission_id);

insert values (1,13) into role_permission (role_id, permission_id);

insert into user values (First Name, Last Name, Email ID, Password, Mobile Phone, Country, User Type)
('Kite', 'Danie', '[email protected]','$2y$12$U9VFvF/e4XtvXv8qQn7GmOOlAOnXAb0Al.qTOvbI3Gi0yMyiqaYNW',
'+916789401494','USA', 'super_admin');

insert into user values (First Name, Last Name, Email ID, Password, Mobile Phone, Country, User Type)
(“William”, “John”, “[email protected]”, “$2y$12$U9VFvF/e4XtvXv8qQn7GmOOlAOnXAb0Al.qTOvbI3Gi0yMyiqaYNW”, “+918939052159”, “USA”, “admin”);

insert values (1,2) into role_users (role_id, user_id);

Select * from users where email_id='[email protected]';
Select * from users where email_id='[email protected]';

Select p.permission_name from the users and
Inner join role user r_u on u.id=r_u.user_id
inner join role r on r_u.role_id=r.id
Inner join with role permission r_p on r_p.role_id=r.id
Inner connection permission p on p.id=r_p.permission_id where u.email_id='[email protected]';

Update user, set password='$2a$10$eUB5vV/xWXrcR61TqcWRXOCfl37r3fk.UXNXEzx7xTol5inz5xZ.2';

Please take the opportunity to connect with your friends and family and share this video with them if you find it useful.