アクティビティの概要

セキュリティ アナリストとして、複数のテーブルのデータが必要になることはよくあります。

前回は、リレーショナル データベースが、互いに関連するテーブルを含む構造化データベースであることを学びました。

SQL の結合により、共通の列を含む複数のテーブルを 1 つにすることができます。これは、異なるテーブルに存在する情報をつなぐ必要がある場合に便利です。

このラボのアクティビティでは、SQL の結合を使用して別々のテーブルをつなぎ、必要な情報を取得します。

学んだことを応用してデータを結合してみましょう。

注: 行とレコードという用語は同じ意味で使用しています。

シナリオ

このシナリオでは、複数のマシンが不正使用された最近のセキュリティ インシデントを調査します。

調査のためにデータベースから必要な情報を取得する役割を担います。

方法は次のとおりです。まず、内部結合を使用して、どの従業員がどのマシンを使用しているのかを特定します。次に、左結合と右結合を使用して、特定のユーザーに属していないマシンと、特定のマシンが割り当てられていないユーザーを探します。最後に、内部結合を使用して、全従業員によるすべてのログイン試行を一覧にします。

それでは、SQL でテーブルを結合してみましょう。

注: このラボでは、組織のデータベースとそこにあるテーブルを操作します。

このラボは、MariaDB シェルで組織のデータベースがすでに開かれている状態で始まります。つまり、[ラボを開始] ボタンをクリックするとすぐにタスクから始めることができます。

MariaDB シェルで誤って組織のデータベースを閉じた場合は、sudo mysql organization コマンドを実行すると再接続できます。

免責条項: パフォーマンスと互換性を確保するため、ラボにアクセスする際は Google Chrome または Mozilla Firefox ブラウザを使用することをおすすめします。

ラボを開始する

資料にアクセスするには、まずラボを開始する必要があります。ラボを開始するには、画面上部にある緑色の [ラボを開始] ボタンをクリックします。

[ラボを開始] ボタンをクリックするとシェルが表示され、以降の手順はここで進めます。次のようなシェルが表示されます。

タスクをすべて終えたら、ラボの終了に関するセクションを参照し、ラボの終了手順をご確認ください。

タスク 1. 従業員とマシンをマッチングする

まず、どの従業員がどのマシンを使用しているのかを特定します。データは machines テーブルと employees テーブルに格納されています。

SQL の内部結合を使用して、つながる列に基づいて必要なレコードを返す必要があります。このシナリオでは、どちらのテーブルにも device_id 列があり、これを使用して結合を行います。

1. 次のクエリを実行して、machines テーブルからすべてのレコードを取得します。

SELECT * FROM machines;

結合を行って必要な情報を取得するには、このクエリでは不十分であることがわかります。

2. device_id 列で machines テーブルと employees テーブルの内部結合を行うためのクエリを完成します。X と Y はこの列の名前に置き換えてください。

SELECT * FROM machines INNER JOIN employees ON machines.X = employees.Y; 注: employees テーブルを INNER JOIN の後に配置すると、右のテーブルになります。

結合を完成するには、結合するテーブルを共通の列で結び付ける必要があります。employees テーブルと machines テーブルは、device_id 列が共通しています。

このステップに必要な正しいクエリは次のとおりです。

SELECT * FROM machines INNER JOIN employees ON machines.device_id = employees.device_id; 注: シェルに対してクエリの出力の幅が大きすぎる場合は、ラボの機能のセクションで説明したように、[Linux コンソールを開く] ボタンをクリックと Bash シェルが全画面表示で開くため、クエリを再入力できます。

解答: 内部結合クエリによって 185 件の行が返されました。

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

従業員とマシンをマッチングする

タスク 2. 返すデータを増やす

今度は、すべてのマシンと、マシンを持っている従業員についての情報を返します。次に、その逆を行い、すべての従業員と、従業員に割り当てられているマシンについての情報を取得します。

そのために、employees テーブルと machines テーブルに対し、左結合と右結合を行います。結果には、どちらか一方のテーブルのすべてのレコードが含まれます。共通の device_id 列を使用して両方のテーブルをリンクする必要があります。

1. 次の SQL クエリを実行して、左結合で machines テーブルと employees テーブルをつなぎます。クエリ内のキーワード X は適宜置き換えてください。

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

このステップに必要な正しいクエリは次のとおりです。

SELECT * FROM machines LEFT JOIN employees ON machines.device_id = employees.device_id; 注: 左結合では、FROM の後および LEFT JOIN の前に参照されたテーブルのすべてのレコードが結果に含まれます。この場合、従業員に割り当てられているかどうかに関係なく、machines テーブルのすべてのレコードが含まれます。

解答: 最後に返されたユーザー名は NULL です。

2. 次の SQL クエリを実行して、右結合で machines テーブルと employees テーブルをつなぎます。クエリ内のキーワード X は、解答を導き出すことができるよう、適宜置き換えてください。

SELECT * FROM machines X JOIN employees ON machines.device_id = employees.device_id; 注: 右結合では、RIGHT JOIN の後で参照されたテーブルのすべてのレコードが結果に含まれます。この場合、従業員がマシンを持っているかどうかに関係なく、employees テーブルのすべてのレコードが含まれます。

このステップに必要な正しいクエリは次のとおりです。

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

解答: 最後に返されたレコードの username 列の値は areyes です。

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

返すデータを増やす

タスク 3. ログイン試行のデータを取得する

セキュリティ インシデントの調査を続けるには、ログインを試したすべての従業員の情報を取得します。そのために、employees テーブルと log_in_attempts テーブルの内部結合を行い、共通の username 列でリンクします。

• 次の SQL クエリを実行して、employees テーブルと log_in_attempts テーブルの内部結合を行います。X は右のテーブルの名前に置き換えます。Y と Z は、2 つのテーブルをつなぐ列の名前に置き換えます。

SELECT * FROM employees INNER JOIN X ON Y = Z; 注: テーブルを結合するときは、テーブル名と列名（table.column）を指定する必要があります。

このステップに必要な正しいクエリは次のとおりです。

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

解答: 内部結合によって 200 件のレコードが返されました。

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

ログイン試行のデータを取得する

まとめ

おつかれさまでした。

今回のアクティビティを終えたことで、結合を使用してデータベースの複数のテーブルからデータを組み合わせられるようになりました。

これで、以下の結合を使用する実践的な経験を積むことができました。

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

SQL の結合を使用して、必要なデータを正確に取得できるようになりました。

ラボを終了する

すべてのタスクが問題なく完了したことを確認してから、以下の手順に沿ってラボを終了してください。

1. [ラボを終了] をクリックし、[送信] をクリックします。ラボを終了すると、Bash シェルへのアクセス権が削除され、完了した作業にもう一度アクセスすることはできなくなります。
2. ラボを終了するとアンケート ダイアログが表示され、適宜、ラボを評価したり、フィードバック コメントを記入したりできます。
3. ラボのブラウザタブを閉じてコースに戻ります。
4. 完了してからコースのブラウザタブを更新すると、この項目が完了としてマークされます。