Database.sql

CREATE DATABASE USERSERVICE;
USE USERSERVICE;

CREATE TABLE PERMISSION (ID INT PRIMARY KEY AUTO_INCREMENT, PERMISSION_NAME VARCHAR(20));

CREATE TABLE USER (ID INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), EMAIL_ID VARCHAR(255) UNIQUE KEY, PASSWORD VARCHAR(1000));

CREATE TABLE ROLE (ID INT PRIMARY KEY AUTO_INCREMENT, ROLE_NAME VARCHAR(20));

CREATE TABLE ASSIGN_PERMISSION_TO_ROLE (ID INT PRIMARY KEY AUTO_INCREMENT, PERMISSION_ID INT, FOREIGN KEY(PERMISSION_ID) REFERENCES
PERMISSION (ID), ROLE_ID INT, FOREIGN KEY(ROLE_ID) REFERENCES ROLE(ID));

CREATE TABLE ASSIGN_USER_TO_ROLE (ID INT PRIMARY KEY AUTO_INCREMENT, USER_ID INT, FOREIGN KEY(USER_ID) REFERENCES USER(ID), 
ROLE_ID INT, FOREIGN KEY(ROLE_ID) REFERENCES ROLE(ID));

INSERT INTO PERMISSION (ID, PERMISSION_NAME) VALUES (1, 'CREATE_NOTE'), (2, 'EDIT_NOTE'), (3, 'DELETE_NOTE'), (4, 'VIEW_ALL_NOTE'), (5, 'VIEW_NOTE');

INSERT INTO ROLE (ID, ROLE_NAME) VALUES (1, 'ADMINISTRATOR'), (2, 'AUDITOR');

INSERT INTO USER (ID, NAME, EMAIL_ID, PASSWORD) VALUES (1, 'John', 'john@gmail.com','$2a$10$jbIi/RIYNm5xAW9M7IaE5.WPw6BZgD8wcpkZUg0jm8RHPtdfDcMgm');
INSERT INTO USER (ID, NAME, EMAIL_ID, PASSWORD) VALUES (2, 'Mike', 'mike@gmail.com','$2a$10$jbIi/RIYNm5xAW9M7IaE5.WPw6BZgD8wcpkZUg0jm8RHPtdfDcMgm');

INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (1, 1);
INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (2, 1);
INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (3, 1);
INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (4, 1);
INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (5, 1);

INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (4, 2);
INSERT INTO ASSIGN_PERMISSION_TO_ROLE (PERMISSION_ID, ROLE_ID) VALUES (5, 2);

INSERT INTO ASSIGN_USER_TO_ROLE (USER_ID, ROLE_ID) VALUES (1, 1);
INSERT INTO ASSIGN_USER_TO_ROLE (USER_ID, ROLE_ID) VALUES (2, 2);

SELECT * FROM USER WHERE EMAIL_ID ='john@gmail.com';

SELECT DISTINCT P.PERMISSION_NAME FROM PERMISSION P 
INNER JOIN ASSIGN_PERMISSION_TO_ROLE P_R ON P.ID=P_R.PERMISSION_ID
INNER JOIN ROLE R ON R.ID=P_R.ROLE_ID 
INNER JOIN ASSIGN_USER_TO_ROLE U_R ON U_R.ROLE_ID=R.ID
INNER JOIN USER U ON U.ID=U_R.USER_ID
WHERE U.EMAIL_ID='mike@gmail.com';