Pgsql - Systeme D’authentification Et Autorisation

Absolument ! Pour cet exercice, nous allons concevoir un système d’authentification et d’autorisation pour une API en utilisant les concepts de JWT et OAuth 2.0. Étant donné que ces mécanismes sont souvent implémentés au niveau de l’application (backend) plutôt que directement dans la base de données, l’exercice se concentrera sur la conception de la base de données pour supporter ces fonctionnalités et sur les requêtes SQL pertinentes pour la gestion des utilisateurs, des rôles et des permissions.

1. Modèle Logique des Données (MLD) en ER Diagram

erDiagram
    UTILISATEUR ||--o{ ROLE_UTILISATEUR : possede
    ROLE ||--o{ ROLE_UTILISATEUR : est_attribue_a
    ROLE ||--o{ PERMISSION_ROLE : inclut
    PERMISSION ||--o{ PERMISSION_ROLE : est_incluse_dans
    CLIENT_OAUTH ||--o{ JETON_ACCES : possede
    UTILISATEUR {
        INTEGER id_utilisateur SERIAL PRIMARY KEY
        VARCHAR nom_utilisateur VARCHAR(100) UNIQUE NOT NULL
        VARCHAR mot_de_passe VARCHAR(255) NOT NULL -- Hash du mot de passe
        VARCHAR email VARCHAR(100) UNIQUE NOT NULL
        BOOLEAN est_actif BOOLEAN NOT NULL DEFAULT TRUE
        DATE date_creation DATE NOT NULL DEFAULT CURRENT_DATE
    }
    ROLE {
        INTEGER id_role SERIAL PRIMARY KEY
        VARCHAR nom_role VARCHAR(100) UNIQUE NOT NULL
        VARCHAR description VARCHAR(255)
    }
    PERMISSION {
        INTEGER id_permission SERIAL PRIMARY KEY
        VARCHAR nom_permission VARCHAR(100) UNIQUE NOT NULL
        VARCHAR description VARCHAR(255)
    }
    ROLE_UTILISATEUR {
        INTEGER id_role_utilisateur SERIAL PRIMARY KEY
        INTEGER id_utilisateur INTEGER REFERENCES UTILISATEUR(id_utilisateur)
        INTEGER id_role INTEGER REFERENCES ROLE(id_role)
        UNIQUE (id_utilisateur, id_role)
    }
    PERMISSION_ROLE {
        INTEGER id_permission_role SERIAL PRIMARY KEY
        INTEGER id_role INTEGER REFERENCES ROLE(id_role)
        INTEGER id_permission INTEGER REFERENCES PERMISSION(id_permission)
        UNIQUE (id_role, id_permission)
    }
    CLIENT_OAUTH {
        INTEGER id_client SERIAL PRIMARY KEY
        VARCHAR client_id VARCHAR(255) UNIQUE NOT NULL
        VARCHAR client_secret VARCHAR(255) NOT NULL
        VARCHAR redirect_uri TEXT
        VARCHAR grant_types VARCHAR(255) -- Ex: 'authorization_code,password,refresh_token'
        VARCHAR scope TEXT
    }
    JETON_ACCES {
        INTEGER id_jeton SERIAL PRIMARY KEY
        VARCHAR jeton VARCHAR(255) UNIQUE NOT NULL
        INTEGER id_utilisateur INTEGER REFERENCES UTILISATEUR(id_utilisateur)
        INTEGER id_client INTEGER REFERENCES CLIENT_OAUTH(id_client)
        TIMESTAMP date_creation TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
        TIMESTAMP date_expiration TIMESTAMP WITHOUT TIME ZONE NOT NULL
        VARCHAR refresh_token VARCHAR(255) UNIQUE
        VARCHAR scope TEXT
    }

2. Création de la Base de Données et des Tables (À faire par vous - en PostgreSQL)

Créez une nouvelle base de données nommée authentification dans PostgreSQL et exécutez les instructions SQL pour créer les tables basées sur le diagramme ER ci-dessus. Définissez soigneusement les clés primaires, les clés étrangères et toutes les contraintes (NOT NULL, UNIQUE). Utilisez SERIAL pour les colonnes d’auto-incrémentation.

3. Insertion de Données (Instructions et Données)

Insérez des données pour configurer votre système d’authentification et d’autorisation.

Table UTILISATEUR:

nom_utilisateur	mot_de_passe (hash)	email	est_actif
alice	$2a$10$examplehash1	[adresse e-mail supprimée]	TRUE
bob	$2a$10$anotherhash2	[adresse e-mail supprimée]	TRUE
charlie	$2a$10$yetanother3	[adresse e-mail supprimée]	FALSE

Table ROLE:

nom_role	description
admin	Administrateur système
editor	Éditeur de contenu
viewer	Lecteur seul

Table PERMISSION:

nom_permission	description
create	Créer des ressources
read	Lire des ressources
update	Mettre à jour des ressources
delete	Supprimer des ressources
publish	Publier du contenu

Table ROLE_UTILISATEUR:

id_utilisateur	id_role
1	1
2	2
2	3
3	3

Table PERMISSION_ROLE:

id_role	id_permission
1	1
1	2
1	3
1	4
1	5
2	1
2	2
2	3
2	5
3	2

Table CLIENT_OAUTH:

client_id	client_secret	redirect_uri	grant_types	scope
mobile_app	secret_mobile	com.example.mobile://	authorization_code,password,refresh_token	read,write
web_app	secret_web	https://example.com/callback	authorization_code	read
trusted_cli	trusted_secret		client_credentials	api

Table JETON_ACCES:

jeton	id_utilisateur	id_client	date_creation	date_expiration	refresh_token	scope
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxI…	1	1	2025-04-09 02:00:00	2025-04-09 03:00:00	rtoken_alice_mobile	read,write
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIyI…	2	2	2025-04-09 02:05:00	2025-04-09 02:15:00	rtoken_bob_web	read

Instructions pour l’insertion (À faire par vous - en PostgreSQL) :

Utilisez l’instruction INSERT INTO pour ajouter ces données dans les tables correspondantes. N’oubliez pas que les mots de passe dans la table UTILISATEUR devraient en réalité être des hachages sécurisés (comme ceux générés par bcrypt).

4. Requêtes SQL Avancées (Beaucoup ! - en PostgreSQL)

Voici une série de requêtes SQL pour explorer votre système d’authentification et d’autorisation.

Requêtes de base sur les utilisateurs, rôles et permissions:

1. Affichez tous les utilisateurs.
2. Affichez les utilisateurs actifs.
3. Affichez tous les rôles.
4. Affichez toutes les permissions.
5. Affichez les rôles attribués à un utilisateur spécifique (par exemple, l’utilisateur ‘alice’).
6. Affichez les utilisateurs qui ont un rôle spécifique (par exemple, le rôle ‘editor’).
7. Affichez les permissions associées à un rôle spécifique (par exemple, le rôle ‘admin’).
8. Affichez les rôles qui incluent une permission spécifique (par exemple, la permission ‘publish’).
9. Affichez les utilisateurs avec leurs rôles.
10. Affichez les rôles avec leurs permissions.

Requêtes sur l’autorisation (vérification des permissions):

11. Vérifiez si un utilisateur spécifique (par exemple, ‘bob’) a une permission spécifique (‘publish’). (Nécessite une jointure sur plusieurs tables).
12. Affichez toutes les permissions d’un utilisateur donné (via ses rôles).
13. Trouvez les utilisateurs qui ont à la fois le rôle ‘editor’ et ‘viewer’.
14. Trouvez les utilisateurs qui ont au moins une permission de ‘delete’.
15. Affichez les rôles qui ont toutes les permissions ‘read’, ‘create’ et ‘update’.

Requêtes sur les clients OAuth 2.0:

16. Affichez tous les clients OAuth 2.0 enregistrés.
17. Trouvez un client OAuth 2.0 par son client_id.
18. Affichez les grant_types supportés par un client spécifique.
19. Affichez les clients qui autorisent le scope ‘read,write’.

Requêtes sur les jetons d’accès (JWT simulé):

20. Affichez tous les jetons d’accès actifs (non expirés).
21. Trouvez un jeton d’accès par sa valeur.
22. Affichez le jeton d’accès associé à un utilisateur spécifique pour un client OAuth 2.0 donné.
23. Affichez les jetons d’accès qui expireront dans les prochaines 24 heures.
24. Trouvez l’utilisateur associé à un jeton d’accès spécifique.
25. Affichez le client OAuth 2.0 qui a émis un jeton d’accès spécifique.
26. Affichez les jetons d’accès avec un scope spécifique (par exemple, ‘read’).
27. Trouvez les jetons d’accès qui ont un refresh_token non nul.

Requêtes avancées et combinaisons:

28. Affichez les utilisateurs (et leur statut actif) qui ont le rôle ‘admin’ OU le rôle ‘editor’.
29. Trouvez les utilisateurs qui ont des rôles qui leur donnent la permission ‘delete’ mais pas la permission ‘create’.
30. Affichez les clients OAuth 2.0 qui permettent l’octroi de type ‘password’ et ont un redirect_uri défini.
31. Trouvez les utilisateurs qui ont un jeton d’accès actif émis par le client ‘mobile_app’.
32. Affichez les utilisateurs qui ont des rôles avec toutes les permissions de base (‘create’, ‘read’, ‘update’, ‘delete’).
33. Trouvez les clients OAuth 2.0 qui ne spécifient aucun scope.
34. Affichez les utilisateurs qui n’ont aucun rôle attribué.
35. Trouvez les rôles qui n’ont aucune permission associée.
36. Affichez les utilisateurs et le nombre de rôles qu’ils possèdent.
37. Affichez les rôles et le nombre de permissions qu’ils incluent.
38. Trouvez les utilisateurs actifs qui ont au moins une permission ‘publish’.
39. Affichez les clients OAuth 2.0 qui supportent à la fois ‘authorization_code’ et ‘refresh_token’.
40. Trouvez les jetons d’accès créés au cours de la semaine dernière.

Scénarios d’autorisation complexes:

41. Écrivez une requête qui, étant donné un nom_utilisateur et un nom_permission, renvoie TRUE si l’utilisateur a cette permission, FALSE sinon. (Ceci simule une vérification d’autorisation).
42. Trouvez tous les utilisateurs qui ont au moins une permission qui commence par ‘read’.
43. Affichez les rôles dont la description contient le mot ‘administrateur’.
44. Trouvez les clients OAuth 2.0 dont le redirect_uri correspond à un pattern spécifique (par exemple, se terminant par ‘/callback’).
45. Affichez les jetons d’accès triés par leur date d’expiration (les plus proches en premier).
46. Trouvez les utilisateurs qui ont des rôles qui leur permettent à la fois de ‘create’ et de ‘read’ mais pas de ‘delete’.
47. Affichez les clients OAuth 2.0 qui n’ont pas de scope défini.
48. Trouvez les utilisateurs qui ont plus de deux rôles.
49. Affichez les rôles qui ont plus de quatre permissions.
50. Trouvez les utilisateurs actifs qui ont au moins une permission ‘update’ et dont l’email contient ‘@example.com’ (si vous aviez plus de données email variées).

Et ainsi de suite ! Cet exercice vous permet de concevoir une base de données robuste pour la gestion de l’authentification et de l’autorisation avec des concepts clés comme les utilisateurs, les rôles, les permissions et le support pour OAuth 2.0 (clients et jetons). N’hésitez pas à imaginer d’autres requêtes pour explorer les relations entre ces entités. Bonne exploration !