Présentation de l'activité

En tant qu'analyste de sécurité, vous aurez régulièrement besoin de données provenant de plusieurs tables.

Précédemment, vous avez découvert qu'une base de données relationnelle est une base de données structurée qui contient des tables liées les unes aux autres.

Les jointures SQL vous permettent d'associer plusieurs tables possédant une colonne commune. Cette opération est utile lorsque vous devez regrouper des informations qui apparaissent dans plusieurs tables différentes.

Dans cet atelier, vous allez utiliser des jointures SQL pour associer des tables distinctes et récupérer les informations dont vous avez besoin.

Mettez en pratique vos connaissances et joignez des données !

Remarque : Les termes ligne et enregistrement sont utilisés de manière interchangeable tout au long de cette activité.

Scénario

Dans ce scénario, vous enquêterez sur un incident de sécurité récent qui a compromis plusieurs machines.

À cette fin, vous êtes chargé d'obtenir les informations requises à partir de la base de données.

Voici comment vous allez procéder : Premièrement, vous utiliserez une jointure interne pour identifier quels employés utilisent quelles machines. Deuxièmement, vous utiliserez les jointures gauche et droite pour trouver les machines qui n'appartiennent pas à un utilisateur spécifique, ainsi que les utilisateurs pour lesquels aucune machine spécifique n'est assignée. Troisièmement, vous utiliserez une jointure interne pour recenser les tentatives de connexion effectuées par l'ensemble des employés.

Vous êtes prêt à joindre des tables en SQL !

Remarque : Dans cet atelier, vous travaillerez avec la base de données "organization" et les tables qu'elle contient.

