Exercice 3 - Bases de données relationnelles et SQL · Bac Numérique et Sciences Informatiques Polynésie 2025 (jour 1)

Partie C

Dans cette partie, on s'intéresse à la sécurité du gestionnaire de mots de passe d'Alice. Alice utilise un système d'exploitation multi-utilisateurs basé sur Linux et elle a les droits de lecture, d'écriture et d'exécution dans son répertoire personnel /home.

La base de données est stockée dans un fichier gestionnaire.db. Alice décide de chiffrer ce fichier. Elle crée un fichier chiffrement.py dans lequel elle écrit le code d'une fonction Python chiffrement dont la documentation est donnée ci-après.

def chiffrement(f_source, f_dest, f_cle):

'''Crée un fichier f_dest contenant les données

du fichier f_source chiffrées selon la clé

contenue dans le fichier f_cle.

f_source (str): chemin vers le fichier à chiffrer

f_dest (str): chemin vers le fichier chiffré

f_cle (str): chemin vers le fichier contenant la clé

''' On donne l'arborescence de fichiers utilisée par Alice sur la Figure 2.

Alice ouvre un interpréteur Python dans le répertoire Documents, qui est ainsi le répertoire courant (ou répertoire de travail) et exécute le contenu du fichier chiffrement.py.

L'algorithme de chiffrement consiste à appliquer l'opération OU-exclusif bit à bit entre le fichier source et le fichier contenant la clé de chiffrement.

Le premier bit du fichier chiffré est le résultat du OU-exclusif entre le premier bit du fichier source et le premier bit du fichier clé. De même, le deuxième bit est obtenu par le OU-exclusif entre les deuxièmes bits de ces deux fichiers et ainsi de suite.

On suppose, pour simplifier, que le fichier contenant la clé est de la même taille que le fichier source, qui est donc aussi la taille du fichier chiffré.

On rappelle ci-après la table de vérité du OU-exclusif, noté XOR.

Table de vérité du XOR a · b · a XOR b

0 · 0 · 0

0 · 1 · 1

1 · 0 · 1

1 · 1 · 0

On rappelle que l'hexadécimal correspond à l'écriture des entiers dans la base 16. Les chiffres de cette base sont notés 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F.

Partie D

On donne ci-dessous quatre bonnes pratiques proposées par le gouvernement en matière de gestion des mots de passe.

P1: Utilisez un mot de passe différent pour chaque accès (messagerie, banque en ligne, comptes de réseaux sociaux, etc.): en cas de compromission de l'un de vos comptes, cela évitera l'effet boule de neige.

P2: Créez un mot de passe suffisamment long, complexe et inattendu, de 8 caractères minimum, contenant des minuscules, des majuscules, des chiffres et des caractères spéciaux.

P3: Ne communiquez jamais votre mot de passe à un tiers: aucune organisation ou personne de confiance ne vous demandera de lui communiquer votre mot de passe.

P4: Utilisez un gestionnaire de mots de passe: pas simple de retenir tous ses codes de connexion ! Heureusement des outils de type « coffres forts de mots de passe » existent. Ces derniers mémorisent tous vos mots de passe et vous permettent d'en générer de manière aléatoire.

Source: d'après https://cyber.gouv.fr/bonnes-pratiques-protegez-vous

Partie B

Dans cette partie, on pourra utiliser les clauses du langage SQL pour:

construire des requêtes d'interrogation à l'aide de SELECT, FROM, WHERE (avec les opérateurs logiques AND, OR), JOIN... ON;

construire des requêtes d'insertion et de mise à jour à l'aide de UPDATE, INSERT, DELETE;

affiner les recherches à l'aide de DISTINCT, ORDER BY.

Alice souhaite mettre en œuvre une base de données composée des deux tables compte et site dont des extraits sont donnés dans les tableaux suivants.

compte

mot_de_passe

utilisateur

renouvellement

id_site

Asrtg!Myfj

aliceB24

2022-06-30

1

@rDfohpj!&

aliceB24

2021-03-12

2

GxRGDxc(u-PM

alice_B@votremailp.me

2018-10-14

4

Ghcj=+f*AZs

alice1276

2022-06-30

3

cYFgt!:Ehr;

alice_B2@votremailp.me

2022-06-30

4

site

id

nom_site

url

1

Vosnotes

https://logi-educ.net/vosnotes/eleve.html

2

Banque Perso

https://www.banqueperso.fr/connexion.html

3

Elec verte

https://espace-client.ev.fr/login

4

Votremailp

https://account.votremailp.me/login

L'attribut mot_de_passe est une clé primaire de la table compte.

L'attribut id est une clé primaire de la table site.

L'attribut renouvellement, correspondant au dernier renouvellement du mot de passe, est une chaîne de caractères de format AAAA-MM-JJ. Ainsi un mot de passe renouvelé le 21 février 2025 correspond à un attribut renouvellement de '2025-02-21'.

Dans la table compte, l'attribut id_site est une clé étrangère référençant l'attribut id de la table site.

6.

Expliquer en quoi il n'est pas possible, pour Alice, d'avoir le même mot de passe pour deux sites différents.

7.

Écrire la requête SQL permettant d'afficher toutes les URL enregistrées dans la base de données.

8.

La Banque Perso a demandé à Alice de renouveler son mot de passe. Elle remplace le mot de passe '@rDfohpj!&' par 'yhTS?d@UTJe'. Écrire la requête SQL permettant de faire la modification dans la base de données. Dans cette question, on ignorera l'attribut renouvellement et on ne cherchera pas à le mettre à jour.

On rappelle que le langage SQL utilise l'ordre lexicographique (ordre du dictionnaire) pour comparer deux éléments de type texte. Selon cet ordre, on a par exemple '3' supérieur à '1' et 'python' est inférieur à 'sql'.

9.

À la date du 20 mars 2025, Alice a décidé de lister tous les mots de passe qui n'ont pas été renouvelés depuis plus d'un an. Écrire la requête SQL permettant de donner la liste des id_site concernés.

10.

Donner une raison pour laquelle Alice a préféré choisir le format AAAA-MM-JJ pour l'attribut renouvellement plutôt que le format JJ-MM-AAAA.

11.

Écrire une requête SQL permettant d'afficher tous les utilisateurs et les mots de passe du ou des sites de nom 'Votremailp' dont l'identifiant est supposé non connu. Le résultat devra être affiché par ordre chronologique de date de renouvellement de mot de passe.

12.

Pour réaliser le projet, il aurait été possible de regrouper toutes les informations dans une seule table mais Alice a choisi d'utiliser deux tables: les tables site et compte. Donner un avantage impliqué par le choix d'Alice.