On souhaite ajouter une gestion des rôles et des permissions à notre gestion des utilisateurs.

• Créez la BDD qui respecte ce schéma :

• Créez les pages PHP qui permettent de CRUD les rôles et les permissions
• Modifiez les pages de gestion des utilisateurs pour qu’elles affichent et gère les rôles :
  • Create
  • Read
  • Update
  • Delete
• Modifiez les pages de gestion des rôles pour qu’elles affichent et gère les permissions :
  • Create
  • Read
  • Update
  • Delete

Données fournies

-- Insert data into USERS table and get IDs
INSERT INTO users (username, email, password) VALUES ('user1', '[email protected]', 'password1');
SET @user1_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user2', '[email protected]', 'password2');
SET @user2_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user3', '[email protected]', 'password3');
SET @user3_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user4', '[email protected]', 'password4');
SET @user4_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user5', '[email protected]', 'password5');
SET @user5_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user6', '[email protected]', 'password6');
SET @user6_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user7', '[email protected]', 'password7');
SET @user7_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user8', '[email protected]', 'password8');
SET @user8_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user9', '[email protected]', 'password9');
SET @user9_id = LAST_INSERT_ID();
INSERT INTO users (username, email, password) VALUES ('user10', '[email protected]', 'password10');
SET @user10_id = LAST_INSERT_ID();

-- Insert data into ROLES table and get IDs
INSERT INTO roles (role_name) VALUES ('Admin');
SET @role1_id = LAST_INSERT_ID();
INSERT INTO roles (role_name) VALUES ('Editor');
SET @role2_id = LAST_INSERT_ID();
INSERT INTO roles (role_name) VALUES ('Viewer');
SET @role3_id = LAST_INSERT_ID();
INSERT INTO roles (role_name) VALUES ('Contributor');
SET @role4_id = LAST_INSERT_ID();
INSERT INTO roles (role_name) VALUES ('Moderator');
SET @role5_id = LAST_INSERT_ID();

-- Insert data into PERMISSIONS table and get IDs
INSERT INTO permissions (permission_name) VALUES ('create user');
SET @perm1_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('read user');
SET @perm2_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('update user');
SET @perm3_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('delete user');
SET @perm4_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('create role');
SET @perm5_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('read role');
SET @perm6_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('update role');
SET @perm7_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('delete role');
SET @perm8_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('create permission');
SET @perm9_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('read permission');
SET @perm10_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('update permission');
SET @perm11_id = LAST_INSERT_ID();
INSERT INTO permissions (permission_name) VALUES ('delete permission');
SET @perm12_id = LAST_INSERT_ID();

-- Insert data into USER_ROLE table
INSERT INTO user_role (user_id, role_id) VALUES
(@user1_id, @role1_id),
(@user2_id, @role2_id),
(@user3_id, @role3_id),
(@user4_id, @role4_id),
(@user5_id, @role5_id),
(@user6_id, @role2_id),
(@user7_id, @role3_id),
(@user8_id, @role1_id),
(@user9_id, @role4_id),
(@user10_id, @role5_id);

-- Insert data into ROLE_PERMISSION table
INSERT INTO role_permission (role_id, permission_id) VALUES
(@role1_id, @perm1_id),
(@role1_id, @perm2_id),
(@role1_id, @perm3_id),
(@role1_id, @perm4_id),
(@role1_id, @perm5_id),
(@role1_id, @perm6_id),
(@role1_id, @perm7_id),
(@role1_id, @perm8_id),
(@role1_id, @perm9_id),
(@role1_id, @perm10_id),
(@role1_id, @perm11_id),
(@role1_id, @perm12_id),
(@role2_id, @perm2_id),
(@role2_id, @perm6_id),
(@role2_id, @perm10_id),
(@role3_id, @perm2_id),
(@role4_id, @perm1_id),
(@role4_id, @perm2_id),
(@role4_id, @perm3_id),
(@role4_id, @perm4_id),
(@role5_id, @perm6_id),
(@role5_id, @perm7_id),
(@role5_id, @perm8_id);