L'atelier démarre avec la base de données "organization" déjà ouverte dans le shell MariaDB. Vous pouvez commencer l'exercice dès que vous avez cliqué sur le bouton Start Lab (Démarrer l'atelier).

Si vous quittez par inadvertance la base de données "organization" dans le shell MariaDB, vous pouvez vous reconnecter en utilisant la commande sudo mysql organization.

Avis de non-responsabilité : Pour des performances et une compatibilité optimales, nous vous invitons à utiliser Google Chrome ou Mozilla Firefox pour accéder aux ateliers.

Démarrer l'atelier

Vous devez démarrer l'atelier pour accéder aux supports de cours. Pour cela, cliquez sur le bouton vert "Démarrer l'atelier" en haut de l'écran.

Après avoir cliqué sur le bouton Démarrer l'atelier, vous verrez une interface système où vous pourrez suivre les prochaines étapes de l'atelier. Elle devrait se présenter comme suit :

Une fois que vous avez terminé toutes les tâches, reportez-vous à la section Terminer l'atelier qui suit les tâches pour savoir comment mettre fin à l'atelier.

Tâche 1 : Faire correspondre les employés à leurs machines

Vous devez d'abord identifier quels employés utilisent quelles machines. Les données se trouvent dans les tables machines et employees.

Vous devez utiliser une jointure SQL interne pour renvoyer les enregistrements dont vous avez besoin à partir d'une colonne commune. Dans ce scénario, les deux tables contiennent la colonne device_id, que vous utiliserez pour créer la jointure.

1. Exécutez la requête suivante pour récupérer l'intégralité des enregistrements de la table machines :

SELECT * FROM machines;

Notez que cette requête n'est pas suffisante pour créer la jointure et récupérer les informations dont vous avez besoin.

2. Complétez la requête pour créer une jointure interne entre les tables machines et employees à partir de la colonne device_id. Remplacez X et Y par le nom de cette colonne :

SELECT * FROM machines INNER JOIN employees ON machines.X = employees.Y; Remarque : placer la table employees après INNER JOIN fait de cette dernière la table de droite.

Pour effectuer une jointure, vous devez lier les deux tables jointes par le biais d'une colonne commune. Dans le cas des tables employees et machines, la colonne device_id est commune.

À cette étape, la requête à utiliser est :

SELECT * FROM machines INNER JOIN employees ON machines.device_id = employees.device_id; Remarque : si le résultat de la requête est trop volumineux pour votre shell, appuyez sur le bouton Open Linux Console (Ouvrir la console Linux), comme décrit dans la section de l'atelier consacrée aux fonctionnalités, afin d'afficher le shell bash en plein écran et saisir à nouveau la requête.

Réponse : la requête de jointure interne renvoie 185 lignes.

Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement effectué cette tâche.

Faire correspondre les employés à leurs machines

Tâche 2 : Renvoyer plus de données

Vous devez maintenant renvoyer les informations à propos de l'ensemble des machines et des employés qui en possèdent. Vous devez ensuite effectuer l'opération inverse : récupérer les informations de tous les employés et des machines qui leur sont assignées.

Pour ce faire, créez une jointure droite et une gauche entre les tables employees et machines. Les résultats incluent tous les enregistrements des deux tables. Vous devez lier ces tables à l'aide de la colonne commune device_id.

1. Exécutez la requête SQL suivante pour associer les tables machines et employees grâce à une jointure gauche. Vous devez remplacer le mot clé X dans la requête :

SELECT * FROM machines X JOIN employees ON machines.device_id = employees.device_id;

À cette étape, la requête à utiliser est :

SELECT * FROM machines LEFT JOIN employees ON machines.device_id = employees.device_id; Remarque : dans une jointure gauche, tous les enregistrements de la table référencée après FROM et avant LEFT JOIN sont inclus dans les résultats. Dans ce cas, l'ensemble des enregistrements de la table machines sont inclus, peu importe si celles-ci sont assignées à un employé ou non.

Réponse : le dernier nom d'utilisateur renvoie une valeur NULL.

2. Exécutez la requête SQL suivante pour associer les tables machines et employees grâce à une jointure droite. Vous devez remplacer le mot clé X dans la requête pour résoudre le problème :

SELECT * FROM machines X JOIN employees ON machines.device_id = employees.device_id; Remarque : dans une jointure droite, tous les enregistrements de la table référencée après RIGHT JOIN sont inclus dans les résultats. Dans ce cas, l'ensemble des enregistrements de la table employees sont inclus, peu importe si une machine est assignée ou non à ces derniers.

À cette étape, la requête à utiliser est :

SELECT * FROM machines RIGHT JOIN employees ON machines.device_id = employees.device_id;

Réponse : la valeur affichée dans la colonne "username" pour le dernier enregistrement est areyes.

Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement effectué cette tâche.

Renvoyer plus de données

Tâche 3 : Récupérer les données de tentatives de connexion

Afin de poursuivre votre enquête sur l'incident de sécurité, vous devez récupérer les informations sur tous les employés à l'origine d'une tentative de connexion. Pour ce faire, créez une jointure interne entre les tables employees et log_in_attempts, en les associant à partir de la colonne username.

• Exécutez la requête SQL suivante pour créer une jointure interne entre les tables employees et log_in_attempts. Remplacez X par le nom de la table de droite. Puis remplacez Y et Z par le nom de la colonne qui lie les deux tables :

SELECT * FROM employees INNER JOIN X ON Y = Z; Remarque : vous devez spécifier le nom de la table en plus du nom de la colonne (table.colonne) lorsque vous créez la jointure.

À cette étape, la requête à utiliser est :

SELECT * FROM employees INNER JOIN log_in_attempts ON employees.username = log_in_attempts.username;

Réponse : la jointure interne renvoie 200 enregistrements.

Cliquez sur Vérifier ma progression pour vérifier que vous avez correctement effectué cette tâche.

Récupérer les données de tentatives de connexion

Conclusion

Bravo !

Vous avez terminé cette activité ! Vous êtes désormais capable d'utiliser des jointures pour associer des données de plusieurs tables d'une base de données.

Vous venez d'acquérir une expérience pratique concernant l'utilisation de :

• INNER JOIN ;
• LEFT JOIN ;
• RIGHT JOIN.

Vous avez utilisé les jointures SQL pour obtenir les données précises dont vous avez besoin. Bien joué !

Terminer l'atelier

Avant de terminer l'atelier, assurez-vous d'avoir accompli toutes les tâches à votre convenance et prenez connaissance des étapes ci-dessous :

1. Cliquez sur End Lab (Terminer l'atelier), puis sur Submit (Envoyer). La fermeture de l'atelier supprimera votre accès au shell bash. Vous ne pourrez plus accéder au travail que vous y avez accompli.
2. Après la fermeture de l'atelier, une boîte de dialogue d'enquête apparaît. Vous pouvez y noter l'atelier et laisser vos commentaires, si vous le souhaitez.
3. Fermez l'onglet de navigateur de l'atelier pour revenir à votre cours.
4. Rafraîchissez l'onglet de navigateur du cours après avoir indiqué que cet élément est terminé.