MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table `users` (
`id` int ,
`username` varchar(5),
`password` varchar(15)
) ;

 INSERT INTO `users` (`id`, `username`, `password`) VALUES
    (1, 'foo', 'pass1'),
    (2, 'bar', 'pass2');
    
create table `role` (
`id` int ,
`libelle` varchar(15),
`statut` int
) ;    
    
    
    INSERT INTO `role` (`id`, `libelle`, `statut`) VALUES
    (1, 'Operator', 1),
    (2, 'User', 1),
    (5, 'Administrator', 1);
    
 
create table `type_role` (
`id` int ,
`libelle` varchar(15),
`statut` int
) ;    
     
    
    INSERT INTO `type_role` (`id`, `libelle`, `statut`) VALUES
    (1, 'Executif', 1),
    (2, 'home', 1),
    (3, 'System', 1);
    
create table `action` (
`id` int ,
`id_user` int ,
`id_role` int,
id_type_role int 
) ;        
    
    INSERT INTO `action` (`id`, `id_user`, `id_role`, `id_type_role`) VALUES
    (1, 1, 5, 3),
    (2, 2, 5, 3);
select * from users;
select * from role;
select * from type_role;
select * from action;



id
username
password


1
foo
pass1

2
bar
pass2




id
libelle
statut


1
Operator
1

2
User
1

5
Administrator
1




id
libelle
statut


1
Executif
1

2
home
1

3
System
1




id
id_user
id_role
id_type_role


1
1
5
3

2
2
5
3


SELECT u.id AS id,
       r.libelle AS role,
       t.libelle AS type
FROM users u
JOIN action a ON a.id_user = u.id
JOIN type_role t ON t.id = a.id_type_role
JOIN role r ON r.id = a.id_role
WHERE a.id_user =1 
AND t.libelle = 'System';


id
role
type


1
Administrator
System